sql - 每个代理商的销售额,年和月

我有一个带有agentID、transDate 和transValue 的table。我如何报告每个代理、年份和月份的所有 transValue?这些列应该如下所示: agentID|year|January|February... 在每个月下必须对 transValue 进行汇总。现在我有一部分代码:

select agentID, year(transDate), sum(transValue) as January
from operations
where month(transDate) = 01
group by agentID, year(transDate)

我不知道如何添加接下来的几个月。有什么建议么?

回答1

您可以使用条件聚合来生成该报告 -

select agentID, year(transDate),
       sum(CASE WHEN month(transDate) = 1 THEN transValue END) as January,
       sum(CASE WHEN month(transDate) = 2 THEN transValue END) as february,
       sum(CASE WHEN month(transDate) = 3 THEN transValue END) as March,
       sum(CASE WHEN month(transDate) = 4 THEN transValue END) as April,
       sum(CASE WHEN month(transDate) = 5 THEN transValue END) as May,
       sum(CASE WHEN month(transDate) = 6 THEN transValue END) as June,
       sum(CASE WHEN month(transDate) = 7 THEN transValue END) as July,
       sum(CASE WHEN month(transDate) = 8 THEN transValue END) as August,
       sum(CASE WHEN month(transDate) = 9 THEN transValue END) as Spetember,
       sum(CASE WHEN month(transDate) = 10 THEN transValue END) as October,
       sum(CASE WHEN month(transDate) = 11 THEN transValue END) as November,
       sum(CASE WHEN month(transDate) = 12 THEN transValue END) as December
  from operations
 group by agentID, year(transDate)