mysql - SQL 查询从年份范围返回 "most nominated"，作为列表(带有新列)

Question

我有一个表格(指定)，如下所示:

Year  Category      Artist-Name                Title          Album
1980  AlbumOfYear   Michael Jackson            Thriller       Thriller
1980  ...           ...
1981  ...           ...
1982  ...           ...

我正在尝试开发一个查询，该查询将返回用户输入的一系列年份中提名次数最多的艺术家姓名，然后按降序返回列表。这样的事情可能吗？ (根据查询创建提名和排名的新列)

类似的东西

Rank   Artist              Nominations
1      Michael Jackson     3
2      Billy Joel          2

Answer 1

SET @fromyear = 1980;
SET @toyear = 1992;


SET @rank = 0;


SELECT 
@rank:=@rank+1 as rank, 
noms.artist, 
noms.nominations
FROM
    (
    SELECT COUNT(*) as nominations, `Artist-Name` as artist 
    FROM `Nominations`
    WHERE `Year` BETWEEN @fromyear AND @toyear
    GROUP BY `Artist-Name`
    ORDER BY COUNT(*) DESC
    ) as noms;