data表,code,data,class字段code data class
001 100 01
002 60 01
003 200 02
004 150 03
005 256 02想得到
code data class
001 100 01
002 60 01
小记 160 01
003 200 02
005 256 02
小记 456 02
004 150 03
小记 150 03
累计 766
001 100 01
002 60 01
003 200 02
004 150 03
005 256 02想得到
code data class
001 100 01
002 60 01
小记 160 01
003 200 02
005 256 02
小记 456 02
004 150 03
小记 150 03
累计 766
union all
select '小记',sum(data),class into #Tmp from data group by dataselect * from #Tmp order by class, datadrop table #Tmp
insert into #test(code,data,class)
select '001',100,'01' union all
select '002',60,'01' union all
select '003',200,'02' union all
select '004',150,'03' union all
select '005',256,'02'select code,data,class from #test
union all
select '小计',sum(data),class from #test group by class
union all
select '合计',sum(data),'所有' from #test
order by classdrop table #test
(code Char(3),
data Int,
class Char(2))
Insert data Select '001', 100, '01'
Union All Select '002', 60, '01'
Union All Select '003', 200, '02'
Union All Select '004', 150, '03'
Union All Select '005', 256, '02'
GO
Select
(Case When Grouping(class)=1 Then N'累计' When Grouping(code)=1 Then N'小计' Else code End) As code,
SUM(data) As data,
class
From data
Group By class,code
With Rollup
GO
Drop Table data
--Result
/*
code data class
001 100 01
002 60 01
小计 160 01
003 200 02
005 256 02
小计 456 02
004 150 03
小计 150 03
累计 766 NULL
*/
insert data
select '001',100,'01' union all
select '002',60,'01' union all
select '003',200,'02' union all
select '004',150,'03' union all
select '005',256,'02'
--test
select * from (
select code,data,class from data
union
select '小计',sum(data),class from data group by class
union
select '累计',sum(data),'' from data
) aa order by (case class when '' then 1 else 0 end),class
/*
code data class
001 100 01
002 60 01
小记 160 01
003 200 02
005 256 02
小记 456 02
004 150 03
小记 150 03
累计 766
*/
--drop
drop table data