SQL查询分组合计语句,请大哥前辈指教!谢谢!急,急,急......

字段,表结构如下:
时间类别数量
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

请问各位应该怎么样来做!谢谢!

作者: cr198 发布时间: 2007-11-04

select 时间, 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 tablename
group by 时间

作者: fengming2222 发布时间: 2007-11-04

SQL code

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 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)

作者: pt1314917 发布时间: 2007-11-04

SQL code

create table tb(时间 datetime,类别 varchar(10),数量 int)
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',2000

select [时间]=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

作者: liangCK 发布时间: 2007-11-04

SQL code

create table tb(时间 datetime,类别 varchar(10),数量 int)
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

作者: dawugui 发布时间: 2007-11-04

楼上的都是人才啊,顶起!!!1顶起

作者: zfanlong1314 发布时间: 2011-06-06

SQL code


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 行）

作者: Lyongt 发布时间: 2011-06-06

Select 时间+'(小计)' 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 时间 With Rollup

作者: Lyongt 发布时间: 2011-06-06

SQL查询分组合计语句,请大哥前辈指教!谢谢!急,急,急......

热门频道