仅在 mysql 查询中抓取第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3217217/
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
grabbing first row in a mysql query only
提问by eminem
if i had a query such as
如果我有一个查询，例如
select * from tbl_foo where name = 'sarmen'
and this table has multiple instances of name = sarmen how can i virtually assign row numbers to each row without having to create a column that auto incriments? i have a reason for what im doing and dont need an auto_incrimented col in my example.
并且该表有多个 name = sarmen 实例，我如何虚拟地为每一行分配行号，而不必创建自动增加的列？我有我在做什么的理由，并且在我的示例中不需要 auto_incrimented col。
so if each row is assign a virtual row number through sql or maybe php i will be able to print out the first row or the last row anytime i need to.
因此，如果每一行都通过 sql 或 php 分配了一个虚拟行号，我将能够随时打印出第一行或最后一行。
thnx
谢谢
回答by Mark Byers
To return only one row use LIMIT 1:
要仅返回一行，请使用LIMIT 1：
SELECT *
FROM tbl_foo
WHERE name = 'sarmen'
LIMIT 1
It doesn't make sense to say 'first row' or 'last row' unless you have an ORDER BYclause. Assuming you add an ORDER BYclause then you can use LIMIT in the following ways:
除非您有ORDER BY子句，否则说“第一行”或“最后一行”是没有意义的。假设您添加了一个ORDER BY子句，那么您可以通过以下方式使用 LIMIT：
- To get the first row use
LIMIT 1. - To get the 2nd row you can use limit with an offset:
LIMIT 1, 1. - To get the last row invert the order (change ASC to DESC or vice versa) then use
LIMIT 1.
- 要获得第一行，请使用
LIMIT 1. - 要获得第二行，您可以使用带偏移量的限制：
LIMIT 1, 1。 - 要获得最后一行反转顺序（将 ASC 更改为 DESC，反之亦然），然后使用
LIMIT 1.
回答by OMG Ponies
You didn't specify how the order is determined, but this will give you a rank value in MySQL:
您没有指定如何确定顺序，但这将在 MySQL 中为您提供一个排名值：
SELECT t.*,
@rownum := @rownum +1 AS rank
FROM TBL_FOO t
JOIN (SELECT @rownum := 0) r
WHERE t.name = 'sarmen'
Then you can pick out what rows you want, based on the rank value.
然后您可以根据排名值挑选出您想要的行。
回答by Anax
You can get the total number of rows containing a specific name using:
您可以使用以下方法获取包含特定名称的总行数：
SELECT COUNT(*) FROM tbl_foo WHERE name = 'sarmen'
Given the count, you can now get the nth row using:
给定计数，您现在可以使用以下方法获取第 n 行：
SELECT * FROM tbl_foo WHERE name = 'sarmen' LIMIT (n - 1), 1
Where 1 <= n <= COUNT(*) from the first query.
其中 1 <= n <= COUNT(*) 来自第一个查询。
Example:
示例：
getting the 3rd row
获得第三行
SELECT * FROM tbl_foo WHERE name = 'sarmen' LIMIT 2, 1
