如何在SQLite中使用ROW_NUMBER

Question

以下是我的查询。

select * from data where value = "yes";

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

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

如何在SQLite中使用ROW_NUMBER？这样我就可以得到以下所示的结果。

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

行数作为编号。

- Puja Surya

---

获取查询结果行号的方法因不同数据库而异，以下为一些示例：MySQL/MariaDB：使用变量来模拟行号：SET @row_number:=0; SELECT @row_number:=@row_number+1 AS row_number, column1, column2 FROM table WHERE condition;PostgreSQL：使用 window 函数 row_number()：SELECT row_number() OVER () AS row_number, column1, column2 FROM table WHERE condition;Oracle：使用伪列 ROWNUM：SELECT ROWNUM AS row_number, column1, column2 FROM table WHERE condition;SQL Server：使用 window 函数 ROW_NUMBER()：SELECT ROW_NUMBER() OVER (ORDER BY column) AS row_number, column1, column2 FROM table WHERE condition; - Scotch

“id”列是自增还是主键列？你尝试过使用“SELECT _ROWID_”吗？ - Alix Axel

@Scotch：这并不完全是重复的，因为OP似乎在询问完整表扫描的ROWID，而不是特定查询的ROWID。 - Alix Axel

@Scotch：数据似乎表明是这样的。我知道你提供的答案也可以克服这个问题，但这可能是不必要的并且更慢（取决于“id”列的定义）。 - Alix Axel

那是真的。我想人们应该使用类似的东西，直到像rownum或row number()这样的东西被实现。 - Scotch

Answer 1

SQLite Release 3.25.0将增加对窗口函数的支持。

2018-09-15 (3.25.0)

1. 增加对窗口函数的支持

---

窗口函数:

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

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

row_number()

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

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

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

db-fiddle.com演示

- Lukasz Szozda

Answer 2

尝试执行此查询

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 |

- Meherzad

Answer 3

ROW_NUMBER() 窗口函数可以在空的 ORDER() 上执行，方法如下（感谢@forpas）：

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

- Nelson

---

ORDER BY (SELECT NULL) 将返回任意行号。此外，它并不是必需的，因为只需使用 OVER () 即可实现相同的效果。 - forpas

谢谢。实际上，就性能而言，OVER() 没有额外的开销，而 OVER(ORDER BY (SELECT NULL)) 则有可能返回任意数字，除此之外还会增加一些开销。 - Nelson

Answer 4

我与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

- user3198319

---

这只是意味着，在SQLite中没有这样的功能。顺便问一下，这是否是O(n^2)？ - Tomasz Gandor

是的，这是n^2级别的。不要这样做 :) - Habbie

Answer 5

更新：sqlite3版本3.25现在支持窗口函数，包括：

row_number() over(order by id)

SQLITE3文档

- v12golfcart

Answer 6

SELECT (SELECT COUNT(*)
FROM main AS t2
WHERE t2.col1 < t1.col1) + (SELECT COUNT(*)
FROM main AS t3
WHERE t3.col1 = t1.col1 AND t3.col1 < t1.col1) AS rowNum, * FROM Table_name t1  WHERE rowNum=0 ORDER BY t1.col1 ASC

- sadegh salehi

---

欢迎来到 Stack Overflow。当代码配合解释时，它会更有帮助。Stack Overflow 的目的是为了学习，而不是提供盲目复制粘贴的片段。请编辑您的答案并解释它如何回答特定的问题。请参见 [answer]。 - Chris