如何在MySQL Workbench中查询拥有最多重复曲目的作曲家

原有查询的问题 #

原有SQL按「曲目名+作曲家」分组统计，得到的是单首曲目的重复次数，同一作曲家如果有多首重复曲目会生成多条记录，无法直接得到作曲家维度的统计结果。

调整方案 #

方案1：统计作曲家名下所有重复曲目的总重复次数（所有重复的曲目记录数求和），取最高值 #

SELECT 
    Composer,
    SUM(track_dup_count) AS total_dup_count
FROM (
    -- 子查询先获取每首重复曲目的重复次数
    SELECT 
        Name, 
        Composer, 
        COUNT(*) AS track_dup_count 
    FROM Track
    GROUP BY Name, Composer
    HAVING COUNT(*) > 1
) AS track_dup
GROUP BY Composer
ORDER BY total_dup_count DESC
LIMIT 1;

方案2：统计作曲家名下有多少首不同的重复曲目（只要曲目重复就算1次，不统计重复次数），取最高值 #

SELECT 
    Composer,
    COUNT(DISTINCT Name) AS dup_track_num
FROM (
    SELECT 
        Name, 
        Composer
    FROM Track
    GROUP BY Name, Composer
    HAVING COUNT(*) > 1
) AS track_dup
GROUP BY Composer
ORDER BY dup_track_num DESC
LIMIT 1;

补充说明 #

如果存在多名作曲家并列最高的情况，可使用窗口函数RANK()筛选所有排名第一的记录，示例如下：

WITH composer_stats AS (
    SELECT 
        Composer,
        SUM(track_dup_count) AS total_dup_count,
        RANK() OVER(ORDER BY SUM(track_dup_count) DESC) AS rank_num
    FROM (
        SELECT 
            Name, 
            Composer, 
            COUNT(*) AS track_dup_count 
        FROM Track
        GROUP BY Name, Composer
        HAVING COUNT(*) > 1
    ) AS track_dup
    GROUP BY Composer
)
SELECT Composer, total_dup_count
FROM composer_stats
WHERE rank_num = 1;

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