多表关联查询问题：获取玩家可选及报名/退课的全部课程

嘿，这个需求我熟！咱们先理清楚逻辑，一步步来解决～

首先得明确两张表的大致结构（我先按常见场景假设字段，你可以根据实际表结构调整）：

• public_courses：存所有公开课程，核心字段比如course_id（课程主键）、course_name（课程名）、start_time（开课时间）
• player_course_enrollments：存玩家报名状态，核心字段比如player_id（玩家ID）、course_id（关联课程）、status（状态：in=在学/已报名，out=已退课）

核心思路：用左连接（LEFT JOIN）兜底所有课程 #

我们要的是所有公开课程，同时展示指定玩家的报名状态——不管他有没有报过，报过的话要显示是in还是out，没报过的话也要明确标记。左连接刚好能满足这个需求：它会保留左表（公开课程表）的所有数据，右表（报名状态表）匹配到就显示数据，没匹配到就显示NULL。

具体SQL实现（以查询玩家ID=123为例） #

SELECT 
    pc.course_id,
    pc.course_name,
    pc.start_time,
    -- 用COALESCE把NULL替换成直观的"从未报名"
    COALESCE(pce.status, 'never_enrolled') AS enrollment_status
FROM 
    public_courses pc
LEFT JOIN 
    player_course_enrollments pce
ON 
    pc.course_id = pce.course_id 
    AND pce.player_id = 123; -- 关键：把玩家ID条件放在ON里，不是WHERE！

为什么这么写？ #

• 把pce.player_id = 123放在ON子句，而不是WHERE，是为了避免过滤掉玩家没报名的课程。如果放WHERE里，左连接就变成内连接了，只会返回玩家有报名记录的课程，不符合需求。
• COALESCE函数用来处理NULL值：如果玩家没报过某门课，pce.status会是NULL，用这个函数可以把它替换成never_enrolled，让结果更易读。

扩展需求：单独筛选玩家曾报名过的课程（含退课） #

如果需要单独列出这个玩家**曾经报名过（不管现在是in还是out）**的课程，只需要加个条件过滤掉NULL状态的记录：

SELECT 
    pc.course_id,
    pc.course_name,
    pc.start_time,
    pce.status
FROM 
    public_courses pc
LEFT JOIN 
    player_course_enrollments pce
ON 
    pc.course_id = pce.course_id 
    AND pce.player_id = 123
WHERE 
    pce.status IS NOT NULL;

特殊情况处理：同一个玩家同一门课有多条记录 #

如果业务上允许玩家重复报名又退课，导致player_course_enrollments里同一玩家同一课程有多条记录，那我们需要取最新的状态。这时候可以用窗口函数来实现：

-- 先获取每个玩家每个课程的最新报名记录
WITH latest_enrollments AS (
    SELECT 
        player_id,
        course_id,
        status,
        -- 按报名时间倒序排序，取第一条（最新）
        ROW_NUMBER() OVER (PARTITION BY player_id, course_id ORDER BY enroll_time DESC) AS rn
    FROM 
        player_course_enrollments
)
SELECT 
    pc.course_id,
    pc.course_name,
    pc.start_time,
    COALESCE(le.status, 'never_enrolled') AS enrollment_status
FROM 
    public_courses pc
LEFT JOIN 
    latest_enrollments le
ON 
    pc.course_id = le.course_id 
    AND le.player_id = 123
    AND le.rn = 1; -- 只保留最新的那条记录

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