字段,表结构如下:
时间 类别 数量
2007-10-11 Au1 2000
2007-10-11 Au2 2000
2007-10-11 Au3 2000
2007-10-11 Au4 2000
2007-11-11 Au2 2000
2007-11-11 Au3 2000
2007-11-11 Au1 2000
2007-11-11 Au2 2000
2007-12-11 Au3 2000
2007-12-11 Au4 2000
2007-12-11 Au2 2000
合计之后的结果要这样的!
时间 Au1 Au2 Au3 Au4
2007-10 (小计) 2000 2000 2000 2000
2007-11 (小计) 2000 4000 2000 0
2007-12 (小计) 0 2000 3000 4000
2007(总计) 4000 8000 7000 6000请问各位应该怎么样来做!谢谢!
时间 类别 数量
2007-10-11 Au1 2000
2007-10-11 Au2 2000
2007-10-11 Au3 2000
2007-10-11 Au4 2000
2007-11-11 Au2 2000
2007-11-11 Au3 2000
2007-11-11 Au1 2000
2007-11-11 Au2 2000
2007-12-11 Au3 2000
2007-12-11 Au4 2000
2007-12-11 Au2 2000
合计之后的结果要这样的!
时间 Au1 Au2 Au3 Au4
2007-10 (小计) 2000 2000 2000 2000
2007-11 (小计) 2000 4000 2000 0
2007-12 (小计) 0 2000 3000 4000
2007(总计) 4000 8000 7000 6000请问各位应该怎么样来做!谢谢!
解决方案 »
- 花生壳地址解析不对
- 求救SQL Server 2000启动服务失败
- SQL SERVER 数据量大 查询问题?
- 周健,以及各位数据库大虾们快进来看看吧!这个问题怎么搞啊
- 关于Sql2005数据中的备份,如果还原到Sql2000中
- 本地计算机上的MSSQLSERVER服务启动后又停止了。一些服务自动停止,如果它们没有什么可做的...
- [求助]寻求往返程数据修改的帮助
- 多表查询问题向大家请教
- 征求计算工作时间的SQL语句
- 比较ITEM相同的两条记录日期范围是否生命
- visual studio 2005 中自带的SQL怎么用啊?
- 请问SQL SERVER 2005能在Windows XP系统里安装吗?
Au2=sum(case 类别 when 'Au2' then 数量 else 0 end),
Au3=sum(case 类别 when 'Au3' then 数量 else 0 end),
Au4=sum(case 类别 when 'Au4' then 数量 else 0 end)
from tablename
group by 时间
set @sql='select convert(varchar(7),时间,120) [时间]'
select @sql=@sql+',['+类别+']=max(case 类别 when '''+类别+''' then 数量 else 0 end)' from (select distinct 类别 from tt1)a
set @sql=@sql+' from tt1 group by convert(varchar(7),时间,120)'
set @sql=@sql+' union select distinct convert(varchar(4),时间,120)+''(总计)'' [时间]'
select @sql=@sql+',['+类别+']=(select sum(数量) from tt1 where 类别='''+ 类别+''')' from (select distinct 类别 from tt1)a
set @sql=@sql+' from tt1'
exec(@sql)
insert tb select '2007-10-11','Au1',2000
union all select '2007-10-11','Au2',2000
union all select '2007-10-11','Au3',2000
union all select '2007-10-11','Au4',2000
union all select '2007-11-11','Au2',2000
union all select '2007-11-11','Au3',2000
union all select '2007-11-11','Au1',2000
union all select '2007-11-11','Au2',2000
union all select '2007-12-11','Au3',2000
union all select '2007-12-11','Au4',2000
union all select '2007-12-11','Au2',2000select [时间]=convert(varchar(7),时间,120),
[Au1]=sum(case when 类别='Au1' then 数量 else 0 end),
[Au2]=sum(case when 类别='Au2' then 数量 else 0 end),
[Au3]=sum(case when 类别='Au3' then 数量 else 0 end),
[Au4]=sum(case when 类别='Au4' then 数量 else 0 end)
from tb
group by convert(varchar(7),时间,120)
union all
select [时间]=convert(varchar(4),时间,120),
[Au1]=sum(case when 类别='Au1' then 数量 else 0 end),
[Au2]=sum(case when 类别='Au2' then 数量 else 0 end),
[Au3]=sum(case when 类别='Au3' then 数量 else 0 end),
[Au4]=sum(case when 类别='Au4' then 数量 else 0 end)
from tb
group by convert(varchar(4),时间,120)
drop table tb
insert into tb values('2007-10-11','Au1',2000)
insert into tb values('2007-10-11','Au2',2000)
insert into tb values('2007-10-11','Au3',2000)
insert into tb values('2007-10-11','Au4',2000)
insert into tb values('2007-11-11','Au2',2000)
insert into tb values('2007-11-11','Au3',2000)
insert into tb values('2007-11-11','Au1',2000)
insert into tb values('2007-11-11','Au2',2000)
insert into tb values('2007-12-11','Au3',2000)
insert into tb values('2007-12-11','Au4',2000)
insert into tb values('2007-12-11','Au2',2000)
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
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
from tb
group by convert(varchar(4),时间,120)
/*
时间 Au1 Au2 Au3 Au4
------- ----------- ----------- ----------- -----------
2007-10 2000 2000 2000 2000
2007-11 2000 4000 2000 0
2007-12 0 2000 2000 2000
2007 4000 8000 6000 4000
*/--静态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 + ' 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 + ' from tb group by convert(varchar(4),时间,120)'
exec(@sql1 + ' union all ' + @sql2)
/*
时间 Au1 Au2 Au3 Au4
------- ----------- ----------- ----------- -----------
2007-10 2000 2000 2000 2000
2007-11 2000 4000 2000 0
2007-12 0 2000 2000 2000
2007 4000 8000 6000 4000
*/drop table tb
Select 时间, Au1, Au2, Au3, Au4
From (
Select 时间+'1' Type, 时间+'(小计)' As 时间, Sum(isNull(Au1,0)) As Au1, Sum(isNull(Au2,0)) As Au2, Sum(isNull(Au3,0)) As Au3, Sum(isNull(Au4,0)) As Au4
From (
Select Convert(Varchar(7), 时间, 120) As 时间,
Case When (类别 = 'Au1') Then 数量 Else 0 End As Au1,
Case When (类别 = 'Au2') Then 数量 Else 0 End As Au2,
Case When (类别 = 'Au3') Then 数量 Else 0 End As Au3,
Case When (类别 = 'Au4') Then 数量 Else 0 End As Au4
From @Temp
) T
Group By 时间
Union
Select 时间+'2' Type, Left(时间,4)+'(总计)', Sum(isNull(Au1,0)) As Au1, Sum(isNull(Au2,0)) As Au2, Sum(isNull(Au3,0)) As Au3, Sum(isNull(Au4,0)) As Au4
From (
Select Convert(Varchar(4), 时间, 120) As 时间,
Case When (类别 = 'Au1') Then 数量 Else 0 End As Au1,
Case When (类别 = 'Au2') Then 数量 Else 0 End As Au2,
Case When (类别 = 'Au3') Then 数量 Else 0 End As Au3,
Case When (类别 = 'Au4') Then 数量 Else 0 End As Au4
From @Temp
) T
Group By 时间
) T0
Order By Type, 时间
执行结果(所影响的行数为 10 行)时间 Au1 Au2 Au3 Au4
------------- ----------- ----------- ----------- -----------
2007-10(小计) 2000 2000 2000 2000
2007-11(小计) 2000 2000 2000 0
2007-12(小计) 0 2000 2000 2000
2007(总计) 4000 6000 6000 4000(所影响的行数为 4 行)
From (
Select Convert(Varchar(7), 时间, 120) As 时间,
Case When (类别 = 'Au1') Then 数量 Else 0 End As Au1,
Case When (类别 = 'Au2') Then 数量 Else 0 End As Au2,
Case When (类别 = 'Au3') Then 数量 Else 0 End As Au3,
Case When (类别 = 'Au4') Then 数量 Else 0 End As Au4
From @Temp
) T
Group By 时间 With Rollup