mysql - SQL ROW_NUMBER 给出错误

Question

我需要对 MySQL 中的行进行排序，并根据该顺序为每一行分配一个编号。 ORDER BY 按预期工作，但不是 ROW_NUMBER()。

这个有效:

USE my_database;
SELECT
    id
    ,volume
    FROM my_table
    ORDER BY volume;

这不起作用:

USE my_database;
SELECT
    id
    ,volume
    ,ROW_NUMBER() over(ORDER BY volume)
    FROM my_table
    ORDER BY volume;

我收到此错误消息:

SELECT id ,volume ,ROW_NUMBER() over(ORDER BY volume) FROM my_table ORDER BY volume Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY volume) FROM my_table ORDER BY vol' at line 4 0.000 sec

我哪里做错了，我该如何让它发挥作用？

我还尝试了 RANK() 和 DENSE_RANK()，它们给出了同样的问题。

Answer 1

MySQL 中没有ROW_NUMBER() 或RANK() 这样的东西。尝试以下操作:

USE my_database;
SET @row_number = 0; 
SELECT id
     , volume
     , @row_number := @row_number + 1 AS rank
FROM my_table
ORDER BY volume;