SQL子查询中如何按年份GROUP BY？薪资收入按年统计问题求助

表结构 #

AccountTransactionID    int 
TransactionDate         date        
Description             varchar(MAX)    
Expense                 float   
Income                  float   
AccountID               int 
Note                    varchar(MAX)    
Category                varchar(50) 

问题与错误表现 #

需要按TransactionDate的年份分组，生成包含以下字段的数据集：

• Year：交易年份
• TotalIn：仅统计Category='Income - Paycheck'的年度收入总和
• TotalOut：所有支出的年度总和

当前SQL的子查询未与外层查询的年份关联，导致TotalIn返回全表符合条件的总收入，而非分年度统计：

错误SQL语句 #

SELECT 
    DATEPART(YYYY, TransactionDate) AS Year, 
    (SELECT SUM(Income) AS Income
     FROM AccountTransactions 
     WHERE  Category = 'Income - Paycheck') AS TotalIn, 
    SUM(Expense) AS TotalOut
FROM
    AccountTransactions 
WHERE 
    AccountId = 91
GROUP BY 
    DATEPART(YYYY, TransactionDate)
ORDER BY 
    Year Desc

错误执行结果 #

Year    TotalIn      TotalOut
------------------------------------
2023    358457.24     90587.7800000001
2022    358457.24    151930.6
2021    358457.24    197909.46
2020    358457.24     51612.19

解决方案 #

方案1：关联子查询（满足子查询分组需求） #

修改子查询，添加年份关联条件，确保每个年份只统计对应年度的指定类别收入，同时加上账户过滤避免跨账户统计：

SELECT 
    DATEPART(YYYY, t.TransactionDate) AS Year, 
    (SELECT SUM(sub.Income)
     FROM AccountTransactions sub
     WHERE sub.Category = 'Income - Paycheck'
       AND DATEPART(YYYY, sub.TransactionDate) = DATEPART(YYYY, t.TransactionDate)
       AND sub.AccountId = 91) AS TotalIn, 
    SUM(t.Expense) AS TotalOut
FROM
    AccountTransactions t
WHERE 
    t.AccountId = 91
GROUP BY 
    DATEPART(YYYY, t.TransactionDate)
ORDER BY 
    Year Desc

方案2：条件聚合（更简洁高效） #

无需子查询，直接用CASE语句在聚合时过滤指定类别，仅需一次表扫描，性能更优：

SELECT 
    DATEPART(YYYY, TransactionDate) AS Year, 
    SUM(CASE WHEN Category = 'Income - Paycheck' THEN Income ELSE 0 END) AS TotalIn, 
    SUM(Expense) AS TotalOut
FROM
    AccountTransactions
WHERE 
    AccountId = 91
GROUP BY 
    DATEPART(YYYY, TransactionDate)
ORDER BY 
    Year Desc

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