查询满足特定Prereq状态要求的Workorder表wonum

需求回顾 #

先明确需求：从workorder表中筛选wonum，满足：

• 如果该wonum在prereq表中有type为'ABC'或'DEF'的前置记录，那么这些记录的status必须全部为'COMP'
• 其他类型（比如示例中的'TEST'）的前置记录状态不影响筛选结果

方案一：使用分组+HAVING子句 #

这是一种基于聚合的方法，适合需要同时统计分组内情况的场景：

SELECT w.wonum
FROM workorder w
LEFT JOIN prereq p 
  ON w.wonum = p.wonum 
  AND p.type IN ('ABC', 'DEF') -- 只关联我们关心的类型
GROUP BY w.wonum
HAVING 
    -- 情况1：没有任何ABC/DEF类型的前置记录
    COUNT(p.wonum) = 0 
    OR 
    -- 情况2：所有ABC/DEF记录的status都是COMP，没有非COMP的情况
    MAX(CASE WHEN p.status != 'COMP' THEN 1 ELSE 0 END) = 0;

逻辑解释 #

1. LEFT JOIN确保我们只拉取prereq中type为'ABC'或'DEF'的记录，其他类型的记录不会被关联进来
2. 按wonum分组后，HAVING子句判断两种合法情况：
   • 如果分组后COUNT(p.wonum)=0，说明这个wonum没有我们关心的前置记录，直接符合要求
   • 用CASE语句把非COMP的记录标记为1，COMP标记为0，MAX值为0就意味着所有记录都是COMP，符合条件

方案二：使用NOT EXISTS子查询 #

这个方案逻辑更直观，直接排除不符合条件的wonum：

SELECT w.wonum
FROM workorder w
WHERE NOT EXISTS (
    SELECT 1
    FROM prereq p
    WHERE p.wonum = w.wonum
      AND p.type IN ('ABC', 'DEF')
      AND p.status != 'COMP'
);

逻辑解释 #

这个SQL的核心是：筛选那些不存在"ABC/DEF类型且状态不是COMP"的前置记录的wonum。

• 如果某个wonum有任何一条ABC/DEF类型且状态不是COMP的记录，就会被排除
• 没有ABC/DEF记录的wonum，因为子查询找不到匹配项，所以NOT EXISTS为真，会被保留

示例验证 #

对应你给出的示例数据：

Workorder表wonum：123、456、245
Prereq表记录：

type	status	wonum
ABC	COMP	123
DEF	PENDING	123
TEST	WORKING	123
ABC	COMP	456
TEST	WORKING	456
ABC	COMP	245
DEF	COMP	245
TEST	WORKING	245

• wonum=123：存在DEF类型且status为PENDING的记录，两个方案都会排除它
• wonum=456：只有ABC类型且status为COMP，没有不符合条件的记录，被保留
• wonum=245：ABC和DEF类型的记录status都是COMP，被保留

最终输出结果为456、245，和预期完全一致。

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