如何编写SQL查询检测同一ID同时存在open和closed状态的重复记录？

核心需求 #

需要检测两类违规情况：

1. 同一customerID（结合customernumber）同时存在open和closed状态的记录
2. 同一customerID（结合customernumber）存在多条open状态的记录（匹配你原始代码的检测逻辑）

方案一：聚合统计一次性覆盖两种情况 #

通过聚合函数统计各状态的记录数，直接筛选出违规的客户数据：

SELECT
    customerID,
    customernumber,
    SUM(CASE WHEN Status = 'open' THEN 1 ELSE 0 END) AS open_record_count,
    SUM(CASE WHEN Status = 'closed' THEN 1 ELSE 0 END) AS closed_record_count,
    COUNT(DISTINCT orderid) AS unique_order_count
FROM 
    test.dbo
GROUP BY 
    customerID, customernumber
HAVING 
    -- 条件1：同时存在open和closed状态
    (open_record_count > 0 AND closed_record_count > 0)
    -- 条件2：存在多条open状态记录
    OR open_record_count > 1

方案二：针对性验证“有open的客户是否存在closed记录” #

如果仅需验证已存在open记录的客户是否同时有closed记录，可以用EXISTS子查询结合原始逻辑：

SELECT
    customerID,
    customernumber,
    COUNT(DISTINCT orderid) AS open_order_count
FROM 
    test.dbo o
WHERE
    Status = 'open'
    AND EXISTS (
        SELECT 1 
        FROM test.dbo c 
        WHERE c.customerID = o.customerID 
          AND c.customernumber = o.customernumber 
          AND c.Status = 'closed'
    )
-- 可选：保留原始条件，筛选同时有多条open和存在closed的客户
-- HAVING COUNT(DISTINCT orderid) > 1
GROUP BY 
    customerID, customernumber

方案三：自关联查询快速定位跨状态重复 #

直接通过自关联匹配同一客户的不同状态记录：

SELECT DISTINCT
    o.customerID,
    o.customernumber
FROM 
    test.dbo o
JOIN 
    test.dbo c ON o.customerID = c.customerID AND o.customernumber = c.customernumber
WHERE 
    o.Status = 'open' 
    AND c.Status = 'closed'

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