使用SQL实现条件求和：校验列值且同I1/I2时仅计一次

首先明确需求对应的数据集：

字段说明：

• V = Value（数值）
• I = Indicator（标识）
• T = Type（类型）

需求核心：每行仅计算**满足T=1且I∈(1,2)**的数值；若同一行同时存在I=1和I=2的符合条件记录，仅计入一个数值；若为同一I的多个符合记录，则求和。

原代码问题 #

原CASE语句存在语法错误：I1 = (1 OR 2) 应改为 I1 IN (1,2)，但即使修正语法，也无法处理“同一行同时存在I=1和I=2时仅取一个值”的逻辑，会导致第3行计算结果为30（15+15），不符合预期。

SQL实现方案（CTE+行转列） #

通过CTE将每行的多列数据转为行结构，再分组处理逻辑：

方法1：使用UNPIVOT（适用于SQL Server等支持该语法的数据库） #

WITH unpivoted_data AS (
    -- 生成每行唯一标识，若原表有主键可替换ROW_NUMBER
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
        val AS value,
        ind AS indicator,
        tp AS type
    FROM your_table
    UNPIVOT (
        (val, ind, tp) FOR cols IN (
            (V1, I1, T1),
            (V2, I2, T2),
            (V3, I3, T3),
            (V4, I4, T4)
        )
    ) AS up
    WHERE val IS NOT NULL
),
filtered_records AS (
    SELECT
        row_id,
        value,
        indicator
    FROM unpivoted_data
    WHERE type = 1
      AND indicator IN (1, 2)
),
calculated_results AS (
    SELECT
        row_id,
        CASE
            -- 若同时存在I=1和I=2，取任意一个数值（这里用MAX，可按需替换为MIN/FIRST_VALUE）
            WHEN COUNT(DISTINCT indicator) = 2 THEN MAX(value)
            -- 同一I的多个符合记录，求和
            ELSE SUM(value)
        END AS row_result
    FROM filtered_records
    GROUP BY row_id
)
SELECT row_result
FROM calculated_results
ORDER BY row_id;

方法2：使用UNION ALL模拟行转列（兼容多数数据库） #

WITH unpivoted_data AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
        V1 AS value,
        I1 AS indicator,
        T1 AS type
    FROM your_table
    WHERE V1 IS NOT NULL
    UNION ALL
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
        V2 AS value,
        I2 AS indicator,
        T2 AS type
    FROM your_table
    WHERE V2 IS NOT NULL
    UNION ALL
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
        V3 AS value,
        I3 AS indicator,
        T3 AS type
    FROM your_table
    WHERE V3 IS NOT NULL
    UNION ALL
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
        V4 AS value,
        I4 AS indicator,
        T4 AS type
    FROM your_table
    WHERE V4 IS NOT NULL
),
filtered_records AS (
    SELECT
        row_id,
        value,
        indicator
    FROM unpivoted_data
    WHERE type = 1
      AND indicator IN (1, 2)
),
calculated_results AS (
    SELECT
        row_id,
        CASE
            WHEN COUNT(DISTINCT indicator) = 2 THEN MAX(value)
            ELSE SUM(value)
        END AS row_result
    FROM filtered_records
    GROUP BY row_id
)
SELECT row_result
FROM calculated_results
ORDER BY row_id;

执行以上代码后，将得到预期结果：

• 第1行：27
• 第2行：31
• 第3行：15
• 第4行：18

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