编写SQL查询统计指定艺人的歌曲、混音及总数（按项目排序）

原查询存在两个核心问题：

1. 仅通过Song表关联Credit_To_Artist，导致关联混音的记录（remix_id非空、song_id为空）无法被匹配到；
2. 同时关联Song和Remix到Project会产生笛卡尔积，可能导致统计结果失真。

以下是两种可行的修正方案：

方式一：子查询分离统计 #

通过子查询分别统计歌曲和混音的数量，再关联到项目表，逻辑清晰且避免笛卡尔积问题：

SELECT 
    1 AS artist_id,
    p.project_id,
    p.project_name,
    COALESCE(s.songcount, 0) AS songcount,
    COALESCE(r.remixcount, 0) AS remixcount,
    COALESCE(s.songcount, 0) + COALESCE(r.remixcount, 0) AS total
FROM Project p
LEFT JOIN (
    -- 统计艺人1在各项目中的歌曲数量
    SELECT s.project_id, COUNT(DISTINCT c2a.song_id) AS songcount
    FROM Credit_To_Artist c2a
    INNER JOIN Song s ON c2a.song_id = s.song_id
    WHERE c2a.artist_id = 1
    GROUP BY s.project_id
) s ON p.project_id = s.project_id
LEFT JOIN (
    -- 统计艺人1在各项目中的混音数量
    SELECT rm.project_id, COUNT(DISTINCT c2a.remix_id) AS remixcount
    FROM Credit_To_Artist c2a
    INNER JOIN Remix rm ON c2a.remix_id = rm.remix_id
    WHERE c2a.artist_id = 1
    GROUP BY rm.project_id
) r ON p.project_id = r.project_id
-- 过滤艺人1无参与的项目
WHERE COALESCE(s.songcount, 0) + COALESCE(r.remixcount, 0) > 0
ORDER BY p.project_name ASC;

方式二：直接关联统计 #

从Credit_To_Artist表出发，通过COALESCE获取项目ID，直接统计各类数量：

SELECT 
    c2a.artist_id,
    p.project_id,
    p.project_name,
    COUNT(DISTINCT c2a.song_id) AS songcount,
    COUNT(DISTINCT c2a.remix_id) AS remixcount,
    COUNT(*) AS total -- 每条记录对应一个歌曲/混音条目，直接计数即可
FROM Credit_To_Artist c2a
LEFT JOIN Song s ON c2a.song_id = s.song_id
LEFT JOIN Remix rm ON c2a.remix_id = rm.remix_id
INNER JOIN Project p ON p.project_id = COALESCE(s.project_id, rm.project_id)
WHERE c2a.artist_id = 1
GROUP BY p.project_id, p.project_name, c2a.artist_id
ORDER BY p.project_name ASC;

预期输出 #

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