仅在mysql查询中获取第一行

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

日Thnx

4 个解决方案

#1

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 BY clause. Assuming you add an ORDER BY clause then you can use LIMIT in the following ways:

除非你有一个ORDER BY子句，否则说“第一行”或“最后一行”是没有意义的。假设您添加了一个ORDER BY子句，那么您可以通过以下方式使用LIMIT：

To get the first row use LIMIT 1.
要获得第一行，请使用LIMIT 1。
To get the 2nd row you can use limit with an offset: LIMIT 1, 1.
要获得第二行，您可以使用带偏移的限制：LIMIT 1,1。
To get the last row invert the order (change ASC to DESC or vice versa) then use LIMIT 1.
要使最后一行反转顺序（将ASC更改为DESC，反之亦然），然后使用LIMIT 1。

#2

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.

然后，您可以根据排名值选择所需的行。

#3

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

获得第3排

SELECT * FROM tbl_foo WHERE name = 'sarmen' LIMIT 2, 1

#4

-2

use the pseudocolumn ROWNUM

使用伪列ROWNUM

#1