如何在 SQLite 中獲取沒有 Windows 函式的行號？

假設有下表架構和數據

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 中使用它。

那麼如何在不使用視窗函式的情況下獲得相同的結果呢？

要對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;

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() ，則可以使用此技巧。

引用自：https://dba.stackexchange.com/questions/259454