如何编写MySQL查询语句获取每年评论量前三的作者？

某博客允许对其条目进行评论，本次需求仅需使用writer和comment两张表，涉及字段包括：

• writer.id
• writer.name
• comment.id
• comment.author_id（外键）
• comment.created（datetime类型）

是否存在一条MySQL查询语句，能够输出每年评论量最高的三位作者？输出格式为三列：

1. 年份
2. 作者姓名
3. 该作者在对应年份的评论数量

假设存在足够的作者和评论且结果无并列情况，输出行数应为博客运营年份数的3倍（每年3行）。

若仅统计单一年份的数据，可通过以下语句实现：

SELECT YEAR(c.created), w.name, COUNT(c.id) as nbrOfComments
FROM comment AS c
INNER JOIN writer AS w
ON c.author_id = w.id 
WHERE YEAR(c.created)='2021'
GROUP BY w.id

为便于测试，以下提供用于创建并初始化表的MySQL代码：

CREATE TABLE  `writer` (
   `id` int NOT NULL AUTO_INCREMENT,
   `name` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
CREATE TABLE  `comment` (
   `id` int NOT NULL AUTO_INCREMENT,
   `content` TEXT COLLATE utf8mb3_unicode_ci NOT NULL,
   `author_id` int NOT NULL,
   `created` datetime NOT NULL,
   PRIMARY KEY (`id`),
   FOREIGN KEY(author_id) REFERENCES writer(id)
 ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
INSERT INTO `writer` (`id`, `name`) VALUES 
 (1, 'Alf'),(2, 'Bob'),(3, 'Cathy'),(4, 'David'),
 (5, 'Eric'),(6, 'Fanny'),(7, 'Gabriel'),(8, 'Hans'),
 (9, 'Ibrahim'),(10, 'James'),(11, 'Kevin'),(12, 'Lena');
 
INSERT INTO `comment` (`id`, `author_id`, `content`, `created`) VALUES
(NULL, '1', 'some text here', '2021-01-22 06:40:31.000000'),
(NULL, '1', 'some text here', '2021-02-22 06:40:31.000000'),
(NULL, '1', 'some text here', '2021-03-22 06:40:31.000000'),
(NULL, '1', 'some text here', '2021-04-22 06:40:31.000000'),
(NULL, '2', 'some text here', '2021-01-22 06:40:31.000000'),
(NULL, '2', 'some text here', '2021-02-22 06:40:31.000000'),
(NULL, '2', 'some text here', '2021-03-22 06:40:31.000000'),
(NULL, '3', 'some text here', '2021-01-22 06:40:31.000000'),
(NULL, '3', 'some text here', '2021-02-22 06:40:31.000000'),
(NULL, '4', 'some text here', '2021-01-22 06:40:31.000000'),
(NULL, '5', 'some text here', '2022-01-22 06:40:31.000000'),
(NULL, '5', 'some text here', '2022-02-22 06:40:31.000000'),
(NULL, '5', 'some text here', '2022-03-22 06:40:31.000000'),
(NULL, '5', 'some text here', '2022-04-22 06:40:31.000000'),
(NULL, '6', 'some text here', '2022-01-22 06:40:31.000000'),
(NULL, '6', 'some text here', '2022-02-22 06:40:31.000000'),
(NULL, '6', 'some text here', '2022-03-22 06:40:31.000000'),
(NULL, '7', 'some text here', '2022-01-22 06:40:31.000000'),
(NULL, '7', 'some text here', '2022-02-22 06:40:31.000000'),
(NULL, '8', 'some text here', '2022-01-22 06:40:31.000000'), 
(NULL, '9', 'some text here', '2023-01-22 06:40:31.000000'),
(NULL, '9', 'some text here', '2023-02-22 06:40:31.000000'),
(NULL, '9', 'some text here', '2023-03-22 06:40:31.000000'),
(NULL, '9', 'some text here', '2023-04-22 06:40:31.000000'),
(NULL, '10', 'some text here', '2023-01-22 06:40:31.000000'),
(NULL, '10', 'some text here', '2023-02-22 06:40:31.000000'),
(NULL, '10', 'some text here', '2023-03-22 06:40:31.000000'),
(NULL, '11', 'some text here', '2023-01-22 06:40:31.000000'),
(NULL, '11', 'some text here', '2023-02-22 06:40:31.000000'),
(NULL, '12', 'some text here', '2023-01-22 06:40:31.000000');

可以通过窗口函数ROW_NUMBER()实现按年份分组取TOP3的需求，具体SQL语句如下：

SELECT year, name, nbrOfComments
FROM (
    SELECT 
        YEAR(c.created) AS year,
        w.name,
        COUNT(c.id) AS nbrOfComments,
        ROW_NUMBER() OVER (PARTITION BY YEAR(c.created) ORDER BY COUNT(c.id) DESC) AS rn
    FROM comment c
    INNER JOIN writer w ON c.author_id = w.id
    GROUP BY YEAR(c.created), w.id, w.name
) AS ranked_authors
WHERE rn <= 3
ORDER BY year, rn;

语句说明 #

1. 内层子查询：按年份和作者分组，统计每个作者每年的评论数，同时用ROW_NUMBER()窗口函数按年份分区，按评论数降序给每个作者生成排名（rn字段）。
2. 外层查询：筛选出排名前3的记录，最后按年份和排名排序输出。

因题目假设无并列情况，使用ROW_NUMBER()即可；若需保留并列结果，可替换为RANK()或DENSE_RANK()。

内容的提问来源于stack exchange，提问作者Henry2025