如何编写SQL查询仅获取指定状态记录并排除特定条目

核心需求 #

• 排除同一设施地址下同时存在LICENSED和CLOSED状态的记录（即示例中的记录2）
• 仅保留状态为CLOSED，且对应地址无LICENSED状态记录的条目（即仅获取示例中的记录3）

原查询的问题 #

原查询仅通过单条记录的状态条件过滤，未考虑同一地址下的其他记录状态，因此会同时返回记录2和3，无法满足需求。

优化后的查询方案 #

方案1：使用NOT EXISTS子查询（逻辑直观，推荐）

SELECT T2.[ID], T2.[Facility Type], T2.[Facility Status], T2.[Facility City], T2.[Facility Address]
FROM T2
WHERE T2.[Facility Status] = 'Closed'
  AND T2.[Facility City] = 'Walnut Creek'
  AND NOT EXISTS (
    -- 检查当前地址是否存在LICENSED状态的记录
    SELECT 1
    FROM T2 AS T1
    WHERE T1.[Facility Address] = T2.[Facility Address]
      AND T1.[Facility Status] = 'LICENSED'
  );

逻辑说明：通过NOT EXISTS确保当前记录的地址下没有任何LICENSED状态的记录，直接排除掉和LICENSED记录同地址的CLOSED记录。

方案2：使用分组过滤+JOIN

WITH ValidAddresses AS (
    -- 筛选出无LICENSED状态记录的地址
    SELECT [Facility Address]
    FROM T2
    GROUP BY [Facility Address]
    HAVING SUM(CASE WHEN [Facility Status] = 'LICENSED' THEN 1 ELSE 0 END) = 0
)
SELECT T2.[ID], T2.[Facility Type], T2.[Facility Status], T2.[Facility City], T2.[Facility Address]
FROM T2
JOIN ValidAddresses VA ON T2.[Facility Address] = VA.[Facility Address]
WHERE T2.[Facility Status] = 'Closed'
  AND T2.[Facility City] = 'Walnut Creek';

逻辑说明：先通过CTE筛选出所有不存在LICENSED状态的地址，再将原表与这些地址关联，最终得到符合条件的CLOSED记录。

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