mysql 查询一个表的序列号,MySQL查询中的每一行都需要一个序列号

So I found this great use:

SELECT (@row:=@row+1) AS ROW, ID

FROM TableA ,(SELECT @row := 0) r

ORDER BY ID DESC

The @row:=@row+1 works great, but I get the row ordered by the ID.

My tables look more like this:

SELECT (@row:=@row+1) AS ROW, ID , ColA, ColB, ColC

FROM TableA

JOIN TableB on TableB.ID = TableA.ID

JOIN TableC on TableC.ID = TableA.ID

WHERE ID<500

,(SELECT @row := 0) r

ORDER BY ID DESC

Note:

I noticed that if I remove the JOINs I DO get the requested result (In Which ROW is the sequential number of each row, no matter the ORDER BY of ID). The first example works great but for some reaosn, the JOINs mess it up somehow.

so I get this:

ROW | ID

3 15

2 10

1 2

What I am after is:

ROW | ID

1 15

2 10

3 2

Here's the

So it basically seems that the row number is evaluated before the ORDER BY takes place. I need the ORDER BY to take place after row was given.

How can I achieve that?

解决方案

Remove the ORDER BY:

SELECT (@row:=@row+1) AS ROW, ID

FROM table1 ,(SELECT @row := 0) r

Then if you want to use an ORDER BY wrap the query in another SELECT:

select *

from

(

SELECT (@row:=@row+1) AS ROW, ID

FROM table1 ,(SELECT @row := 0) r

) x

order by row

Or if you leave the ORDER BY on the query, then you can see the way the row number is being applied by simply playing with either DESC or ASC order - See Demo

If you use DESC order

SELECT (@row:=@row+1) AS ROW, ID

FROM table1, (SELECT @row := 0) r

order by id desc;

the results are which appears to be the result you want:

ROW | ID

----------

1 | 15

2 | 10

3 | 2

If you use ASC order:

SELECT (@row:=@row+1) AS ROW, ID

FROM table1 ,(SELECT @row := 0) r

ORDER BY ID;

the results are:

ROW | ID

----------

1 | 2

2 | 10

3 | 15

Edit, based on your change, you should place the row number in a sub-query, then join the other tables:

select *

from

(

SELECT (@row:=@row+1) AS ROW, ID

FROM Table1,(SELECT @row := 0) r

order by ID desc

) x

JOIN Table2

on x.ID = Table2.ID;