mysql - 如何使用groupby对mysql进行排名

Question

我正在尝试对数据库中的一些数据进行排名，我注意到当我放置 group by 子句时，情况变得非常错误；

SET @rank=0;

SELECT @rank:=@rank+1 AS RankSemGenero
    ,a.nome AS Artista
    ,f.nome AS Musica
    ,SUM(rnk.total) AS Tocadas
    ,rnk.mes AS Mes
    ,rnk.dia AS Dia
    ,current_timestamp() AS Criado_Em_Sem_Genero
    ,23 AS RankComGenero
    ,current_timestamp() AS Criado_Em_Com_Genero
    /*,CASE rnk.categoria
        WHEN 1 then 'AM'
        WHEN 2 then 'FM'
        WHEN 3 then 'Web'
        WHEN 4 then 'Comunitaria'
    END AS Categoria_Radio*/
    ,'Todas' AS TipoEmissora
    ,5 AS Relevancia_Emissora
    ,'Nacional' AS Local
    ,5 AS Relevancia_Local
    ,1 AS fl_ativo
FROM rnk201901 rnk
LEFT JOIN artistas a ON rnk.artista = a.id
LEFT JOIN fonogramas f ON rnk.fonograma = f.id
WHERE rnk.dia = 10
-- AND rnk.fonograma = 35876
-- GROUP BY rnk.fonograma
ORDER BY rnk.total DESC;

上面的代码以正确的方式提供信息 1 直到 ....

但是如果我更改 GROUP BY 行，我会收到类似以下内容的信息:1700 而不是 1。

GROUP BY rnk.fonograma

知道如何通过 1 乘 1 来处理这个组吗？

谢谢!!

Answer 1

在group by中使用变量时，您需要使用子查询:

select (@rank := @rank + 1) as rank, t.*
from (<your aggregation query here with order by>) t cross join
     (select @rank := 0) params;