表:
F1 F2
-----------------
A 1
A 1
A 1
A 2
A 1
B 1
B 1
B 1
请问怎样实现如下结果(SQL解决):
F1 F2
-----------------
A 1
A 1
A 1
A 2
A 1
Total A: 6
B 1
B 1
B 1
Total B: 3
--数据
declare @t table (F1 varchar(10),F2 int)
insert @t select 'A',1
union all select 'A',1
union all select 'A',1
union all select 'A',2
union all select 'A',1
union all select 'B',1
union all select 'B',1
union all select 'B',1
union all select 'C',1
union all select 'C',2
union all select 'C',1
union all select 'C',3
--语句1.不显示最后一行(如果不要的话)
declare @count int
declare @s varchar(8000)
select id=identity(int,1,1), * into # from @t
set @count=@@rowcount+(select count(distinct(F1)) from #)
select @count
select @s=(
'select top '+rtrim(@count)+'
(
case isnumeric(isnull(rtrim(id),''Total:'')) when 1 then ''''
when 0 then (isnull(rtrim(id),''Total:'')) end
)+isnull(F1,''All'') as F1
,sum(F2) as F2
from #
group by F1,id with rollup
')
exec (@s)
--语句2,显示最后一行
select
(
case isnumeric(isnull(rtrim(id),'Total:')) when 1 then ''
when 0 then (isnull(rtrim(id),'Total:')) end
)+isnull(F1,'All') as F1
,sum(F2) as F2
from #
group by F1,id with rollup
--破弃测试环境
drop table #
/*
结果集1:
F1 F2
---------------------- -----------
A 1
A 1
A 1
A 2
A 1
Total:A 6
B 1
B 1
B 1
Total:B 3
C 1
C 2
C 1
C 3
Total:C 7
结果集2
F1 F2
---------------------- -----------
A 1
A 1
A 1
A 2
A 1
Total:A 6
B 1
B 1
B 1
Total:B 3
C 1
C 2
C 1
C 3
Total:C 7
Total:All 16
*/