如何使用MAX嵌套SQL子查询获取段落最多作品的角色列表

你原来写的统计SQL存在两个问题：

• 分组逻辑不严谨：SELECT中取了works.id但GROUP BY用的是works.Title，如果有重名作品会出现统计错误，分组应该以作品主键works.id为唯一依据
• 缺少后续关联逻辑：没有取到段落数最高的作品ID，也没有关联作品与角色的对应关系表，无法查到角色列表

基础写法（兼容所有SQL版本，仅取单个段落数最高的作品） #

如果你的业务场景不需要处理多个作品并列段落数第一的情况，直接用嵌套子查询过滤即可：

SELECT c.*
FROM characters c
JOIN work_characters wc ON c.id = wc.character_id
WHERE wc.work_id = (
    SELECT w.id
    FROM works w
    JOIN chapters ch ON w.id = ch.work_id
    JOIN paragraphs p ON ch.id = p.chapter_id
    GROUP BY w.id
    ORDER BY COUNT(DISTINCT p.id) DESC
    LIMIT 1
)

兼容并列第一场景的写法（支持窗口函数的数据库：MySQL8+、PostgreSQL等） #

如果存在多个作品段落数并列第一的情况，用RANK()窗口函数可以把所有符合条件的作品下的角色都查出来，不会漏数据：

SELECT DISTINCT c.*
FROM (
    SELECT 
        w.id AS work_id,
        RANK() OVER(ORDER BY COUNT(DISTINCT p.id) DESC) AS paragraph_rank
    FROM works w
    JOIN chapters ch ON w.id = ch.work_id
    JOIN paragraphs p ON ch.id = p.chapter_id
    GROUP BY w.id
) work_stat
JOIN work_characters wc ON work_stat.work_id = wc.work_id
JOIN characters c ON wc.character_id = c.id
WHERE work_stat.paragraph_rank = 1

逻辑说明 #

1. 内层子查询先按作品维度统计每个作品对应的去重段落总数，按段落数倒序排序取排名最高的作品ID
2. 外层查询通过作品-角色关联中间表，匹配到该作品绑定的所有角色详情
3. 用COUNT(DISTINCT paragraphs.id)统计是为了避免关联过程中出现数据重复导致段落数统计虚高

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