PostgreSQL中基于关联表行存在性筛选课程相关数据的方法

嘿，根据你给出的四张表结构，我整理了几个常见的、基于“另一表是否存在行”的查询场景，你可以根据实际需求调整：

场景1：查询所有课程，同时标记是否存在对应版本 #

比如你想知道哪些课程已经创建了至少一个版本，用EXISTS子查询就能轻松实现：

SELECT 
  c.id,
  c.data,
  c.status,
  -- 标记是否存在对应的course_version
  EXISTS (
    SELECT 1 FROM course_versions cv 
    WHERE cv.course_id = c.id AND cv.status = 'active' -- 可根据需要加状态过滤
  ) AS has_active_version
FROM courses c;

这里EXISTS会快速检查当前课程是否有匹配的版本记录，返回true或false，比LEFT JOIN后判断cv.id IS NOT NULL的效率更高，尤其是数据量较大时。

场景2：查询某个用户的所有课程版本，同时标记是否有学习进度 #

假设你要查用户ID为123的用户，对所有课程版本的学习进度情况：

SELECT 
  cv.id AS version_id,
  cv.course_id,
  cv.data AS version_data,
  -- 标记该用户是否有对应进度
  EXISTS (
    SELECT 1 FROM course_progresses cp 
    WHERE cp.course_version_id = cv.id AND cp.user_id = 123
  ) AS has_progress
FROM course_versions cv
ORDER BY cv.course_id, cv.created_at DESC;

如果需要只显示用户有进度的版本，直接把EXISTS放到WHERE子句里就行：

SELECT cv.id, cv.course_id, cv.data
FROM course_versions cv
WHERE EXISTS (
  SELECT 1 FROM course_progresses cp 
  WHERE cp.course_version_id = cv.id AND cp.user_id = 123
);

场景3：查询所有没有任何用户进度的课程版本 #

反过来，如果你想找哪些课程版本还没有任何用户学习过：

SELECT cv.id, cv.course_id, cv.data
FROM course_versions cv
WHERE NOT EXISTS (
  SELECT 1 FROM course_progresses cp 
  WHERE cp.course_version_id = cv.id
);

NOT EXISTS会过滤掉所有存在进度记录的版本，只保留完全没有进度的。

场景4：查询用户及其对应课程的最新版本进度 #

如果需要关联用户、课程、最新版本和进度，可以结合ROW_NUMBER()和EXISTS：

WITH latest_course_versions AS (
  SELECT 
    cv.*,
    ROW_NUMBER() OVER (PARTITION BY cv.course_id ORDER BY cv.created_at DESC) AS rn
  FROM course_versions cv
  WHERE cv.status = 'active'
)
SELECT 
  u.id AS user_id,
  u.email,
  lcv.course_id,
  lcv.data AS latest_version_data,
  EXISTS (
    SELECT 1 FROM course_progresses cp 
    WHERE cp.course_version_id = lcv.id AND cp.user_id = u.id
  ) AS has_latest_version_progress
FROM users u
CROSS JOIN latest_course_versions lcv
WHERE lcv.rn = 1 -- 只取每个课程的最新版本
ORDER BY u.id, lcv.course_id;

这里先用CTE获取每个课程的最新激活版本，再关联用户，标记是否有该版本的进度。

如果你的实际需求和上面的场景不同，可以补充具体的查询目标（比如要查哪些字段、过滤条件），我再帮你调整SQL～

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