如何查询ID_CARD表中仅存在初始DRAFT版本、无更高版本的记录

涉及表结构及数据 #

PROJECT表 #

ID   PROJECT_ID
123  93773
245  98287
675  93889

ID_CARD表 #

PROJECT_ID    CODE         Version    STATUS
245          98287001      1          DRAFT
245          98287002      2          ARCHIVED
245          98287003      3          ACTIVE
245          98287004      4          DRAFT
675          93889001      1          DRAFT
123          93773001      1          DRAFT

需求说明 #

查询ID_CARD表中仅存在Version=1的初始DRAFT版本、不存在Version≥2更高版本的记录，预期返回结果：

PROJECT_ID    CODE         Version    STATUS
675          93889001      1          DRAFT

原SQL问题 #

原有SQL仅筛选了状态为DRAFT的记录，没有排除存在更高版本的项目对应记录，且关联PROJECT表的条件错误，因此返回结果不符合要求。

修正后SQL #

方案1：使用NOT EXISTS逻辑（性能更优，推荐） #

SELECT ic.*
FROM id_card ic
-- 若不需要PROJECT表的字段可删除下面这行JOIN语句
LEFT JOIN project p ON p.id = ic.project_id
WHERE ic.status = 'DRAFT' 
  AND ic.version = 1
  -- 排除同项目下存在更高版本的记录
  AND NOT EXISTS (
    SELECT 1 
    FROM id_card ic2 
    WHERE ic2.project_id = ic.project_id 
      AND ic2.version >= 2
  )

方案2：使用分组聚合筛选 #

SELECT ic.*
FROM id_card ic
-- 若不需要PROJECT表的字段可删除下面这行JOIN语句
LEFT JOIN project p ON p.id = ic.project_id
INNER JOIN (
  -- 先筛选出最大版本号为1的项目ID
  SELECT project_id
  FROM id_card
  GROUP BY project_id
  HAVING MAX(version) = 1
) t ON ic.project_id = t.project_id
WHERE ic.status = 'DRAFT' AND ic.version = 1

两种方案均可得到符合要求的返回结果。

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