如何根据指定case的审批记录情况筛选mst_table文档数据

表结构 #

mst_table 表 #

document name
1        Request for Approval
2        Purchase order
3        Payment Slip
・　　　　・
・　　　　・　

history_record 表 #

case date        status
A     2023/3/13   rejected
A     2023/3/14   rejected
A     2023/3/15   approved
B     2023/3/13   rejected
B     2023/3/13   rejected

需求说明 #

以history_record中的case为变量，从mst_table中筛选文档：

• 当指定case存在approved状态的记录时，排除文档1（Request for Approval）
• 若不存在approved状态记录，则返回全部文档

示例结果：

• 当变量为case='A'时，返回除文档1外的所有记录
• 当变量为case='B'时，返回所有文档

尝试的错误SQL #

SELECT EXISTS(SELECT * FROM history_record WHERE case = 'A')

正确SQL实现 #

方法1：子查询直接判断状态 #

这是最简洁的写法，通过EXISTS子查询判断指定case是否存在approved记录，再结合文档ID条件过滤：

SELECT m.document, m.name
FROM mst_table m
WHERE 
  NOT (
    EXISTS(SELECT 1 FROM history_record h WHERE h.case = 'A' AND h.status = 'approved')
    AND m.document = 1
  )

逻辑说明：

• 当EXISTS(...)为真（指定case有approved记录），则排除document=1的行
• 当EXISTS(...)为假（无approved记录），NOT(...)整体为真，所有行都保留

方法2：聚合判断+左连接 #

如果需要复用状态判断结果，可以先通过聚合查询得到指定case的状态标识，再关联主表筛选：

SELECT m.document, m.name
FROM mst_table m
LEFT JOIN (
  SELECT 
    case, 
    MAX(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS has_approved
  FROM history_record
  WHERE case = 'A'
  GROUP BY case
) h ON 1=1
WHERE (h.has_approved IS NULL OR h.has_approved = 0) OR m.document != 1

逻辑说明：

• 子查询中has_approved为1表示存在approved记录，0表示不存在
• 左连接后，若has_approved为0或空（无对应case记录），则保留所有行；若为1，则排除document=1的行

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