基于LINK_CUST_TRX_ID和LINE_TYPE汇总金额的SQL查询需求

数据库表结构 #

字段说明：

• TRX_NO：交易编号
• CUST_TRX_ID：客户交易ID
• EXT_AMT：扩展金额
• LINE_TYPE：行类型，值为LINE或TAX
• LINK_CUST_TRX_ID：将TAX行关联到对应LINE行的ID

已尝试的SQL #

WITH combined AS (
  SELECT TRX_NO, EXT_AMT
  FROM your_table
  WHERE LINE_TYPE = 'LINE'
  UNION ALL
  SELECT t1.TRX_NO, t1.EXT_AMT + COALESCE(SUM(t2.EXT_AMT), 0) AS EXT_AMT
  FROM your_table t1
  LEFT JOIN your_table t2 ON t1.LINK_CUST_TRX_ID = t2.CUST_TRX_ID
  WHERE t1.LINE_TYPE = 'TAX'
  GROUP BY t1.TRX_NO, t1.CUST_TRX_ID, t1.EXT_AMT
)
SELECT TRX_NO, SUM(EXT_AMT) AS EXT_AMT
FROM combined
GROUP BY TRX_NO
ORDER BY TRX_NO;

期望输出结果 #

解决方案 #

原查询的关联逻辑错误，且汇总方式不符合需求。正确思路是先按关联关系汇总TAX金额，再分别输出LINE行金额和对应TAX的总金额：

WITH tax_summary AS (
  SELECT 
    TRX_NO,
    LINK_CUST_TRX_ID,
    SUM(EXT_AMT) AS total_tax
  FROM your_table
  WHERE LINE_TYPE = 'TAX'
  GROUP BY TRX_NO, LINK_CUST_TRX_ID
)
-- 输出所有LINE行的金额
SELECT TRX_NO, EXT_AMT FROM your_table WHERE LINE_TYPE = 'LINE'
UNION ALL
-- 输出对应LINE行的TAX总金额
SELECT ts.TRX_NO, ts.total_tax 
FROM tax_summary ts
JOIN your_table t ON ts.LINK_CUST_TRX_ID = t.CUST_TRX_ID AND t.LINE_TYPE = 'LINE'
ORDER BY TRX_NO, EXT_AMT;

逻辑说明 #

• tax_summary 公共表表达式先按交易编号和关联的LINE ID汇总TAX金额，得到每个LINE对应的总税额
• 第一部分SELECT直接取出所有LINE行的金额
• 第二部分SELECT通过JOIN关联LINE行，确保只输出有对应LINE的TAX汇总（若业务允许无关联的TAX行，可去掉JOIN部分）
• 最终按交易编号和金额排序，匹配期望结果

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