SQL优化请求：从指定类型播放列表统计高频电影名并升序展示

需求翻译 #

展示播放列表（Playlist）中流派名称（Genre_Name）为「TV Shows」或「90’s movie」的播放列表里，出现次数最多的电影名称（NameOfMovie），并按升序排列。

涉及表结构 #

• Movie：MovieID（电影ID）、NameOfMovie（电影名称）
• PlaylistMovie：MovieID（关联电影ID）、PlaylistID（关联播放列表ID）
• Playlist：PlaylistID（播放列表ID）、Genre_Name（流派名称）

原SQL的问题 #

1. 表名错误：误用了playlisttrack，实际关联表应为PlaylistMovie
2. 条件匹配错误：把流派名称的匹配字段写错了，应该用Playlist.Genre_Name，而非PlaylistMovie.PlaylistId
3. 分组字段错误：Movie.Name字段不存在，需按Movie.MovieID和Movie.NameOfMovie分组（符合SQL分组规范）
4. 缺少次数统计逻辑：没有统计电影出现的次数，无法判断哪部电影出现最多
5. 缺少排序逻辑：没有按要求对结果进行排序

正确SQL实现 #

方案1：统计所有符合条件的电影并按次数排序 #

这个方案会列出所有在目标流派播放列表中出现的电影，先按出现次数从多到少排，次数相同的按电影名称升序排：

SELECT 
    m.MovieID,
    m.NameOfMovie,
    COUNT(pm.PlaylistID) AS 出现次数
FROM 
    Movie m
INNER JOIN 
    PlaylistMovie pm ON m.MovieID = pm.MovieID
INNER JOIN 
    Playlist p ON pm.PlaylistID = p.PlaylistID
WHERE 
    p.Genre_Name IN ('TV Shows', '90’s movie')
GROUP BY 
    m.MovieID, m.NameOfMovie
ORDER BY 
    出现次数 DESC, m.NameOfMovie ASC;

方案2：仅提取出现次数最多的电影（含并列） #

如果只需要出现次数最多的那些电影（可能有多部电影次数相同），可以用窗口函数筛选：

WITH 电影次数统计 AS (
    SELECT 
        m.MovieID,
        m.NameOfMovie,
        COUNT(pm.PlaylistID) AS 出现次数,
        RANK() OVER (ORDER BY COUNT(pm.PlaylistID) DESC) AS 排名
    FROM 
        Movie m
    INNER JOIN 
        PlaylistMovie pm ON m.MovieID = pm.MovieID
    INNER JOIN 
        Playlist p ON pm.PlaylistID = p.PlaylistID
    WHERE 
        p.Genre_Name IN ('TV Shows', '90’s movie')
    GROUP BY 
        m.MovieID, m.NameOfMovie
)
SELECT MovieID, NameOfMovie, 出现次数
FROM 电影次数统计
WHERE 排名 = 1
ORDER BY NameOfMovie ASC;

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