多表关联去重：SQL查询避免重复并优先显示complete状态

需要从多个SQL表中提取数据，包含主表（topics）和若干子表，目标是获取主表的所有行，但当前查询会产生重复记录。具体要求如下：
展示所有topics及其状态ch_status，若同一主题同时存在In-Progress和complete状态，需优先显示complete状态。

业务表说明 #

• Topics表：存储各类主题
• used_topics_chapters表：存储主题与章节的关联关系
• completed表：用户开始学习章节时，插入包含进度状态ch_status和user_id的记录

示例数据 #

1  Topic1  InProgress 1
2  Topic1  Complete   0
3  Topic2  AnotherStatus 1
4 Topic3   NoStatus   1
5  Topic2  complete  0

期望展示结果 #

1  Topic1  Complete   0
2  Topic2  Complete   0
3 Topic3   NoStatus   1

已尝试的代码与样本数据 #

Create table topics
(
  id int,
  name varchar(10)
)

insert into topics values(1,'Topic 1'),(2,'Topic 2'),(3,'Topic 3')

-- 创建主题-章节关联表
create table used_topics_chapters(id int,topic_id int,name varchar(10))

insert into used_topics_chapters values(1,1,'Chap1'),(2,3,'Chap 2'),(3,3,'Chap3')

-- 创建进度记录表
create table completed
(
  id int,
  chp_id int,
  ch_status varchar(20),
  user_id int
)

insert into completed values(1,3,'complete',100),(2,2,'In-Progress',101)

-- 当前查询语句
select t.id,t.name,c.ch_status,
case c.ch_status when 'complete' then 0 else 1 end as can_modify
from topics t
left join used_topics_chapters as utc on t.id=utc.topic_id
left join completed as c on c.chp_id=utc.id

当前输出 #

+----+---------+-------------+------------+
| id | name    | ch_status   | can_modify |
+----+---------+-------------+------------+
| 1  | Topic 1 | (null)      | 1          |
+----+---------+-------------+------------+
| 2  | Topic 2 | (null)      | 1          |
+----+---------+-------------+------------+
| 3  | Topic 3 | In-Progress | 1          |
+----+---------+-------------+------------+
| 3  | Topic 3 | complete    | 0          |
+----+---------+-------------+------------+

期望输出 #

+----+---------+-----------+------------+
| id | name    | ch_status | can_modify |
+----+---------+-----------+------------+
| 1  | Topic 1 | (null)    | 1          |
+----+---------+-----------+------------+
| 2  | Topic 2 | (null)    | 1          |
+----+---------+-----------+------------+
| 3  | Topic 3 | complete  | 0          |
+----+---------+-----------+------------+

解决方案 #

使用窗口函数ROW_NUMBER()对每个主题的状态进行优先级排序，优先保留complete状态的记录，具体SQL如下：

WITH ranked_status AS (
    SELECT 
        t.id,
        t.name,
        c.ch_status,
        CASE c.ch_status WHEN 'complete' THEN 0 ELSE 1 END AS can_modify,
        -- 按主题分组，给状态设置优先级：complete > 其他非空状态 > null
        ROW_NUMBER() OVER (
            PARTITION BY t.id 
            ORDER BY 
                CASE WHEN c.ch_status = 'complete' THEN 1 
                     WHEN c.ch_status IS NOT NULL THEN 2 
                     ELSE 3 END
        ) AS rn
    FROM topics t
    LEFT JOIN used_topics_chapters utc ON t.id = utc.topic_id
    LEFT JOIN completed c ON c.chp_id = utc.id
)
SELECT id, name, ch_status, can_modify
FROM ranked_status
WHERE rn = 1;

逻辑说明

1. 通过CTE（公共表表达式）ranked_status，对每个主题（按t.id分组）的所有关联状态进行排序；
2. 排序规则：complete状态标记为1（优先级最高），其他非空状态标记为2，null标记为3；
3. 最后筛选出每个分组中排序值rn=1的记录，确保每个主题只显示一条，且优先展示complete状态。

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