这个sum应该怎么写?

[freshman123]

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

[freshman123]

朋友说用变量,可以我不会

[splory]

select * into #Tmp from data
union all
select '小记',sum(data),class into #Tmp from data group by data

select * from #Tmp order by class, data

drop table #Tmp

[ping3000]

create table #test(code varchar(10),data int ,class varchar(10))
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 class

drop table #test

[paoluo]

用Wtih　Rollup

Create Table data
(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
*/

[splory]

select rtrim(code) code,data,class into #Tmp from table1

insert into #Tmp select '小记',sum(data),class from table1 group by class

select * from #Tmp order by class,code

drop table #Tmp

经过测试

[wgsasd311]

create table data(code varchar(20),data int,class varchar(20))
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

[paoluo]

沒話說了，樓主，你測試過那個語句沒有？？