如何编写SQL CASE语句校验合同列值配置正确性？

你的核心需求可拆解为：同一合同需同时满足「至少存在一行Column1为'x'」和「至少存在一行Column2为'x'」，同时排除单行中Column1与Column2均为'x'、或单行无任何'x'的错误场景。以下是两种完善后的实现方案：

方法一：子查询聚合合同状态 #

先通过子查询统计每个合同的整体配置状态，再关联回原表标记每行结果：

SELECT
    t1.Contract,
    t1.ContractType,
    t1.Column1,
    t1.Column2,
    t1.Site,
    t1.Date,
    CASE
        -- 单行同时包含两个x，标记错误
        WHEN t1.Column1 = 'x' AND t1.Column2 = 'x' THEN 'Error: 单行同时存在Column1和Column2的x'
        -- 单行无有效x，标记错误
        WHEN t1.Column1 <> 'x' AND t1.Column2 <> 'x' THEN 'Error: 单行无有效x配置'
        -- 合同整体缺少Column1的x
        WHEN contract_stats.has_col1_x = 0 THEN 'Error: 合同未配置Column1的x'
        -- 合同整体缺少Column2的x
        WHEN contract_stats.has_col2_x = 0 THEN 'Error: 合同未配置Column2的x'
        -- 所有条件符合要求
        ELSE 'Valid'
    END AS Results
FROM Table1 t1
JOIN (
    SELECT
        Contract,
        -- 统计合同是否存在Column1的x
        MAX(CASE WHEN Column1 = 'x' THEN 1 ELSE 0 END) AS has_col1_x,
        -- 统计合同是否存在Column2的x
        MAX(CASE WHEN Column2 = 'x' THEN 1 ELSE 0 END) AS has_col2_x
    FROM Table1
    WHERE ContractType IN ('Type1','Type2','Type3') 
      AND Site = 'Site 1'
      AND Date >= CURRENT_TIMESTAMP
    GROUP BY Contract
    HAVING COUNT(DISTINCT ContractType) >= 2
) contract_stats ON t1.Contract = contract_stats.Contract
WHERE t1.Site = 'Site 1' 
  AND t1.Date >= CURRENT_TIMESTAMP
ORDER BY t1.Contract;

方法二：窗口函数实现（更简洁） #

利用窗口函数直接在原查询中计算合同的聚合状态，无需额外关联：

SELECT DISTINCT
    t1.Contract,
    t1.ContractType,
    t1.Column1,
    t1.Column2,
    t1.Site,
    t1.Date,
    CASE
        WHEN t1.Column1 = 'x' AND t1.Column2 = 'x' THEN 'Error: 单行同时存在Column1和Column2的x'
        WHEN t1.Column1 <> 'x' AND t1.Column2 <> 'x' THEN 'Error: 单行无有效x配置'
        WHEN MAX(CASE WHEN Column1 = 'x' THEN 1 ELSE 0 END) OVER (PARTITION BY t1.Contract) = 0 THEN 'Error: 合同未配置Column1的x'
        WHEN MAX(CASE WHEN Column2 = 'x' THEN 1 ELSE 0 END) OVER (PARTITION BY t1.Contract) = 0 THEN 'Error: 合同未配置Column2的x'
        ELSE 'Valid'
    END AS Results
FROM Table1 t1
WHERE t1.ContractType IN ('Type1','Type2','Type3') 
  AND t1.Site = 'Site 1'
  AND t1.Date >= CURRENT_TIMESTAMP
  AND EXISTS (
      SELECT 1
      FROM Table1 t2
      WHERE t2.Contract = t1.Contract
        AND t2.ContractType IN ('Type1','Type2','Type3')
        AND t2.Site = 'Site 1'
        AND t2.Date >= CURRENT_TIMESTAMP
      GROUP BY t2.Contract
      HAVING COUNT(DISTINCT t2.ContractType) >= 2
  )
ORDER BY t1.Contract;

说明 #

• 两种方案都会优先校验单行错误，再判断合同整体是否满足双列x的配置要求
• 窗口函数方案代码更紧凑，但需确保你的数据库支持窗口函数语法（如MySQL 8+、PostgreSQL、SQL Server等均支持）
• 错误提示可根据实际需求调整，便于快速定位问题

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