這是我在下面給出的查詢。select * from data where value = "yes";我的ID是自動(dòng)遞增，下面是給定查詢的結(jié)果。id || value 1 || yes3 || yes4 || yes6 || yes9 || yes如何在sqlite中使用ROW_NUMBER？這樣我就可以得到下面給出的結(jié)果。NoId || value 1 || yes2 || yes3 || yes4 || yes5 || yesROW_NUMBER AS NoId。
3 回答
慕蓋茨4494581
TA貢獻(xiàn)1850條經(jīng)驗(yàn) 獲得超11個(gè)贊
試試這個(gè)查詢
select id, value, (select count(*) from tbl b where a.id >= b.id) as cnt
from tbl a
| id | value | cnt |
--------------------
| 1 | yes | 1 |
| 3 | yes | 2 |
| 4 | yes | 3 |
| 6 | yes | 4 |
| 9 | yes | 5 |
長風(fēng)秋雁
TA貢獻(xiàn)1757條經(jīng)驗(yàn) 獲得超7個(gè)贊
我對(duì)fiddleanswer進(jìn)行了一些修改，并得到了預(yù)期的結(jié)果
select id, value ,
(select count(*) from data b where a.id >= b.id and b.value='yes') as cnt
from data a where a.value='yes';
result
1|yes|1
3|yes|2
4|yes|3
6|yes|4
9|yes|5
添加回答
舉報(bào)
0/150
提交
取消