如何在SQL中按条件计算含返回标识的结算金额合计

原需求与SQL #

原需求为统计满足以下条件的交易结算金额计数与合计，按结算日期分组：

• MCC = '6011'
• MSG_TYPE = 'MSGTPRES'
• HOST_DATE ≥ 当日零点（TRUNC(SYSDATE)）

原SQL语句：

select COUNT(STTL_AMOUNT) as "COUNT",
       SUM(STTL_AMOUNT) as "Settlement Amount",
       HOST_DATE as "Settlement Date"
FROM OPR_OPERATION
WHERE MCC = '6011'
  AND MSG_TYPE = 'MSGTPRES'
  AND HOST_DATE >= TRUNC(SYSDATE)
GROUP BY HOST_DATE;

扩展需求 #

需新增逻辑：当returned字段值为'-1'时，从结算金额合计中扣除对应行的STTL_AMOUNT值。

解决方案 #

你尝试的SUM(STTL_AMOUNT where returned='0')写法不符合标准SQL语法，正确做法是在SUM函数内嵌套CASE表达式，根据returned的值对STTL_AMOUNT做不同处理：

• 当returned='0'时，正常计入金额
• 当returned='-1'时，以负数形式计入金额（等效于扣除）
• 若存在其他returned值，可根据需求设为0（忽略）或按实际规则处理

修改后的SQL语句：

select COUNT(STTL_AMOUNT) as "COUNT",
       SUM(CASE 
             WHEN returned = '0' THEN STTL_AMOUNT
             WHEN returned = '-1' THEN -STTL_AMOUNT
             ELSE 0 -- 其他情况忽略，可根据需求调整
           END) as "Settlement Amount",
       HOST_DATE as "Settlement Date"
FROM OPR_OPERATION
WHERE MCC = '6011'
  AND MSG_TYPE = 'MSGTPRES'
  AND HOST_DATE >= TRUNC(SYSDATE)
GROUP BY HOST_DATE;

补充说明 #

如果你的数据库支持FILTER子句（如PostgreSQL），也可以用以下写法，但兼容性不如CASE表达式：

SUM(STTL_AMOUNT) FILTER (WHERE returned = '0') - 
SUM(STTL_AMOUNT) FILTER (WHERE returned = '-1') as "Settlement Amount"

但考虑到你原尝试的写法无效，大概率是使用不支持FILTER的数据库（如Oracle），因此优先推荐CASE表达式的写法。

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