11,849
社区成员
发帖
与我相关
我的任务
分享
乌龟大哥是帮别人问的吧?
create table dd(时间 datetime,单位 varchar(10),重量 int)
insert into dd select '2007-10-11','Au1',100
insert into dd select '2007-10-11', 'Au2', 200
insert into dd select '2007-10-11', 'Au3', 300
insert into dd select '2007-10-11', 'Au4', 400
insert into dd select '2007-11-11', 'Au1', 200
insert into dd select '2007-11-11', 'Au2', 200
insert into dd select '2007-11-11', 'Au3', 200
insert into dd select '2007-11-11', 'Au4', 300
declare @sql varchar(8000)
set @sql='select convert(varchar(7),时间,120) [时间]'
select @sql=@sql+',['+单位+']=max(case 单位 when '''+单位+''' then 重量 else 0 end)' from (select distinct 单位 from dd)a
set @sql=@sql+',sum(重量)[合计] from dd group by convert(varchar(7),时间,120) '
set @sql=@sql+' union select distinct convert(varchar(4),时间,120)+''(总计)'' [时间]'
select @sql=@sql+',['+单位+']=(select sum(重量) from dd where 单位='''+ 单位+''')' from (select distinct 单位 from dd)a
set @sql=@sql+',sum(重量)[合计] from dd group by convert(varchar(4),时间,120)+''(总计)'''
exec(@sql)
create table tb(时间 datetime,类别 varchar(10),数量 int)
insert into tb values('2007-10-11','Au1',100)
insert into tb values('2007-10-11','Au2',200)
insert into tb values('2007-10-11','Au3',300)
insert into tb values('2007-10-11','Au4',400)
insert into tb values('2007-11-11','Au1',200)
insert into tb values('2007-11-11','Au2',200)
insert into tb values('2007-11-11','Au3',200)
insert into tb values('2007-11-11','Au4',300)
go
--静态SQL,指类型只有Au1,Au2,Au3,Au4四种.
select convert(varchar(7),时间,120) 时间,
sum(case 类别 when 'Au1' then 数量 else 0 end) Au1,
sum(case 类别 when 'Au2' then 数量 else 0 end) Au2,
sum(case 类别 when 'Au3' then 数量 else 0 end) Au3,
sum(case 类别 when 'Au4' then 数量 else 0 end) Au4,
sum(数量) 合计
from tb
group by convert(varchar(7),时间,120)
union all
select convert(varchar(4),时间,120) 时间,
sum(case 类别 when 'Au1' then 数量 else 0 end) Au1,
sum(case 类别 when 'Au2' then 数量 else 0 end) Au2,
sum(case 类别 when 'Au3' then 数量 else 0 end) Au3,
sum(case 类别 when 'Au4' then 数量 else 0 end) Au4,
sum(数量) 合计
from tb
group by convert(varchar(4),时间,120)
/*
时间 Au1 Au2 Au3 Au4 合计
------- ----------- ----------- ----------- ----------- -----------
2007-10 100 200 300 400 1000
2007-11 200 200 200 300 900
2007 300 400 500 700 1900
*/
--静态SQL,指类型不止Au1,Au2,Au3,Au4四种.
declare @sql1 varchar(8000)
set @sql1 = 'select convert(varchar(7),时间,120) 时间'
select @sql1 = @sql1 + ' , sum(case 类别 when ''' + 类别 + ''' then 数量 else 0 end) [' + 类别 + ']'
from (select distinct 类别 from tb) as a
set @sql1 = @sql1 + ' ,sum(数量) 合计 from tb group by convert(varchar(7),时间,120)'
declare @sql2 varchar(8000)
set @sql2 = 'select convert(varchar(4),时间,120) 时间'
select @sql2 = @sql2 + ' , sum(case 类别 when ''' + 类别 + ''' then 数量 else 0 end) [' + 类别 + ']'
from (select distinct 类别 from tb) as a
set @sql2 = @sql2 + ',sum(数量) 合计 from tb group by convert(varchar(4),时间,120)'
exec(@sql1 + ' union all ' + @sql2)
/*
时间 Au1 Au2 Au3 Au4 合计
------- ----------- ----------- ----------- ----------- -----------
2007-10 100 200 300 400 1000
2007-11 200 200 200 300 900
2007 300 400 500 700 1900
*/
drop table tb