如何按范围选择行?(复制)

时间:2022-10-04 09:22:16

Possible Duplicate:
Select statement in SQLite recognizing row number

可能重复:在SQLite中选择语句识别行号

For example, SELECT * FROM table WHERE [row] BETWEEN x AND y

例如,在x和y之间的[row]表中选择*

How can this be done? I've done some reading but haven't found anything specifically correct.

怎么做呢?我读了一些书,但没有发现任何特别正确的地方。

Imagine a list where you want results paged by an X amount of results, so for page 10 you would need results from rows 10 * X to 10 * X + X. Rather than display ALL results in one go

想象一个列表,您希望结果按一定数量的结果进行分页,因此对于第10页,您需要10 * X到10 * X + X的结果,而不是一次性显示所有结果

7 个解决方案

#1


40  

For mysql you have limit, you can fire query as :

对于mysql有限制,可以按以下方式进行查询:

SELECT * FROM table limit 100` -- get 1st 100 records
SELECT * FROM table limit 100, 200` -- get 200 records beginning with row 101

For Oracle you can use rownum

对于Oracle,您可以使用rownum

See mysql select syntax and usage for limit here.

请参阅mysql选择语法和用法。

For SQLite, you have limit, offset. I haven't used SQLite but I checked it on SQLite Documentation. Check example for SQLite here.

对于SQLite,你有极限,偏移量。我没有使用SQLite,但是我在SQLite文档中检查过。这里检查SQLite的示例。

#2


9  

You can use rownum :

你可以使用rownum:

SELECT * FROM table WHERE rownum > 10 and rownum <= 20

#3


5  

Following your clarification you're looking for limit:

在你的澄清之后,你正在寻找限制:

SELECT * FROM `table` LIMIT 0, 10 

This will display the first 10 results from the database.

这将显示来自数据库的前10个结果。

SELECT * FROM `table` LIMIT 5, 5 .

Will display 5-9 (5,6,7,8,9)

将显示5 - 9(5、6、7、8、9)

The syntax follows the pattern:

语法遵循以下模式:

SELECT * FROM `table` LIMIT [row to start at], [how many to include] .

The SQL for selecting rows where a column is between two values is:

选择列位于两个值之间的行的SQL是:

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

See: http://www.w3schools.com/sql/sql_between.asp

参见:http://www.w3schools.com/sql/sql_between.asp

If you want to go on the row number you can use rownum:

如果你想使用行号,可以使用rownum:

SELECT column_name(s)
FROM table_name
WHERE rownum 
BETWEEN x AND y

However we need to know which database engine you are using as rownum is different for most.

但是,我们需要知道使用哪个数据库引擎作为rownum是不同的。

#4


2  

Using Between condition

之间的使用条件

SELECT *
FROM TEST
WHERE COLUMN_NAME BETWEEN x AND y ;

Or using Just operators,

或者使用运营商,

SELECT *
FROM TEST
WHERE COLUMN_NAME >= x AND COLUMN_NAME   <= y;

#5


1  

Have you tried your own code?
This should work:

你试过你自己的代码吗?这应该工作:

SELECT * FROM people WHERE age BETWEEN x AND y

#6


1  

Use the LIMIT clause:

使用限制条款:

/* rows x- y numbers */
SELECT * FROM tbl LIMIT x,y;

refer : http://dev.mysql.com/doc/refman/5.0/en/select.html

参见:http://dev.mysql.com/doc/refman/5.0/en/select.html

#7


0  

Assuming id is the primary key of table :

假设id是表的主键:

SELECT * FROM table WHERE id BETWEEN 10 AND 50

For first 20 results

前20个结果

SELECT * FROM table order by id limit 20;

#1


40  

For mysql you have limit, you can fire query as :

对于mysql有限制,可以按以下方式进行查询:

SELECT * FROM table limit 100` -- get 1st 100 records
SELECT * FROM table limit 100, 200` -- get 200 records beginning with row 101

For Oracle you can use rownum

对于Oracle,您可以使用rownum

See mysql select syntax and usage for limit here.

请参阅mysql选择语法和用法。

For SQLite, you have limit, offset. I haven't used SQLite but I checked it on SQLite Documentation. Check example for SQLite here.

对于SQLite,你有极限,偏移量。我没有使用SQLite,但是我在SQLite文档中检查过。这里检查SQLite的示例。

#2


9  

You can use rownum :

你可以使用rownum:

SELECT * FROM table WHERE rownum > 10 and rownum <= 20

#3


5  

Following your clarification you're looking for limit:

在你的澄清之后,你正在寻找限制:

SELECT * FROM `table` LIMIT 0, 10 

This will display the first 10 results from the database.

这将显示来自数据库的前10个结果。

SELECT * FROM `table` LIMIT 5, 5 .

Will display 5-9 (5,6,7,8,9)

将显示5 - 9(5、6、7、8、9)

The syntax follows the pattern:

语法遵循以下模式:

SELECT * FROM `table` LIMIT [row to start at], [how many to include] .

The SQL for selecting rows where a column is between two values is:

选择列位于两个值之间的行的SQL是:

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

See: http://www.w3schools.com/sql/sql_between.asp

参见:http://www.w3schools.com/sql/sql_between.asp

If you want to go on the row number you can use rownum:

如果你想使用行号,可以使用rownum:

SELECT column_name(s)
FROM table_name
WHERE rownum 
BETWEEN x AND y

However we need to know which database engine you are using as rownum is different for most.

但是,我们需要知道使用哪个数据库引擎作为rownum是不同的。

#4


2  

Using Between condition

之间的使用条件

SELECT *
FROM TEST
WHERE COLUMN_NAME BETWEEN x AND y ;

Or using Just operators,

或者使用运营商,

SELECT *
FROM TEST
WHERE COLUMN_NAME >= x AND COLUMN_NAME   <= y;

#5


1  

Have you tried your own code?
This should work:

你试过你自己的代码吗?这应该工作:

SELECT * FROM people WHERE age BETWEEN x AND y

#6


1  

Use the LIMIT clause:

使用限制条款:

/* rows x- y numbers */
SELECT * FROM tbl LIMIT x,y;

refer : http://dev.mysql.com/doc/refman/5.0/en/select.html

参见:http://dev.mysql.com/doc/refman/5.0/en/select.html

#7


0  

Assuming id is the primary key of table :

假设id是表的主键:

SELECT * FROM table WHERE id BETWEEN 10 AND 50

For first 20 results

前20个结果

SELECT * FROM table order by id limit 20;