实现按IDNUMBER条件求和并生成标识的SQL查询求助

示例表结构及数据 #

需求说明 #

对每个IDNUMBER的Amount值做如下处理：

• 若该ID下存在负数Amount，保留该ID的所有原Amount值
• 若该ID下无负数Amount，对Amount求和
  处理后的结果值>=0时，Flag_in设为2；否则设为1。

用户尝试的错误SQL #

SELECT IDNUMBER,
CASE
    WHEN MIN(CAST(Amount AS DECIMAL(9,2))) < 0 THEN MIN(CAST(Amount AS DECIMAL(9,2)))
     ELSE CAST(SUM(CAST(Amount AS DECIMAL(9,2))) AS DECIMAL(9,2))
    END AS Sum_Result,
    CASE
      WHEN MIN(CAST(Amount AS DECIMAL(9,2))) < 0 THEN 1
      ELSE 2
    END AS "FLAG_In"
FROM Table1
GROUP BY IDNUMBER;

正确SQL语句 #

WITH id_metadata AS (
    SELECT 
        IDNUMBER,
        SUM(CAST(Amount AS DECIMAL(9,2))) AS total_sum,
        MAX(CASE WHEN CAST(Amount AS DECIMAL(9,2)) < 0 THEN 1 ELSE 0 END) AS has_negative
    FROM Table1
    GROUP BY IDNUMBER
)
-- 处理存在负数的ID，保留所有原金额
SELECT
    t.IDNUMBER,
    CAST(t.Amount AS DECIMAL(9,2)) AS Sum_result,
    CASE WHEN CAST(t.Amount AS DECIMAL(9,2)) < 0 THEN 1 ELSE 2 END AS Flag_in
FROM Table1 t
JOIN id_metadata im ON t.IDNUMBER = im.IDNUMBER
WHERE im.has_negative = 1

UNION ALL

-- 处理无负数的ID，输出求和结果
SELECT
    IDNUMBER,
    total_sum AS Sum_result,
    2 AS Flag_in
FROM id_metadata
WHERE has_negative = 0
ORDER BY IDNUMBER, Sum_result DESC;

思路说明 #

1. 先用CTEid_metadata统计每个ID的总金额，以及该ID是否存在负数金额
2. 分两个分支处理：
   • 存在负数的ID：直接关联原表取出每条金额，根据金额正负设置Flag_in
   • 无负数的ID：直接输出统计好的总金额，Flag_in固定为2
3. 通过UNION ALL合并两个分支的结果，最终排序输出

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