22,209
社区成员
发帖
与我相关
我的任务
分享
set nocount on ;
DECLARE @t TABLE(a int,b varchar(10),c varchar(10),d money)
INSERT @t SELECT '1 ', 'aa ', 'a ', 124
UNION ALL SELECT '2 ', 'aa ', 'a ', -23
INSERT @t SELECT '3 ', 'aa ', 'b ', 124
UNION ALL SELECT '4 ', 'aa ', 'b ', -23
INSERT @t SELECT '5 ', 'aa ', 'c ', 124
UNION ALL SELECT '6 ', 'aa ', 'd ', -23
INSERT @t SELECT '7 ', 'aa ', 'e ', 124
UNION ALL SELECT '8 ', 'aa ', 'f ', -23
INSERT @t SELECT '9 ', 'aa ', 'g ', 124
UNION ALL SELECT '10 ', 'aa ', 'f ', -23
UNION ALL SELECT '11 ', 'bb ' , 'a ',-23
UNION ALL SELECT '12 ', 'bb ', 'a ', 124
UNION ALL SELECT '13 ', 'bb ', 'e' , -90
UNION ALL SELECT '14 ', 'bb ' , 'f ',-23
UNION ALL SELECT '15 ', 'bb ', 'g ', 101
UNION ALL SELECT '16 ', 'bb ', 'g', -90
select
[b]=case when t.c is null then t.b+'總計' else t.b end,
[c]=isnull(t.c,''),t.总计,t.总额,case when t.c is null then stuff(t3.包含id.value('/R[1]','nvarchar(100)'),1,1,'') else stuff(t2.包含id.value('/R[1]','nvarchar(100)'),1,1,'')end
from
(select b,c,总计=count(1),总额=sum(d) from @t group by b,c with rollup having not (grouping(b)=1 and grouping(c)=1))T
outer apply
(select 包含id=(select N','+rtrim(a) from @T where b=t.b and c=t.c group by a for xml path(''),root('R'),type) )t2
outer apply
(select 包含id=(select N','+rtrim(a) from @T where b=t.b group by a for xml path(''),root('R'),type) )t3
b c 总计 总额
-------------- ---------- ----------- --------------------- ----------------------------------------------------------------------------------------------------
aa a 2 101.00 1,2
aa b 2 101.00 3,4
aa c 1 124.00 5
aa d 1 -23.00 6
aa e 1 124.00 7
aa f 2 -46.00 8,10
aa g 1 124.00 9
aa 總計 10 505.00 1,2,3,4,5,6,7,8,9,10
bb a 2 101.00 11,12
bb e 1 -90.00 13
bb f 1 -23.00 14
bb g 2 11.00 15,16
bb 總計 6 -1.00 11,12,13,14,15,16
set nocount on ;
DECLARE @t TABLE(a int,b varchar(10),c varchar(10),d money)
INSERT @t SELECT '1 ', 'aa ', 'a ', 124
UNION ALL SELECT '2 ', 'aa ', 'a ', -23
INSERT @t SELECT '3 ', 'aa ', 'b ', 124
UNION ALL SELECT '4 ', 'aa ', 'b ', -23
INSERT @t SELECT '5 ', 'aa ', 'c ', 124
UNION ALL SELECT '6 ', 'aa ', 'd ', -23
INSERT @t SELECT '7 ', 'aa ', 'e ', 124
UNION ALL SELECT '8 ', 'aa ', 'f ', -23
INSERT @t SELECT '9 ', 'aa ', 'g ', 124
UNION ALL SELECT '10 ', 'aa ', 'f ', -23
UNION ALL SELECT '11 ', 'bb ' , 'a ',-23
UNION ALL SELECT '12 ', 'bb ', 'a ', 124
UNION ALL SELECT '13 ', 'bb ', 'e' , -90
UNION ALL SELECT '14 ', 'bb ' , 'f ',-23
UNION ALL SELECT '15 ', 'bb ', 'g ', 101
UNION ALL SELECT '16 ', 'bb ', 'g', -90
select
t.*,case when t.c is null then stuff(t3.包含id.value('/R[1]','nvarchar(100)'),1,1,'') else stuff(t2.包含id.value('/R[1]','nvarchar(100)'),1,1,'')end
from
(select b,c,总计=count(1),总额=sum(d) from @t group by b,c with rollup having not (grouping(b)=1 and grouping(c)=1))T
outer apply
(select 包含id=(select N','+rtrim(a) from @T where b=t.b and c=t.c group by a for xml path(''),root('R'),type) )t2
outer apply
(select 包含id=(select N','+rtrim(a) from @T where b=t.b group by a for xml path(''),root('R'),type) )t3
b c 总计 总额
---------- ---------- ----------- --------------------- ----------------------------------------------------------------------------------------------------
aa a 2 101.00 1,2
aa b 2 101.00 3,4
aa c 1 124.00 5
aa d 1 -23.00 6
aa e 1 124.00 7
aa f 2 -46.00 8,10
aa g 1 124.00 9
aa NULL 10 505.00 1,2,3,4,5,6,7,8,9,10
bb a 2 101.00 11,12
bb e 1 -90.00 13
bb f 1 -23.00 14
bb g 2 11.00 15,16
bb NULL 6 -1.00 11,12,13,14,15,16
DECLARE @t TABLE(a int,b varchar(10),c varchar(10),d money)
INSERT @t SELECT '1', 'aa', 'a', 124
UNION ALL SELECT '2', 'aa', 'a', -23
INSERT @t SELECT '3', 'aa', 'b', 124
UNION ALL SELECT '4', 'aa', 'b', -23
INSERT @t SELECT '5', 'aa', 'c', 124
UNION ALL SELECT '6', 'aa', 'd', -23
INSERT @t SELECT '7', 'aa', 'e', 124
UNION ALL SELECT '8', 'aa', 'f', -23
INSERT @t SELECT '9', 'aa', 'g', 124
UNION ALL SELECT '10', 'aa', 'f', -23
UNION ALL SELECT '11', 'bb' , 'a',-23
UNION ALL SELECT '12', 'bb', 'a', 124
UNION ALL SELECT '13', 'bb', 'e', -90
UNION ALL SELECT '14', 'bb' , 'f',-23
UNION ALL SELECT '15', 'bb', 'g', 101
UNION ALL SELECT '16 ', 'bb ', 'g', -90
select * from t
create function get_str(@b varchar(10),@c varchar(10))
returns varchar(100)
as
begin
declare @str varchar(8000)
set @str=''
if @c=''
select @str=@str+cast(a as varchar(2))+',' from t where b=@b
else
select @str=@str+cast(a as varchar(2))+',' from t where b=@b and c=@c
return left(@str,len(@str)-1)
end
select b=case when c is null then b+'总计' else b end,c=isnull(c,''),总计=count(1),总额=sum(d),包含id=dbo.get_str(b,isnull(c,'')) from t group by b,c with ROLLUP
b c 总计 总额 包含id
-------------- ---------- ----------- --------------------- ----------------------------------------------------------------------------------------------------
aa a 2 101.00 1,2
aa b 2 101.00 3,4
aa c 1 124.00 5
aa d 1 -23.00 6
aa e 1 124.00 7
aa f 2 -46.00 8,10
aa g 1 124.00 9
aa总计 10 505.00 1,2,3,4,5,6,7,8,9,10
bb a 2 101.00 11,12
bb e 1 -90.00 13
bb f 1 -23.00 14
bb g 2 11.00 15,16
bb总计 6 -1.00 11,12,13,14,15,16
select b,c,count(1),sum(d) , fn_test(b,c) as 包含ID from @t group by b,c
select b,c,count(1),sum(d) , fn_test(b,c) as 包含ID from @t