SQL SERVER 算出一年中每月的总数量

最新推荐文章于 2023-10-10 15:59:24 发布

ChineseHW

最新推荐文章于 2023-10-10 15:59:24 发布

阅读量891

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/ChineseHW/article/details/120303658

版权

select y.year,

convert(varchar(50), sum(一月))+','+convert(varchar(50), sum(一月1))  as '一月',
convert(varchar(50), sum(二月))+','+convert(varchar(50), sum(二月1))  as '二月',
convert(varchar(50), sum(三月))+','+convert(varchar(50), sum(三月1))  as '三月',
convert(varchar(50), sum(四月))+','+convert(varchar(50), sum(四月1))  as '四月',
convert(varchar(50), sum(五月))+','+convert(varchar(50), sum(五月1))  as '五月',
convert(varchar(50), sum(六月))+','+convert(varchar(50), sum(六月1))  as '六月',
convert(varchar(50), sum(七月))+','+convert(varchar(50), sum(七月1))  as '七月',
convert(varchar(50), sum(八月))+','+convert(varchar(50), sum(八月1))  as '八月',
convert(varchar(50), sum(九月))+','+convert(varchar(50), sum(九月1))  as '九月',
convert(varchar(50), sum(十月))+','+convert(varchar(50), sum(十月1))  as '十月',
convert(varchar(50), sum(十一月))+','+convert(varchar(50), sum(十一月1))  as '十一月',
convert(varchar(50), sum(十二月))+','+convert(varchar(50), sum(十二月1))  as '十二月'
from(
select m.year,
(case when (m.month = 1) then m.SumArea else 0 end) as '一月',
(case when (m.month = 2) then m.SumArea else 0 end) as '二月',
(case when (m.month = 3) then m.SumArea else 0 end) as '三月',
(case when (m.month = 4) then m.SumArea else 0 end) as '四月',
(case when (m.month = 5) then m.SumArea else 0 end) as '五月',
(case when (m.month = 6) then m.SumArea else 0 end) as '六月',
(case when (m.month = 7) then m.SumArea else 0 end) as '七月',
(case when (m.month = 8) then m.SumArea else 0 end) as '八月',
(case when (m.month = 9) then m.SumArea else 0 end) as '九月',
(case when (m.month = 10) then m.SumArea else 0 end) as '十月',
(case when (m.month = 11) then m.SumArea else 0 end) as '十一月',
(case when (m.month = 12) then m.SumArea else 0 end) as '十二月',

(case when (m.month = 1) then m.SumAmount else 0 end) as '一月1',
(case when (m.month = 2) then m.SumAmount else 0 end) as '二月1',
(case when (m.month = 3) then m.SumAmount else 0 end) as '三月1',
(case when (m.month = 4) then m.SumAmount else 0 end) as '四月1',
(case when (m.month = 5) then m.SumAmount else 0 end) as '五月1',
(case when (m.month = 6) then m.SumAmount else 0 end) as '六月1',
(case when (m.month = 7) then m.SumAmount else 0 end) as '七月1',
(case when (m.month = 8) then m.SumAmount else 0 end) as '八月1',
(case when (m.month = 9) then m.SumAmount else 0 end) as '九月1',
(case when (m.month = 10) then m.SumAmount else 0 end) as '十月1',
(case when (m.month = 11) then m.SumAmount else 0 end) as '十一月1',
(case when (m.month = 12) then m.SumAmount else 0 end) as '十二月1'from
(select sum(SumArea) as SumArea, sum(SumAmount) as SumAmount,datepart(year,OrderDate) as year ,datepart(month,OrderDate) as month from AA_SaleOrder group by datepart(year,OrderDate),datepart(month,OrderDate))m) y
group by y.year