Oracle环境下查询含最多不同流派的播放列表ID及SQL逻辑咨询

原SQL的问题分析 #

你写的SQL是按播放列表ID和流派名称分组，统计的是每个播放列表内单个流派的曲目数量——比如某列表里有3首摇滚、2首爵士，会返回两行数据：该列表ID+摇滚+3，该列表ID+爵士+2。这和需求要的「统计每个播放列表拥有的不同流派总数，再找出总数最多的播放列表」完全不符。

正确查询实现及逻辑解析 #

步骤1：统计每个播放列表的不同流派数量

先关联三张表，按播放列表ID分组，用去重计数得到每个列表的流派种类数：

SELECT 
    pt.playlistid,
    COUNT(DISTINCT g.genreid) AS distinct_genre_count
FROM playlisttrack pt
JOIN track t ON pt.trackid = t.trackid
JOIN genre g ON t.genreid = g.genreid
GROUP BY pt.playlistid

逻辑说明：

• 关联playlisttrack（播放列表-曲目关联表）、track（曲目表）、genre（流派表），获取每个播放列表下所有曲目的流派信息
• COUNT(DISTINCT g.genreid)：同一个流派可能对应多首曲目，去重后计数才是该列表的不同流派总数，用genreid比name更准确（避免流派名称重复的极端情况）
• 按playlistid分组，得到每个播放列表对应的流派种类数

步骤2：找出流派数量最多的播放列表

如果需要返回所有并列最多的播放列表，推荐用窗口函数的方式；如果只需要一个（不考虑并列），可以用子查询取最大值：

方法1：用子查询筛选最大值（支持所有SQL方言）

WITH playlist_genre_counts AS (
    SELECT 
        pt.playlistid,
        COUNT(DISTINCT g.genreid) AS distinct_genre_count
    FROM playlisttrack pt
    JOIN track t ON pt.trackid = t.trackid
    JOIN genre g ON t.genreid = g.genreid
    GROUP BY pt.playlistid
)
SELECT playlistid
FROM playlist_genre_counts
WHERE distinct_genre_count = (SELECT MAX(distinct_genre_count) FROM playlist_genre_counts)

方法2：用窗口函数排名（支持PostgreSQL、MySQL 8+、SQL Server等）

WITH ranked_playlists AS (
    SELECT 
        pt.playlistid,
        COUNT(DISTINCT g.genreid) AS distinct_genre_count,
        RANK() OVER (ORDER BY COUNT(DISTINCT g.genreid) DESC) AS rnk
    FROM playlisttrack pt
    JOIN track t ON pt.trackid = t.trackid
    JOIN genre g ON t.genreid = g.genreid
    GROUP BY pt.playlistid
)
SELECT playlistid
FROM ranked_playlists
WHERE rnk = 1

逻辑说明：

• 先通过CTE（公共表表达式）得到每个播放列表的流派数量
• 方法1：先算出所有列表的最大流派数，再筛选出等于该最大值的列表ID
• 方法2：用RANK()窗口函数给每个列表按流派数量降序排名，排名为1的就是流派最多的列表（如果有多个列表流派数相同且都是最多，都会被返回）

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