sqlite 没有 row_number , 但是有 rowid , 不过 rowid 这玩意只能在未删除过的情况是连续的,确实很坑。
下面的做法可以产生正确的行号:
drop table if exists testTable1;
create table testTable1( id INT PRIMARY KEY,[name] NVARCHAR(20), parentId INT );
INSERT INTO testTable1(id,[name],parentId) VALUES(2,'xf1',0);
INSERT INTO testTable1(id,[name],parentId) VALUES(3,'xf2',0);
INSERT INTO testTable1(id,[name],parentId) VALUES(5,'xf3',2);
INSERT INTO testTable1(id,[name],parentId) VALUES(6,'xf4',3);
INSERT INTO testTable1(id,[name],parentId) VALUES(7,'xf5',4);
INSERT INTO testTable1(id,[name],parentId) VALUES(9,'xf6',5);
delete from testTable1 where id=7;
select
rowid
,(select count(*) from testTable1 b where a.id >= b.id) as row_number
,*
from testTable1 as a
order by id;
/*
rowid row_number id name parentId
1 1 2 xf1 0
2 2 3 xf2 0
3 3 5 xf3 2
4 4 6 xf4 3
6 5 9 xf6 5
*/