关于sql 分组统计查询,我们在做报表的时候经常需要用到;今天就在这里整理下;
先附上一段sql代码:
if object_id(N'#mytb',N'U') is not null drop table #mytb
godeclare @Year intset @Year=2014create table #mytb ([Date] int,[Count] int,[Price] decimal(18, 0),[spbm] varchar(50),[sppp] varchar(100),[spmc] varchar(500))insert #mytbselect *from(select m as [Date], sum( case when datepart(month,c.addtime)=m then ordernum else 0 end ) as [Count] , sum( case when datepart(month,c.addtime)=m then ordernum*orderPrice else 0 end ) as Price, c.spbm, s.sppp, s.spmcfrom TB_CusorderDetail c left join TB_SPXX s on c.spbm=s.spbm, ( select 1 m union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 ) aawhere @Year=year(c.addtime) and c.delstatus=0 and c.spbm in (SELECT spbm FROM TB_SPXX WHERE delstatus=0 AND isupinfo=1 AND spbm LIKE '1%' AND LEN(SPBM)>7)group by m,c.spbm,s.sppp,s.spmc )zselect sppp,spmc,spbm,
max(case [Date] when 1 then [Count] else 0 end) '一月份总量',max(case [Date] when 1 then [Price] else 0 end) '一月份总额',max(case [Date] when 2 then [Count] else 0 end) '二月份总量',max(case [Date] when 2 then [Price] else 0 end) '二月份总额',max(case [Date] when 3 then [Count] else 0 end) '三月份总量',max(case [Date] when 3 then [Price] else 0 end) '三月份总额',max(case [Date] when 4 then [Count] else 0 end) '四月份总量',max(case [Date] when 4 then [Price] else 0 end) '四月份总额',max(case [Date] when 5 then [Count] else 0 end) '五月份总量',max(case [Date] when 5 then [Price] else 0 end) '五月份总额',max(case [Date] when 6 then [Count] else 0 end) '六月份总量',max(case [Date] when 6 then [Price] else 0 end) '六月份总额',max(case [Date] when 7 then [Count] else 0 end) '七月份总量',max(case [Date] when 7 then [Price] else 0 end) '七月份总额',max(case [Date] when 8 then [Count] else 0 end) '八月份总量',max(case [Date] when 8 then [Price] else 0 end) '八月份总额'from #mytbgroup by sppp,spmc,spbm
关于这段sql代码,剖析如下:
1:新建一个临时表#mytb
create table #mytb ([Date] int,[Count] int,[Price] decimal(18, 0),[spbm] varchar(50),[sppp] varchar(100),[spmc] varchar(500))
2:分组查询12个月份数据
select *from
(select m as [Date],sum(case when datepart(month,c.addtime)=m then ordernum else 0 end) as [Count] ,sum(case when datepart(month,c.addtime)=m then ordernum*orderPrice else 0 end) as Price,c.spbm,s.sppp,s.spmcfrom TB_CusorderDetail c left join TB_SPXX s on c.spbm=s.spbm,(select 1 munion all select 2union all select 3union all select 4union all select 5union all select 6union all select 7union all select 8union all select 9union all select 10union all select 11union all select 12) aawhere @Year=year(c.addtime) and c.delstatus=0 and c.spbm in (SELECT spbm FROM TB_SPXX WHERE delstatus=0 AND isupinfo=1 AND spbm LIKE '1%' AND LEN(SPBM)>7)group by m,c.spbm,s.sppp,s.spmc)z这段sql主要根据年份查询当年12个月份售出的产品统计;
3:横纵坐标转换
把分组查询出来的数据插入到临时表中后
select sppp,spmc,spbm,
max(case [Date] when 1 then [Count] else 0 end) '一月份总量',max(case [Date] when 1 then [Price] else 0 end) '一月份总额',max(case [Date] when 2 then [Count] else 0 end) '二月份总量',max(case [Date] when 2 then [Price] else 0 end) '二月份总额',max(case [Date] when 3 then [Count] else 0 end) '三月份总量',max(case [Date] when 3 then [Price] else 0 end) '三月份总额',max(case [Date] when 4 then [Count] else 0 end) '四月份总量',max(case [Date] when 4 then [Price] else 0 end) '四月份总额',max(case [Date] when 5 then [Count] else 0 end) '五月份总量',max(case [Date] when 5 then [Price] else 0 end) '五月份总额',max(case [Date] when 6 then [Count] else 0 end) '六月份总量',max(case [Date] when 6 then [Price] else 0 end) '六月份总额',max(case [Date] when 7 then [Count] else 0 end) '七月份总量',max(case [Date] when 7 then [Price] else 0 end) '七月份总额',max(case [Date] when 8 then [Count] else 0 end) '八月份总量',max(case [Date] when 8 then [Price] else 0 end) '八月份总额'from #mytbgroup by sppp,spmc,spbm;利用日期昨晚扭转横纵坐标;
最后附上一图,体现最终结果
