如何在sqlite中使用ROW_NUMBER

Here is my query given below.

这是我在下面给出的查询。

select * from data where value = "yes";

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

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

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

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

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

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

ROW_NUMBER AS NoId.

ROW_NUMBER作为NoId。

3 个解决方案

#1

Try this query

试试这个查询

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 |

#2

I mended somewhat with fiddleanswer and got exactly the result as expected

我用fiddleanswer修补了一些，并得到了预期的结果

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

#3

SQLite Release 3.25.0 will add support for window functions

SQLite版本3.25.0将添加对窗口函数的支持

2018-09-00 (3.25.0)

2018-09-00（3.25.0）

1. Add support for window functions
2. 添加对窗口函数的支持

---

Window Functions :

窗口功能：

A window function is a special SQL function where the input values are taken from a "window" of one or more rows in the results set of a SELECT statement.

窗口函数是一种特殊的SQL函数，其中输入值取自SELECT语句结果集中一行或多行的“窗口”。

SQLite supports the following 11 built-in window functions:

SQLite支持以下11种内置窗口函数：

row_number()

ROW_NUMBER（）

The number of the row within the current partition. Rows are numbered starting from 1 in the order defined by the ORDER BY clause in the window definition, or in arbitrary order otherwise.

当前分区中的行数。行按照窗口定义中ORDER BY子句定义的顺序从1开始编号，否则按任意顺序编号。

So your query could be rewritten as:

因此您的查询可以重写为：

select *, ROW_NUMBER() OVER(ORDER BY Id) AS NoId
from data 
where value = "yes";