MySQL按升序排列的行号

Question

这个问题问了很多关于Stackoverflow的问题，但我面临的问题与它不同。

SET @rownum = 0; 
SELECT DISTINCT doc.id, 
                doc.created_date, 
                ( @rownum := @rownum + 1 ) AS rownumbers 
FROM   document AS doc 
       INNER JOIN document_type_master dt 
               ON doc.document_type_id = dt.id 
ORDER  BY doc.id ASC; 

上面的查询提供了以下输出。

id      created_date         rownumbers
5664    2014-06-05 07:23:40  1
5665    2014-06-06 06:37:34  2
5666    2014-06-06 10:25:56  3
5667    2014-06-06 10:33:39  4
5668    2014-06-06 11:28:28  5

如果我将ORDER BY doc.id ASC更改为ORDER BY doc.id DESC，则输出为

id      created_date         rownumbers
6364    2014-11-11 17:57:04  691
6363    2014-11-11 11:09:49  690
6362    2014-11-11 10:58:34  689
6361    2014-11-10 17:39:47  688
6360    2014-11-10 16:59:53  687

我做了一些实验，发现如果我从查询中删除DISTINCT，那么rownumbers就会从1,2,3...启动，以防出现ORDER BY doc.id DESC。

id      created_date         rownumbers
6364    2014-11-11 17:57:04  1
6363    2014-11-11 11:09:49  2
6362    2014-11-11 10:58:34  3
6361    2014-11-10 17:39:47  4
6360    2014-11-10 16:59:53  5

我也试着像rownumbers一样给ORDER BY doc.id DESC, rownumbers ASC下订单，但没有为我工作。

我希望rownumbers值应该始终按ASC顺序排列，无论我是否使用DISTINCT。

更新

@Barmar是正确的，但是如果我在WHERE子句中使用WHERE，则如下

rownumbers between 1 and 5则显示了一个错误Error Code: 1054. Unknown column 'rownumbers' in 'where clause'。

Answer 1

ORDER BY处理是在生成结果中的所有行之后完成的。因此，它首先通过按数据库中数据的任何顺序处理数据来分配所有的rownumber列，然后反向重新排序。

之所以只有在使用DISTINCT时才会发生这种情况，是因为这要求它首先生成所有的结果，这样它才能删除所有重复的结果。没有DISTINCT，它可以采取快捷方式并对原始表数据执行排序。然而，虽然这通常如预期的那样工作，但并不能得到保证。

要获得所需的结果，需要将ORDER BY放入子查询中：

SET @rownum = 0;
SELECT x.*, ( @rownum := @rownum + 1 ) AS rownumbers 
FROM (
    SELECT DISTINCT doc.id, 
                    doc.created_date
    FROM   document AS doc 
           INNER JOIN document_type_master dt 
                   ON doc.document_type_id = dt.id 
    ORDER  BY doc.id DESC) AS x

若要引用WHERE子句中的行号，需要另一级别的子查询：

SELECT *
FROM (
    SELECT x.*, ( @rownum := @rownum + 1 ) AS rownumbers 
    FROM (
        SELECT DISTINCT doc.id, 
                        doc.created_date
        FROM   document AS doc 
               INNER JOIN document_type_master dt 
                       ON doc.document_type_id = dt.id 
        ORDER  BY doc.id DESC) AS x
    ) AS y
WHERE rownumbers BETWEEN 1 AND 5

或者您可以使用HAVING

SELECT x.*, ( @rownum := @rownum + 1 ) AS rownumbers 
FROM (
    SELECT DISTINCT doc.id, 
                    doc.created_date
    FROM   document AS doc 
           INNER JOIN document_type_master dt 
                   ON doc.document_type_id = dt.id 
    ORDER  BY doc.id DESC) AS x
HAVING rownumbers BETWEEN 1 AND 5

但是我看到了一些奇怪的行为，当我试图使用HAVING和来自这样的变量的别名时，显然是因为MySQL所采用的快捷方式。