SQL查询：筛选仅同时含a1、a2状态附件且无其他状态的项目

现有两张业务表：

• Project（项目表）：包含Id（项目ID）、Name（项目名称）两个字段
• Attachment（附件表）：包含Id（附件ID）、Status（附件状态）、ProjectId（关联项目ID）三个字段

查询目标：筛选出同时满足以下所有条件的项目名称：

1. 项目关联的所有附件状态只能是a1或a2，不存在a3、a4或其他状态
2. 项目必须同时包含a1、a2两种状态的附件，不能仅存在其中一种
   预期正确返回结果为GHI。

原写法存在几个明显错误，无法得到正确结果：

• GROUP BY同时对项目名和附件状态分组，会把同一个项目下不同状态的附件拆成独立分组，无法以项目为维度做全量状态校验
• HAVING子句语法不完整，且仅做了单状态的等值判断，无法实现「仅存在指定状态、必须同时包含两类状态」的聚合校验
• 没有逻辑排除存在a3/a4状态附件的项目

以下聚合判断写法兼容性最好，支持MySQL、PostgreSQL、SQL Server等所有主流关系型数据库：

SELECT 
    p.Name
FROM 
    Project p
INNER JOIN 
    Attachment a ON p.Id = a.ProjectId
GROUP BY 
    p.Id, p.Name
HAVING
    -- 校验不存在a3、a4状态的附件
    SUM(CASE WHEN a.Status IN ('a3','a4') THEN 1 ELSE 0 END) = 0
    -- 校验必须同时存在a1、a2两种状态
    AND COUNT(DISTINCT CASE WHEN a.Status IN ('a1','a2') THEN a.Status END) = 2;

如果使用的数据库支持GROUP_CONCAT/STRING_AGG聚合拼接函数，也可以用更直观的状态集合判断写法，以MySQL为例：

SELECT 
    p.Name
FROM 
    Project p
INNER JOIN 
    Attachment a ON p.Id = a.ProjectId
GROUP BY 
    p.Id, p.Name
HAVING
    -- 所有状态去重排序后拼接结果刚好为'a1,a2'即符合要求
    GROUP_CONCAT(DISTINCT a.Status ORDER BY a.Status SEPARATOR ',') = 'a1,a2';

对照给定测试数据逐项目匹配规则：

• 项目ABC（Id=1）：附件全为a1状态，缺少a2，不符合
• 项目DEF（Id=2）：附件全为a2状态，缺少a1，不符合
• 项目GHI（Id=3）：附件状态为a1、a2，无其他状态，且两种状态同时存在，符合要求
• 项目JKL（Id=4）：存在a3状态附件，不符合
• 项目MNO（Id=5）：存在a4状态附件，不符合
• 项目PQR（Id=6）：无关联附件，内连接查询时不会被返回，不符合
  最终查询结果正确返回GHI，与预期一致。

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