oracle 如何在sqlite IPHONE中获取rowNum之类的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14023292/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get rowNum like column in sqlite IPHONE
提问by user1811427
I have an Sqlite database table like this (with out ascending)
我有一个这样的 Sqlite 数据库表（没有升序）
But i need to retrive the table in Ascending order by name, when i set it ascending order the rowId changes as follows in jumbled order
但是我需要按名称按升序检索表，当我将其设置为升序时，rowId 会以混乱的顺序更改如下
But i need to retrieve some limited number of contacts 5in ascending order every time
但是我每次都需要按升序检索有限数量的联系人 5
like Aaa - Eeeeand then Ffff- Jjjjj......
像Aaa级- èéêë和再Ffff- JJJJJ......
but to se**t limits like 0-5 5-10 .... ** it can able using rowids since they are in jumble order
但是要设置 0-5 5-10 之类的 **t 限制 .... ** 它可以使用 rowids，因为它们的顺序是混乱的
So i need another column like (rowNum in oracle) wich is in order 1234567... every time as follows
所以我需要另一列（oracle中的rowNum），其顺序为1234567...每次如下
how to retrive that column with existing columns
如何使用现有列检索该列
Note: WE DONTE HAVE ROWNUM LIKE COLUMN IN SQLITE
Note: WE DONTE HAVE ROWNUM LIKE COLUMN IN SQLITE
采纳答案by ederbf
This is a way of faking a RowNum, hope it helps:
这是一种伪造 RowNum 的方法，希望对您有所帮助：
SELECT
(SELECT COUNT(*)
FROM Names AS t2
WHERE t2.name < t1.name
) + (
SELECT COUNT(*)
FROM Names AS t3
WHERE t3.name = t1.name AND t3.id < t1.id
) AS rowNum,
id,
name
FROM Names t1
ORDER BY t1.name ASC
回答by yves
The fake rownum solution is clever, but I am afraid it doesn't scale well (for complex query you have to join and count on each row the number of row before current row).
假的 rownum 解决方案很聪明，但恐怕它不能很好地扩展（对于复杂的查询，您必须加入并计算每行当前行之前的行数）。
I would consider using create table tmp as select /*your query*/.
because in the case of a create as select operation the rowid created when inserting
the rows is exactly what would be the rownum (a counter). It is specified by the SQLite doc.
我会考虑使用create table tmp as select /*your query*/. 因为在 create as select 操作的情况下，插入行时创建的 rowid 正是 rownum （计数器）。它由 SQLite 文档指定。
Once the initial query has been inserted, you only need to query the tmp table:
插入初始查询后，您只需要查询 tmp 表：
select rowid, /* your columns */ from tmp
order by rowid
回答by chue x
You can use offset/limit.
您可以使用偏移/限制。
Get the first, 2nd, and 3rd groups of five rows:
获取第一、二、三组五行：
select rowid, name from contactinfo order by name limit 0, 5
select rowid, name from contactinfo order by name limit 5, 5
select rowid, name from contactinfo order by name limit 10, 5
Warning, using the above syntax requires SQLite to read through all prior records in sorted order. So to get the 10th record for statement number 3 above SQLite needs to read the first 9 records. If you have a large number of records this can be problematic from a performance standpoint.
警告，使用上述语法需要 SQLite 按排序顺序读取所有先前的记录。所以要获取上面语句 3 的第 10 条记录，SQLite 需要读取前 9 条记录。如果您有大量记录，从性能的角度来看这可能会出现问题。
More info on limit/ offset:
有关限制/偏移的更多信息：
