数据分组问题

F1       F2
-----------------
A         1
A         1
A         1
A         2
A         1
B         1
B         1
B         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

--数据
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 #

/*

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

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

*/

• 本文已收录于以下专栏：

举报原因： 您举报文章：数据分组问题 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)