关闭

数据分组问题

813人阅读 评论(1) 收藏 举报

表:
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

*/

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:260292次
    • 积分:2969
    • 等级:
    • 排名:第11810名
    • 原创:62篇
    • 转载:53篇
    • 译文:0篇
    • 评论:50条
    最新评论