有一表(T),共四列(L1,L2,L3,L4)
L1 L2 L3 L4
2-10 A 20
2-45 B 30
3-56 A 12
3-69 C 15
3-70 B 20
4-9 A 8想要得到
L1 L2 L3 L4
2-45 B 30
3-70 B 20
3-69 C 15
合计 45 20
L1 L2 L3 L4
2-10 A 20
2-45 B 30
3-56 A 12
3-69 C 15
3-70 B 20
4-9 A 8想要得到
L1 L2 L3 L4
2-45 B 30
3-70 B 20
3-69 C 15
合计 45 20
union all
select '','合计',sum(L3),sum(L4) from T where L2<>'A'
(
L1 varchar(10),
L2 varchar(10),
L3 int,
L4 int
)
insert into @t
select '2-10','A',20,null union all
select '2-45','B',30,null union all
select '3-56','A',null,12 union all
select '3-69','C',15,null union all
select '3-70','B',null,20 union all
select '4-9','A',null,8select *
from
(
select
L1,
case when grouping(L1)=1 then '合计' else L2 end as L2,
sum(L3) as L3,
sum(L4) as L4
from @t
where L2<>'A'
group by L1,L2 with rollup
)a
where L2 is not null/*
L1 L2 L3 L4
---------- ---------- ----------- -----------
2-45 B 30 NULL
3-69 C 15 NULL
3-70 B NULL 20
NULL 合计 45 20
*/
insert @t select '2-10','A',20, null
union all select '2-45','B',30, null
union all select '3-56','A',null,12
union all select '3-69','C',15,null
union all select '3-70','B',null,20
union all select '4-9' , 'A',null,8select * from
(
select * from @t
where l2<>'A'
union all
select '','合计',sum(isnull(l3,0)) as l3,sum(isnull(l4,0)) as l4
from @t where l2<> 'A') A
order by 2
select '2-10', 'A', 20, 0 union all select
'2-45', 'B', 30, 0 union all select
'3-56', 'A', 0 , '12' union all select
'3-69', 'C', 15 , '20' union all select
'4-9' , 'A', 0 , 8 select * from @tselect * from @t a where l2 not in (select l2 from @t where l2 = 'A')
insert into @A
Select '2-10', 'A', 20,null
union all
Select '2-45', 'B', 30,null
union all
Select '3-56', 'A', null, 12
union all
Select '3-69' , 'C' ,15,null
union all
Select '3-70', 'B',null, 20
union all
Select '4-9', 'A' ,null, 8
Declare @i int,@j int
set @i=(Select Sum(L3) from @A where L2 <>'A')
set @j=(Select Sum(L4) from @A where L2 <>'A')
Select * From @A where L2 <>'A'
union all
Select ' ','合计',@i,@j
where not exists(select 1 from t b where a.l2=b.l2 and sum(l3)-sum(l4)=0)
union select '','合计',sum(l3),sum(l4) from t
楼主是这个意思吧
未测试,自己改改
--------
declare @t table(l1 char(10),l2 char(5),l3 int,l4 int)insert @t
select '2-10', 'A', 20, 0 union all select
'2-45', 'B', 30, 0 union all select
'3-56', 'A', 0 , '12' union all select
'3-69', 'C', 15 , '20' union all select
'4-9' , 'A', 0 , 8 select * from @t a where l2 not in (select l2 from @t where l2 = 'A')
UNION
select null,'总'as l2,sum(l3),sum(l4) from @t
order by l1 desc
select '2-10', 'A', 20, 0 union all select
'2-45', 'B', 30, 0 union all select
'3-56', 'A', 0 , '12' union all select
'3-69', 'C', 15 , '20' union all select
'4-9' , 'A', 0 , 8 select * from @t a where l2 not in (select l2 from @t where l2 = 'A')
UNION
select null,'总'as l2,sum(l3),sum(l4) from @t where l2 not in (select l2 from @t where l2 = 'A')
order by l1 desc
declare @t table(l1 varchar(10),l2 char,l3 int,l4 int)
insert @t select '2-10','A',20, null
union all select '2-45','B',30, null
union all select '3-56','A',null,12
union all select '3-69','C',15,null
union all select '3-70','B',null,20
union all select '4-9' , 'A',null,8select l1,l2,l3,l4 from @t a
where not exists(select 1 from (select sum(l3) s3,sum(l4) s4 from @t b where a.l2=b.l2)x where s3-s4=0)
union select '','合计',sum(l3),sum(l4) from @t order by l2
select l1,l2,l3,l4 from @t a
where not exists(select 1 from @t b where a.l2=b.l2 having sum(l3)=sum(l4))
union select '','合计',sum(l3),sum(l4) from @t order by l2