MySQL行号 [英] MySQL row number

问题描述

说我有一个MySQL查询，例如:

Say I have a MySQL query, for example:

SELECT id, name, surname FROM employees ORDER BY id

结果将是:

id    name    surname
 1     Peter   Smith
 2     John    Banjo
...
 1384  Will    Levenstein

虽然这是一个有序查询，但我始终可以假设(只要不更改表)John Banjo将排在第二位.

While this is an ordered query, I can always assume (as long as I don't change the table) that John Banjo will come out second.

现在，如果我的查询是

SELECT id, name, surname FROM employees WHERE name = 'John' AND surname = 'Banjo'

我能以某种方式获得第一个查询中的行号吗? 我正尝试在一个更为复杂但始终有序的查询中执行此操作，有什么办法可以存档吗?

Could I somehow get what the row number would be in the first query? I'm trying to do this in a much more complicated, but always ordered query, is there any way to archieve this?

推荐答案

 SELECT x.id, x.name, x.surname, x.rownum
 FROM (
      SELECT @rownum:=@rownum+1 rownum, t.*
      FROM (SELECT @rownum:=0) r, employees t
      ORDER BY Id
 ) x
 WHERE x.name = 'John' 
 AND x.surname = 'Banjo'

这篇关于MySQL行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！