时间戳

首页 > 解决方案 > 根据是否存在另一个表行来选择行

sql - 根据是否存在另一个表行来选择行

问题描述

很难为这个写一个好标题...

我的 postgresql 数据库中有 4 个表：

courses:
| id | data | created_at | updated_at | status |

course_versions:
| id | course_id | data | created_at | updated_at | status |

course_progresses:
| id | course_version_id | user_id | data | created_at | updated_at |

users:
| id | email |

一个课程可以有多个 course_version，一个用户可以有 course_progresses，每个 course_progress 都连接到一个 course_version。现在，我想获取特定用户的所有 course_versions，也就是说，如果用户没有 course_version 的 course_progress，course id: 1它应该返回最新的 course_version。如果用户有 course_progress 的course id: 2course_version 则应该返回该 course_version，无论是否有较新的 course_versions... 明白吗？:)

这是一个有效的查询：

WITH
course_versions_with_progress AS (
  SELECT cv.*
  FROM course_versions AS cv
  INNER JOIN courses AS c ON c.id = cv.course_id
  INNER JOIN course_progresses AS cp ON cp.course_version_id = cv.id
  WHERE c.status = 1 AND cv.status = 1 AND cp.user_id = 123
),
latest_course_versions AS (
  SELECT DISTINCT ON(cv.course_id) cv.*
  FROM course_versions AS cv
  INNER JOIN courses AS c ON c.id = cv.course_id
  WHERE c.status = 1 AND cv.status = 1
  ORDER BY cv.course_id, cv.created_at DESC
)
SELECT * FROM course_versions_with_progress
UNION
  SELECT * FROM latest_course_versions
  WHERE NOT EXISTS (
    SELECT 1
    FROM course_versions_with_progress
    WHERE course_id = latest_course_versions.course_id
  )

不过，这对我来说看起来很可怕……有没有更好的方法来编写这个查询？

标签： sqlpostgresql

解决方案

您可以使用 across join创建用户和课程的二维矩阵。对于每个条目，查找具有进度的版本或最新版本：

select  u.email
,       c.course_id
,       coalesce(progress_version.created_at, latest_version.created_at)
from    users u
cross join
        courses c
left join
        (
        select  cp.user_id
        ,       cv.course_id
        ,       cv.created_at
        from    course_progresses cp
        join    course_versions cv
        on      cv.id = cp.course_version_id
        ) progress_version
on      progress_version.user_id = u.id
        and progress_version.course_id = c.id
left join
        (
        select  row_number() over (partition by course_id 
                                   order by created_at desc) rn
        ,       *
        from    course_versions
        ) latest_version
on      latest_version.course_id = c.id
        and latest_version.rn = 1

推荐阅读