主页 / dba / 问题 / 259454
Accepted
yaugenka
Asked: 2020-02-12 13:41:20 +0800 CST

如何在 SQLite 中获取没有 Windows 函数的行号？

  • 772

假设有下表架构和数据

create table tbl(
    id integer primary key,
    name text,
    pid integer
);

insert into tbl
values
    (1,'d',0),
    (2,'c',1),
    (3,'b',1),
    (4,'e',1),
    (5,'b',0),
    (6,'a',0);

它是父项和子项的一级深度层次结构。

我需要像这样将孩子的名字汇总到他们的父母中

 id | name | children_names
----+------+------------
  6 | a    |
  1 | d    | c, e, b
  5 | b    |

children names需要在每一行中排序，整个结果需要按name列按字母顺序排序，但所有b名称必须始终排在最后。

在 PostgreSQL 中，我会使用这样的row_number() over()窗口函数

with
t as (select * from tbl order by name),
t2 as (select * from t where name<>'b' union all select * from t where name='b'),
t3 as (select *, row_number() over() from t2)
select a.id, a.name, string_agg(b.name, ', ' order by b.row_number)
from t3 a left join t3 b on a.id=b.pid and a.id<>b.id
where a.pid=0
group by a.id, a.name, a.row_number
order by a.row_number

但我需要在缺少窗口功能的 Android Room 中使用它。

那么如何在不使用窗口函数的情况下获得相同的结果呢？

sqlite

2 个回答

  1. Best Answer

    要对group_concat()值进行排序，原始行必须来自有序子查询：

    SELECT id,
       name,
       group_concat(cname) AS children_names
FROM (SELECT p.id,
             p.name,
             c.name AS cname
      FROM      tbl AS p
      LEFT JOIN tbl AS c ON p.id = c.pid
      WHERE p.pid = 0
      ORDER BY c.name = 'b',
               c.name)
GROUP BY id
ORDER BY name = 'b',
         name;
    • 1

  2. SQLite 没有任何STRING_AGG()功能。

    SELECT t1.id, t1.name, COALESCE(GROUP_CONCAT(t2.name), '') children_names
FROM tbl t1
LEFT JOIN tbl t2 ON t1.id = t2.pid
WHERE t1.pid = 0
GROUP BY t1.id, t1.name;

    小提琴

    GROUP_CONCAT()没有ORDER BY条款。无法影响连接值顺序。

    PS。当然，您可以尝试使用递归 CTE 来模拟有序连接，并按照您需要的顺序一个接一个地连接值，但是这个 CTE 太复杂了，我认为这没有任何意义。

    聚苯乙烯。如果您需要通过某些独特的字段/表达式获得不带窗口函数的 ROW_NUMBER() ，则可以使用此技巧。

    • 0

相关问题