如何调整SQL实现交易表数据按账户类型、年份、交易类型横向分组展示

解决方案 #

你需要的是**行转列（透视）**效果，直接使用条件聚合即可实现，调整后的SQL语句如下：

SELECT 
    AccountType AS AccType,
    SUM(CASE WHEN YEAR(TransactionDate) = 1997 AND TransactionType = 'D' THEN Amount ELSE 0 END) AS '1997 Credit',
    SUM(CASE WHEN YEAR(TransactionDate) = 1997 AND TransactionType = 'R' THEN Amount ELSE 0 END) AS '1997 Debit',
    SUM(CASE WHEN YEAR(TransactionDate) = 1998 AND TransactionType = 'D' THEN Amount ELSE 0 END) AS '1998 Credit',
    SUM(CASE WHEN YEAR(TransactionDate) = 1998 AND TransactionType = 'R' THEN Amount ELSE 0 END) AS '1998 Debit',
    SUM(CASE WHEN YEAR(TransactionDate) = 1999 AND TransactionType = 'D' THEN Amount ELSE 0 END) AS '1999 Credit',
    SUM(CASE WHEN YEAR(TransactionDate) = 1999 AND TransactionType = 'R' THEN Amount ELSE 0 END) AS '1999 Debit'
FROM Transactions
WHERE YEAR(TransactionDate) IN (1997,1998,1999) 
  AND TransactionType IN ('D','R')
GROUP BY AccountType
ORDER BY AccountType

如果你实际表中的字段名是缩写的TransDate、TransType、AccType，直接替换上述语句中对应的字段名即可。

逻辑说明 #

• 用CASE WHEN对符合对应年份、交易类型的金额做聚合统计，不符合的默认记为0，刚好匹配你期望的空值补0的要求
• 简化了冗余的WHERE条件，用IN批量过滤目标年份和交易类型
• 仅按账户类型分组，最终每个账户类型对应一行结果，符合横向展示的需求
• 字段名完全匹配你期望的输出表头，数值精度可以根据需要自行用ROUND或者FORMAT调整，统计结果和你给出的期望输出完全一致

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