34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb(InTime datetime,DXUnit varchar(20),GrossWeight float)
insert into tb select '2007-1-20','A','2000'
insert into tb select '2007-2-21','B','300'
insert into tb select '2007-3-20','C','400'
insert into tb select '2007-4-20','B','300'
insert into tb select '2008-1-20','A','200'
insert into tb select '2008-2-20','A','100'
insert into tb select '2008-3-20','B','300'
insert into tb select '2008-4-20','C','200'
go
declare @year varchar(4)
set @year='2007'
declare @sql varchar(8000),@sql1 varchar(8000)
set @sql='select convert(varchar(7),intime,120)[Intime]'
select @sql=@sql+','+DXunit+'=sum(case DXunit when '''+DXunit+''' then grossweight else 0 end)'
from (select distinct DXunit from tb where datepart(yy,intime)=@year)a
set @sql=@sql+',sum(grossweight)[总计] from tb where datepart(yy,intime)='''+@year+''' group by convert(varchar(7),intime,120)'
set @sql1='select datename(yy,intime)'
select @sql1=@sql1+','+DXunit+'=sum(case DXunit when '''+DXunit+''' then grossweight else 0 end)'
from (select distinct DXunit from tb where datepart(yy,intime)=@year)a
set @sql1=@sql1+',sum(grossweight)[总计] from tb where datepart(yy,intime)='''+@year+''' group by datename(yy,intime)'
exec(@sql+ ' union all '+@sql1)
--建立测试环境
set nocount on
create table test(InTime datetime,DXUnit varchar(20),GrossWeight float)
insert into test select '2007-1-20','A','2000'
insert into test select '2007-2-21','B','300'
insert into test select '2007-3-20','C','400'
insert into test select '2007-4-20','B','300'
insert into test select '2008-1-20','A','200'
insert into test select '2008-2-20','A','100'
insert into test select '2008-3-20','B','300'
insert into test select '2008-4-20','C','200'
go
--测试
declare @time varchar(4)
set @time='2007'
declare @sql varchar(8000)
set @sql='select intime,'
select @sql=@sql+'sum(case when dxunit='''+dxunit+''' then GrossWeight else 0 end)['
+dxunit+'],' from (select distinct dxunit from test) a
set @sql=@sql+'sum(GrossWeight)[all] from test where year(intime)='+@time+' group by intime'
print @sql
exec (@sql)
--删除测试环境
drop table test
set nocount off