MySQL查询中的每一行都需要一个序列号 [英] Need a sequence number for every row in MySQL query

问题描述

所以我发现这很有用:

SELECT (@row:=@row+1) AS ROW, ID  
FROM TableA ,(SELECT @row := 0) r   
ORDER BY ID DESC

@row:=@row+1的效果很好，但是我得到了ID排序的行.

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

注意: 我注意到if I remove the JOINs I DO get the requested result(其中ROW是每行的顺序号，无论ID的ORDER BY如何).第一个示例很好用，但出于某种原因，JOIN将其弄乱了.

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.

所以我明白了:

ROW  | ID  
3      15  
2      10  
1      2

我追求的是:

ROW  | ID  
1      15  
2      10  
3      2

这是 SqlFiddle

Here's the SqlFiddle

因此，基本上看来，行号是在ORDER BY发生之前进行评估的.我需要在给出行之后进行ORDER BY.

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.

我该如何实现?

推荐答案

删除ORDER BY:

SELECT (@row:=@row+1) AS ROW, ID  
FROM table1 ,(SELECT @row := 0) r   

请参见带有演示的SQL提琴

然后，如果要使用ORDER BY，则将查询包装在另一个SELECT中:

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

或者如果在查询中保留ORDER BY，则可以通过简单地使用DESC或ASC顺序-

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

如果您使用DESC订单

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

如果您使用ASC订单:

SELECT (@row:=@row+1) AS ROW, ID  
FROM table1 ,(SELECT @row := 0) r   
ORDER BY ID;

结果是:

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;

请参见带有演示的SQL提琴

这篇关于MySQL查询中的每一行都需要一个序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！