如何将两个统计作者参与书籍数量的COUNT查询合并为一个

你可以通过UNION ALL拼接两个统计结果集后外层求和的方式实现需求，语句如下：

SELECT 
    name,
    SUM(book_count) AS total_book_count
FROM (
    -- 统计合著者身份的书籍数量
    SELECT a.name, COUNT(*) AS book_count
    FROM coauthor c 
    INNER JOIN author a ON c.name = a.code
    GROUP BY a.name
    UNION ALL
    -- 统计主作者身份的书籍数量
    SELECT a.name, COUNT(*) AS book_count
    FROM author a 
    INNER JOIN book b ON a.code = b.author
    GROUP BY a.name
) AS author_all_counts
GROUP BY name
ORDER BY total_book_count DESC;

上述语句逻辑说明：

• 内层两个子查询完全复用你已验证过的统计逻辑，不会改变原有计数结果
• UNION ALL会保留所有结果行不做去重，确保两个身份的计数都可以被叠加
• 外层按作者姓名分组求和，最终得到的结果会完全匹配你的预期：George B.（GB）计数为2+1=3，Donald C.（DC）、Roland L.（RL）、Kyle L.（KL）计数均为1。

如果你需要同时查看主作者、合著者的明细计数，可以使用左关联的写法：

SELECT 
    a.name,
    COALESCE(main_stat.main_count, 0) AS main_book_count,
    COALESCE(co_stat.co_count, 0) AS co_author_book_count,
    COALESCE(main_stat.main_count, 0) + COALESCE(co_stat.co_count, 0) AS total_book_count
FROM author a
LEFT JOIN (
    SELECT author, COUNT(*) AS main_count FROM book GROUP BY author
) main_stat ON a.code = main_stat.author
LEFT JOIN (
    SELECT name, COUNT(*) AS co_count FROM coauthor GROUP BY name
) co_stat ON a.code = co_stat.name
WHERE COALESCE(main_stat.main_count, 0) + COALESCE(co_stat.co_count, 0) > 0
ORDER BY total_book_count DESC;

其中WHERE条件的作用是过滤没有参与任何书籍的作者，不需要可以直接删除。

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