如何在 SQLite 中获取行号? [英] How to get row number in SQLite?

问题描述

我读过很多关于如何在 SQLite 中使用行号的文章，但没有一篇文章给了我需要的答案.我知道如何使用此查询选择行号:

I've read many articles regarding how to use a row number in SQLite but none of them gave me the answer I need. I know how to select row number using this query:

SELECT (SELECT COUNT() FROM table WHERE title < t.title OR (title = t.title AND id<t.id)) as rowIndex, t.title FROM table AS t ORDER BY t.title;

但是如果我在查询的末尾添加 COLLATE NOCASE(我需要)，那么结果就完全不同了.

but if I add COLLATE NOCASE (which I need) at the end of the query then the result is completely different.

推荐答案

您的查询包含错误:别名ja"未定义.

Your query contains an error: the alias "ja" is not defined.

试试这个:

SELECT 
  ( SELECT COUNT(*) + 1 
    FROM "table" 
    WHERE title < t.title OR (title = t.title AND id<t.id)
  ) as rowIndex, 
  t.title 
FROM "table" t 
ORDER BY t.title;

但是，请注意，此子查询构造不能很好地扩展.对于大型数据集，您可能希望创建一个临时表并使用 ROWID(如所讨论的，例如 这里).

However, be aware that this sub-query construction will not scale well. For large datasets you might want to create a temp table and use ROWID instead (as discussed, for example, here).

使用 COLLATE NOCASE 进行测试:

Test with COLLATE NOCASE:

CREATE TABLE "table" (id INTEGER, title TEXT COLLATE NOCASE);

INSERT INTO "table" VALUES 
(1, "Book A"), 
(2, "Book b"), 
(3, "Book C"), 
(4, "Book B"), 
(5, "Book a"); 

查询结果:

1|Book A
2|Book a
3|Book b
4|Book B
5|Book C

如果不想声明 COLLATE NOCASE 列，则必须确保在 ORDER BY 部分以及子查询中使用 COLLATE:

If you do not want to declare the column COLLATE NOCASE, you have to make sure that you use COLLATE in the ORDER BY part as well as in the subquery:

SELECT 
  ( SELECT COUNT(*) + 1 
    FROM "table" 
    WHERE title < t.title COLLATE NOCASE OR (title = t.title COLLATE NOCASE  AND id<t.id)
  ) as rowIndex, 
  t.title 
FROM "table" t 
ORDER BY t.title COLLATE NOCASE;

这篇关于如何在 SQLite 中获取行号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！