SQL新手求助：解析获取各分类最短电影的两种查询方案

我是SQL新手，面对复杂查询时常常无从下手，不知道如何逐步构建并组合查询语句。现有需求：编写查询语句返回每个分类的最短电影（若有并列仅返回其一），需包含film_id、title、length、category、row_num列。以下是两种解决方案，希望有人讲解思路以理解实现逻辑。

需求原文翻译：
编写查询语句，返回每个分类的最短电影。
结果顺序无要求。
若有并列最短的情况，仅返回其中一部。
需要返回的列：film_id、title、length、category、row_num

方案一：CTE（公共表表达式）写法 #

WITH movie_ranking AS (
  SELECT  
    F.film_id,
    F.title, 
    F.length, 
    C.name category,
    ROW_NUMBER() OVER(PARTITION BY C.name ORDER BY F.length) row_num    
  FROM film F
  INNER JOIN film_category FC
  ON FC.film_id = F.film_id
  INNER JOIN category C
  ON C.category_id = FC.category_id
) 
SELECT 
  film_id,
  title,
  length,
  category,
  row_num
FROM movie_ranking
WHERE row_num = 1
;

方案二：子查询写法 #

SELECT 
  film_id,
  title,
  length,
  category,
  row_num
FROM (
  SELECT  
    F.film_id,
    F.title, 
    F.length, 
    C.name category,
    ROW_NUMBER() OVER(PARTITION BY C.name ORDER BY F.length) row_num    
  FROM film F
  INNER JOIN film_category FC
  ON FC.film_id = F.film_id
  INNER JOIN category C
  ON C.category_id = FC.category_id
) X
WHERE row_num = 1
;

思路详解 #

这两个方案核心逻辑完全一致，只是临时数据集的定义方式不同，下面拆解核心步骤：

1. 关联表获取完整数据 #

首先通过三次表关联，把电影的基本信息、分类关联关系、分类名称整合到一起：

• film表存电影的核心信息（film_id、title、length）
• film_category是中间关联表，用来绑定电影和对应的分类ID
• category表存分类的名称（name）
  用INNER JOIN只保留有明确分类的电影，无分类的电影会被排除。

2. 窗口函数实现分类内排名 #

核心是ROW_NUMBER() OVER(PARTITION BY C.name ORDER BY F.length)这个窗口函数：

• PARTITION BY C.name：按分类名称拆分数据，每个分类单独处理
• ORDER BY F.length：在每个分类内部，按电影长度从小到大排序，最短的排在最前面
• 这个函数会给每一行生成唯一的row_num，同一分类里最短的电影row_num=1；如果有多部电影长度相同，ROW_NUMBER()会随机给它们分配不同的序号，刚好满足“并列仅返回其一”的要求。

3. 筛选出每个分类的第一名 #

不管是CTE还是子查询，都是先生成带排名的完整数据集，然后在外层查询中通过WHERE row_num = 1筛选出每个分类里排名第一的电影，也就是我们要的最短电影。

两种写法的区别 #

• CTE写法：用WITH定义一个临时视图，逻辑拆分清晰，可读性强，适合复杂查询的分步调试和维护。
• 子查询写法：把排名逻辑嵌套在内部，语句更紧凑，适合简单场景，不需要单独定义临时视图。

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