SQL对不带ROW_Number()且分区依据的行进行编号?MariaDB 10.1

Question

我想这样做：

SELECT * , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY score DESC, creationDate DESC) as num
FROM DB
ORDER BY score DESC, creationDate DESC

但在MariaDB 10.1中，因此row_number不可用。我做到了：

SELECT *, (@num:= @num + 1) AS num
from DB,
(SELECT @num:= 0 AS num1) AS c
order by score DESC, creationDate DESC

但我不知道该如何划分分区，所以我可以为任何Id开始新的计数(最终目标是按创建的数量订购(

感谢

Answer 1

您可以将其表示为：

SELECT db.*,
(@rn := IF(@id = id, @rn + 1,
IF(@id := id, 1, 1)
)
) as num
from (SELECT db.*
FROM DB
ORDER BY id, score DESC, creationDate DESC
) db CROSS JOIN
(SELECT @rn := 0, @id := '') params;

注意：这使用子查询在执行计算之前对行进行排序。这在最近的MySQL 8.0之前的版本中是需要的。我不知道MariaDB是否真的需要它。