编写SQL查询语句计算每个Transaction ID的总余额（余额计算公式：Charges-Payments-Adjustments）

没问题，这就帮你写出符合需求的SQL查询语句。核心思路是用条件聚合分别统计每个交易ID对应的各项金额，再按照指定公式计算最终余额。

先确认下你提供的临时表结构和测试数据：

create table #t1( td varchar(100), tid int, tt varchar(100), amount decimal, userr varchar(100))
insert into #t1 values 
('11-05-2020',1, 'charges', 100, 'john smith'), 
('12-06-2020',2, 'charges', 200, 'tayler grand'), 
('13-06-2020',3, 'charges', 500, 'sushan'), 
('14-05-2020',1, 'Payments', 50, 'john smith'), 
('15-05-2020',1, 'adjustments', 250, 'john smith'), 
('16-05-2020',3, 'Payments', 100, 'sushan')

完整查询语句（带明细项） #

这个版本会展示各项金额的明细，方便你核对数据：

SELECT
    tid AS [Transaction ID],
    -- 统计该交易的所有Charges总额
    SUM(CASE WHEN tt = 'charges' THEN amount ELSE 0 END) AS Total_Charges,
    -- 统计该交易的所有Payments总额
    SUM(CASE WHEN tt = 'Payments' THEN amount ELSE 0 END) AS Total_Payments,
    -- 统计该交易的所有Adjustments总额
    SUM(CASE WHEN tt = 'adjustments' THEN amount ELSE 0 END) AS Total_Adjustments,
    -- 按公式计算总余额：Charges - Payments - Adjustments
    SUM(CASE WHEN tt = 'charges' THEN amount ELSE 0 END) 
    - SUM(CASE WHEN tt = 'Payments' THEN amount ELSE 0 END) 
    - SUM(CASE WHEN tt = 'adjustments' THEN amount ELSE 0 END) AS Total_Balance
FROM #t1
GROUP BY tid
ORDER BY tid;

简化版查询（仅返回交易ID和余额） #

如果只需要最终余额结果，可以用更简洁的写法，直接对Payments和Adjustments取负值后求和：

SELECT
    tid AS [Transaction ID],
    SUM(CASE 
        WHEN tt = 'charges' THEN amount 
        WHEN tt IN ('Payments', 'adjustments') THEN -amount 
        ELSE 0 
    END) AS Total_Balance
FROM #t1
GROUP BY tid
ORDER BY tid;

查询结果说明 #

执行后会得到以下结果：

• tid=1：余额为 100 - 50 - 250 = -200
• tid=2：无Payments和Adjustments，余额为 200
• tid=3：余额为 500 - 100 - 0 = 400

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