MySQL 从mysql表中选择特定行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10457458/
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
Select specific row from mysql table
提问by Nu Gnoj Mik
Ideally I need a query that is equivalent to
理想情况下，我需要一个相当于
select * from customer where row_number() = 3
but that's illegal.
但这是非法的。
I can't use an auto incremented field.
我不能使用自动递增的字段。
row_number() is the row that needs to be selected.
row_number() 是需要选择的行。
How do I go about this?
我该怎么做？
EDIT: Well, I use iSql*plus to practice, and using limit and auto_increment is illegal for some reason. I ended up creating a sequence and a trigger and just upped the id by 1 every time there was an entry.
编辑：嗯，我使用 iSql*plus 来练习，由于某种原因使用 limit 和 auto_increment 是非法的。我最终创建了一个序列和一个触发器，并在每次有条目时将 id 增加 1。
回答by sp00m
You can use LIMIT 2,1instead of WHERE row_number() = 3.
您可以使用LIMIT 2,1代替WHERE row_number() = 3.
As the documentationexplains, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.
正如文档所解释的，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。
Keep in mind that it's an 0-based index. So, if you want the line number n, the first argument should be n-1. The second argument will always be 1, because you just want one row. For example, if you want the line number 56of a table customer:
请记住，它是一个基于 0 的索引。所以，如果你想要行号n，第一个参数应该是n-1。第二个参数将始终为1，因为您只需要一行。例如，如果您想要表格的第56行customer：
SELECT * FROM customer LIMIT 55,1
回答by Sanath
SET @customerID=0;
SELECT @customerID:=@customerID+1 AS customerID
FROM CUSTOMER ;
you can obtain the dataset from SQL like this and populate it into a java data structure (like a List) and then make the necessary sorting over there. (maybe with the help of a comparable interface)
您可以像这样从 SQL 获取数据集并将其填充到 Java 数据结构（如列表）中，然后在那里进行必要的排序。（也许在类似界面的帮助下）
回答by Starx
You cannot select a row like that. You have to specify a field whose values will be 3
您不能选择这样的行。您必须指定一个值为3的字段
Here is a query that will work, if the field you are comparing against is id
这是一个有效的查询，如果您要比较的字段是 id
select * from customer where `id` = 3
回答by fthiella
SQL tables are not ordered by default, and asking for the n-th row from a non ordered set of rows has no meaning as it could potentially return a different row each time unless you specify an ORDER BY:
默认情况下，SQL 表是不排序的，并且从一组非有序行中请求第 n 行没有意义，因为除非您指定 ORDER BY，否则每次都可能返回不同的行：
select * from customer order by id where row_number() = 3
(sometimes MySQL tables are shown with an internal order but you cannot rely on this behaviour). Then you can use LIMIT offset, row_count, with a 0-based offset so row number 3 becomes offset 2:
（有时 MySQL 表以内部顺序显示，但您不能依赖此行为）。然后，您可以使用LIMIT offset, row_count, 以 0 为基础的偏移量，因此行号 3 变为偏移量 2：
select * from customer order by id
limit 2, 1
or you can use LIMIT row_count OFFSET offset:
或者你可以使用LIMIT row_count OFFSET offset：
select * from customer order by id
limit 1 offset 2
回答by Luminous_Path
Your table will need to be created with a unique ID field that will ideally have the AUTO_INCREMENT attribute. example:
您的表需要使用唯一的 ID 字段创建，该字段最好具有 AUTO_INCREMENT 属性。例子：
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
PRIMARY KEY (P_Id)
)
Then you can access the 3rd record in this table with:
然后您可以使用以下命令访问此表中的第三条记录：
SELECT * FROM Persons WHERE P_Id = 3
回答by duncanportelli
You can add an auto generated id field in the table and select by this id
您可以在表中添加一个自动生成的 id 字段并通过此 id 进行选择
SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = 3;
