Inner Join+Group By实现客户采购查询：含计算列与子查询

现有数据表结构 #

Customer表 #

Id Name
1  Foo
2  Bar

CustomerPurchase表 #

CustomerId, Amount, AmountVAT, Accountable(bit)
1           10      11         1
1           20      22         0
2           5       6          0
2           2       3          0

查询需求 #

通过INNER JOIN关联两张表，按客户分组后每组返回一条记录，需包含：

• Customer表的Name字段
• SUM聚合结果：总不含税金额（Total）、总含税金额（TotalVAT）
• 计算列VAT：总含税金额与总不含税金额的差值
• 筛选聚合结果TotalAccountable：仅统计Accountable=1的记录的Amount总和

期望查询结果：

CustomerPurchases
---
Name Total TotalVAT VAT TotalAccountable
Foo  30    33       3   10
Bar  7     9        2   0

尝试的SQL（无法正常运行） #

select C.Name,
       SUM(CP.Amount) as 'Total',
       SUM(CP.AmountVAT) as 'TotalVAT',
       diff? as 'VAT',
       subquery? as 'TotalAccountable'
from Customer C
inner join CustomerPurchase CR
on C.Id = CR.CustomerId
group by C.Id

正确的SQL实现 #

SELECT 
    C.Name,
    SUM(CP.Amount) AS Total,
    SUM(CP.AmountVAT) AS TotalVAT,
    -- 计算VAT：总含税金额减去总不含税金额
    SUM(CP.AmountVAT) - SUM(CP.Amount) AS VAT,
    -- 仅累加Accountable=1的Amount，无符合记录时返回0
    SUM(CASE WHEN CP.Accountable = 1 THEN CP.Amount ELSE 0 END) AS TotalAccountable
FROM Customer C
INNER JOIN CustomerPurchase CP
    ON C.Id = CP.CustomerId
GROUP BY C.Id, C.Name

关键细节说明 #

1. 分组规则：标准SQL要求GROUP BY必须包含SELECT中所有未使用聚合函数的字段，因此这里需要同时分组C.Id和C.Name（部分数据库如MySQL在开启特定配置时可仅分组主键C.Id，但标准写法需包含所有非聚合字段）。
2. VAT计算：直接利用两个SUM聚合结果做差值，无需额外子查询，简洁高效。
3. 条件聚合：用CASE WHEN实现筛选聚合，替代子查询，性能更优，同时处理无符合条件记录时返回0的场景。

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