mysql行号,MySQL行号

Say I have a MySQL query, for example:

SELECT id, name, surname FROM employees ORDER BY id

The result woud be:

id name surname

1 Peter Smith

2 John Banjo

...

1384 Will Levenstein

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.

Now what if my query was

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'