Select statement in SQLite recognizing row number

问题:

I want to write SQLite statement something like this:

SELECT * FROM Table WHERE RowNumber BETWEEN 1 AND 10;

but i don't have such column RowNumber. I have primary key in my table. But is there row number by default that i could use ?

Also i am searching info about writing more complicated SQLite statement. So if you have some links in bookmarks please share.

Thanks.

回答1:

You want to use LIMIT and OFFSET

SELECT * FROM Table LIMIT 10 OFFSET 0

Which can also be expressed with the following shorthand syntax

SELECT * FROM Table LIMIT X,Y

Where X represents the offset, which is exclusive, and Y represents the quantity, so for example

SELECT * FROM Table LIMIT 50,50

Would return rows 51-100

回答2:

The automatically-created rowid for a table can be accessed by a few different names. From the SQLite documentation:

Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name.

回答3:

SELECT * FROM Table WHERE ROWID BETWEEN 1 AND 10;