行号MySQL不正确

Question

我在MySQL中有以下表格：

+--------------------------------------------------+
+  id  | type | recordTimeMS | created             +
+--------------------------------------------------+
+  172 | 3    | 125015       | 2020-11-15 20:23:45 +
+  159 | 3    | 116828       | 2020-11-15 20:56:24 +
+  151 | 3    | 115015       | 2020-11-15 22:23:45 +
+  171 | 3    | 136828       | 2020-11-15 21:56:24 +
+ ....                                             +
+--------------------------------------------------+

我尝试按recordTimeMS和by created对其进行排序，并使用以下select获得行号(as rank)：

SELECT @row_number := @row_number + 1 as rank, utr.id, utr.recordTimeMS, utr.created 
    FROM UserTimeRecord utr,
        (SELECT @row_number:=0) rn
    WHERE type = 3
    ORDER BY utr.recordTimeMS, utr.created
    LIMIT 10

元素的排序是正确的，但排名不正确，结果如下：

+--------------------------------------------------+
+  rank | id  | recordTimeMS | created             +
+--------------------------------------------------+
+    3  | 151 | 115015       | 2020-11-15 22:23:45 +
+    2  | 159 | 116828       | 2020-11-15 20:56:24 +
+    1  | 172 | 125015       | 2020-11-15 20:23:45 +
+    4  | 171 | 136828       | 2020-11-15 21:56:24 +
+--------------------------------------------------+

是什么导致了这种情况，还有其他方法可以做到吗？

Answer 1

感谢@Barmar给我指出了ROW_NUMBER()-function，它使它变得简单得多：

SELECT ROW_NUMBER() OVER (ORDER BY recordTimeMS, created) as rank, 
        utr.id, utr.recordTimeMS, utr.created 
    FROM UserTimeRecord utr 
    WHERE type = 3 
    LIMIT 10 

并产生正确的结果：

+--------------------------------------------------+
+  rank | id  | recordTimeMS | created             +
+--------------------------------------------------+
+    1  | 151 | 115015       | 2020-11-15 22:23:45 +
+    2  | 159 | 116828       | 2020-11-15 20:56:24 +
+    3  | 172 | 125015       | 2020-11-15 20:23:45 +
+    4  | 171 | 136828       | 2020-11-15 21:56:24 +
+--------------------------------------------------+

Answer 2

您需要在子查询中进行排序，并在主查询中进行行数计算。

否则，在某些情况下，MySQL将首先执行@row_number赋值，然后在将这些赋值替换到结果集中后执行排序。

SELECT @row_number := @row_number + 1 as rank, utr.id, utr.recordTimeMS, utr.created 
FROM (
    SELECT id, recordTimeMS, created
    FROM UserTimeRecord 
    WHERE type = 3
    ORDER BY recordTimeMS, created
    LIMIT 10
) AS utr,
(SELECT @row_number:=0) rn