原始表格如下:InTime DXUnit GrossWeight
2007-1-20 A 2000
2007-2-21 B 300
2007-3-20 C 400
2007-4-20 B 300
2008-1-20 A 200
2008-2-20 A 100
2008-3-20 B 300
2008-4-20 C 200
我想要的结果是这样的!
在排列之后,外面有个时间条件,如果我修选择的时间是2007
则现实如下
Intime A B C 总计
2007-01 2000 0 0 2000
2007-02 0 300 0 300
2007-03 0 0 400 400
2007-04 0 300 0 300
2007 2000 600 400 3000如果我时间选择的是2008的话,哪么现实也和上面的一样的行列统计!
2007-1-20 A 2000
2007-2-21 B 300
2007-3-20 C 400
2007-4-20 B 300
2008-1-20 A 200
2008-2-20 A 100
2008-3-20 B 300
2008-4-20 C 200
我想要的结果是这样的!
在排列之后,外面有个时间条件,如果我修选择的时间是2007
则现实如下
Intime A B C 总计
2007-01 2000 0 0 2000
2007-02 0 300 0 300
2007-03 0 0 400 400
2007-04 0 300 0 300
2007 2000 600 400 3000如果我时间选择的是2008的话,哪么现实也和上面的一样的行列统计!
解决方案 »
- SQL服务器性能讨论
- 求一比较时间的sql语句或者存储过程
- 急!!!
- 想了很久未解决,一个周期问题
- 呵呵:一个表中有1600万,用循环怎么写,每次删除100万
- 在数据库中插入含有双引号的字符串:
- SQL 2000 安装(采用默认设置)后,在Enterprise Manage中点击数据库时显示“连接失败,请检查SQL Server注册属性”,
- 哪里有jdbc driver for MS SQL Server <无内容>
- pb中如何用OLEDB连接SQL SERVER数据库
- 请问PARADOX数据库是否支持级连删除,以及在哪里可以定义主外键?
- 试写出一个将职工的相关信息按NAME排序、且无重复NAME的前20条记录的SQL语句
- 拆很长很长的字符串,速度问题
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
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'
godeclare @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)