多作者书籍按作者姓名排序的SQL查询问题

问题原因 #

你的原查询通过LEFT JOIN关联作者表后，单本书对应多个作者会生成多条记录，DISTINCT去重后排序时，数据库会使用该书籍匹配到的任意一条关联记录的作者（通常是关联表中先出现的那个），而非该书籍所有作者中字母序最靠前的那位，导致排序结果不符合预期。

解决方案 #

核心思路是：先为每本书找到所有作者中按surname+name字母序最靠前的那位，再用这个作者作为排序依据。以下提供两种适配不同数据库的写法：

方法1：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库）

SELECT a.*
FROM articles a
LEFT JOIN (
    SELECT 
        aa.id_article,
        au.surname,
        au.name,
        -- 按作者姓名排序，给每本书的作者标记序号，最靠前的作者序号为1
        ROW_NUMBER() OVER (PARTITION BY aa.id_article ORDER BY au.surname ASC, au.name ASC) AS rn
    FROM authors_articles aa
    JOIN authors au ON aa.id_author = au.id
) AS sorted_authors 
    ON a.id = sorted_authors.id_article 
    AND sorted_authors.rn = 1 -- 只保留每本书最靠前的作者
ORDER BY sorted_authors.surname ASC, sorted_authors.name ASC, a.id ASC;

方法2：使用子查询（适用于不支持窗口函数的老版本数据库）

SELECT a.*
FROM articles a
LEFT JOIN (
    SELECT 
        aa.id_article,
        au.surname,
        au.name
    FROM authors_articles aa
    JOIN authors au ON aa.id_author = au.id
    -- 子查询找到当前书籍下字母序最靠前的作者
    WHERE (au.surname, au.name) = (
        SELECT surname, name
        FROM authors_articles aa2
        JOIN authors au2 ON aa2.id_author = au2.id
        WHERE aa2.id_article = aa.id_article
        ORDER BY au2.surname ASC, au2.name ASC
        LIMIT 1
    )
) AS sorted_authors 
    ON a.id = sorted_authors.id_article
ORDER BY sorted_authors.surname ASC, sorted_authors.name ASC, a.id ASC;

效果验证 #

针对你的示例数据：

• Book2的作者中，Steve Blue（surname为Blue）字母序最靠前
• Book3的作者Ben Brown（surname为Brown）次之
• Book1的作者John Red（surname为Red）最后
  最终排序结果会是Book2 → Book3 → Book1，完全符合需求。

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