使用LEFT OUTER JOIN等查询同年代最高分书籍遇重复值问题求助

要实现按出版年份分组，获取每个年份的最高评分，同时将同年份同最高分的书籍名称合并展示，可以通过以下SQL方案实现：

核心思路 #

1. 先计算每个出版年份对应的最高评分
2. 关联原数据表，筛选出该年份中评分等于最高分的所有书籍
3. 按年份分组，用字符串聚合函数将同分数的书籍名称拼接成一个字段

不同数据库的实现代码 #

PostgreSQL 版本

SELECT
    b.yearPublished AS 出版年份,
    MAX(br.bookRating) AS 最高评分,
    STRING_AGG(b.BookName, ', ') AS 书籍名称
FROM
    bookName b
JOIN
    bookRating br ON b.uniqueBookNameId = br.uniqueBookNameId
JOIN (
    -- 子查询获取各年份的最高评分
    SELECT
        yearPublished,
        MAX(bookRating) AS max_rating
    FROM
        bookName b
    JOIN
        bookRating br ON b.uniqueBookNameId = br.uniqueBookNameId
    GROUP BY
        yearPublished
) sub ON b.yearPublished = sub.yearPublished AND br.bookRating = sub.max_rating
GROUP BY
    b.yearPublished
ORDER BY
    b.yearPublished;

MySQL 版本

SELECT
    b.yearPublished AS 出版年份,
    MAX(br.bookRating) AS 最高评分,
    GROUP_CONCAT(b.BookName SEPARATOR ', ') AS 书籍名称
FROM
    bookName b
JOIN
    bookRating br ON b.uniqueBookNameId = br.uniqueBookNameId
JOIN (
    -- 子查询获取各年份的最高评分
    SELECT
        yearPublished,
        MAX(bookRating) AS max_rating
    FROM
        bookName b
    JOIN
        bookRating br ON b.uniqueBookNameId = br.uniqueBookNameId
    GROUP BY
        yearPublished
) sub ON b.yearPublished = sub.yearPublished AND br.bookRating = sub.max_rating
GROUP BY
    b.yearPublished
ORDER BY
    b.yearPublished;

SQL Server 版本

SELECT
    b.yearPublished AS 出版年份,
    MAX(br.bookRating) AS 最高评分,
    STRING_AGG(b.BookName, ', ') AS 书籍名称
FROM
    bookName b
JOIN
    bookRating br ON b.uniqueBookNameId = br.uniqueBookNameId
JOIN (
    -- 子查询获取各年份的最高评分
    SELECT
        yearPublished,
        MAX(bookRating) AS max_rating
    FROM
        bookName b
    JOIN
        bookRating br ON b.uniqueBookNameId = br.uniqueBookNameId
    GROUP BY
        yearPublished
) sub ON b.yearPublished = sub.yearPublished AND br.bookRating = sub.max_rating
GROUP BY
    b.yearPublished
ORDER BY
    b.yearPublished;

说明 #

• 子查询的作用是先锁定每个年份的最高评分，避免直接GROUP BY时丢失同分的书籍记录
• 字符串聚合函数（STRING_AGG/GROUP_CONCAT）会将同年份同最高分的书籍名称用逗号分隔合并成一个字段，满足你需要的输出格式

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