如何在 sqlite 中使用 ROW_NUMBER

问题描述

这是我的查询如下.

select * from data where value = "yes";

我的 id 是自动递增的，下面是给定查询的结果.

My id is auto increment and below there is result of given query.

id || value 
1  ||   yes
3  ||   yes
4  ||   yes
6  ||   yes
9  ||   yes

如何在sqlite中使用ROW_NUMBER?这样我就可以得到下面给出的结果.

How to use ROW_NUMBER in sqlite? So that i can get result which is given below.

NoId || value 
1    ||   yes
2    ||   yes
3    ||   yes
4    ||   yes
5    ||   yes

ROW_NUMBER 作为 NoId.

ROW_NUMBER AS NoId.

推荐答案

试试这个查询

select id, value, (select count(*) from tbl b  where a.id >= b.id) as cnt
from tbl a

FIDDLE

| id | value | cnt |
--------------------
|  1 |   yes |   1 |
|  3 |   yes |   2 |
|  4 |   yes |   3 |
|  6 |   yes |   4 |
|  9 |   yes |   5 |

这篇关于如何在 sqlite 中使用 ROW_NUMBER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！