如表 T_1 中有字段 A,B 假设数据如下
A B
王 12
李 10
王 20
张 30
我想返回 1 王 12
2 王 20
3 王(小计) 32
4 李 10
5 李(小计) 10
6 张 30
7 张(小计) 30 用 union all 来实现可以吗???
A B
王 12
李 10
王 20
张 30
我想返回 1 王 12
2 王 20
3 王(小计) 32
4 李 10
5 李(小计) 10
6 张 30
7 张(小计) 30 用 union all 来实现可以吗???
insert into @t
select '王',12 union all
select '李',10 union all
select '王',20 union all
select '张',30select a= case when grouping(b)=1 then a + '(小计)'else a end,b = sum(b)
from @t
group by a,b with rollup
having grouping(a)=0 or grouping(b)=1
insert test select '王',12
union all select '李',10
union all select '王',20
union all select '张',30
select A,B from
(
select A,B,s1=0,s2=A,s3=0 from test
union all
select A+'(小计)',sum(B),s1=0,s2=A,s3=1 from test
group by A
)a
order by s1,s2,s3drop table testA B
---------------- -----------
李 10
李(小计) 10
王 12
王 20
王(小计) 32
张 30
张(小计) 30
--主要在 with rollupdeclare @t table(a varchar(10),b int)
insert into @t
select '王',12 union all
select '李',10 union all
select '王',20 union all
select '张',30select a= case when grouping(b)=1 then a + '(小计)' else a end,b = sum(b)
from @t
group by a,b with rollup
having grouping(a)=0
insert test select '王',12
union all select '李',10
union all select '王',20
union all select '张',30create table tmp(id int identity(1,1),A varchar(10),B int)
goinsert tmp(A,B)
select A,B from
(
select A,B,s2=A,s3=0 from test
union all
select A+'(小计)',sum(B),s2=A,s3=1 from test
group by A
)a
order by s2,s3select * from tmpdrop table test,tmpid A B
----------- ---------- -----------
1 李 10
2 李(小计) 10
3 王 12
4 王 20
5 王(小计) 32
6 张 30
7 张(小计) 30(所影响的行数为 7 行)
declare @tt table(id int identity(1,1),a varchar(10),b int)
insert into @t
select '王',12 union all
select '李',10 union all
select '王',20 union all
select '张',30insert into @tt
select a= case when grouping(b)=1 then a + '(小计)' else a end,b = sum(b)
from @t
group by a,b with rollup
having grouping(a)=0select * from @tt
insert into @t
select '王',12 union all
select '李',10 union all
select '王',20 union all
select '张',30select id=count(a)+1,a,c=sum(b) from @t group by a
union all
select id=(select count(1) from @t where t.a=a and t.b<=b),*
from @t t
order by a,id
insert into @t
select '王',12 union all
select '李',10 union all
select '王',20 union all
select '张',30
select * from (
select id=count(a)+1,a=a+'(小计)',b=sum(b) from @t group by a
union all
select id=(select count(1) from @t where t.a=a and t.b<=b),*
from @t t) d
order by a,id