Mysql 列出每年最多产的三位作家

博客允许对其条目进行 comments .就我们这里的目的而言，我们只需要两张桌子，writer和comment， 我们需要的所有字段是Writer.id、Writer.name、Comment.id、Comment.Author_id(外键)和Comment.Created(日期时间).

Question.是否有MySQL请求将输出每年最多产的三个 comments 作者的列表， 格式如下:三栏，第一栏是年份，第二栏是作者姓名，第三栏是数字 给定作者在给定年份发表的 comments . 因此，假设有足够的编写者和注释并且结果不相等，则输出中的行数应为 是博客存在年数的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');