一个很BT的汇总算法

create table test(c1 char(1),c2 char(1),c3 char(1),c4 char(1),c5 char(1))

go

declare @n int = 1;

while(@n < 1000)

begin

insert into test values(char(65+((cast(rand()*100 as int)) % 26)),char(65+((cast(rand()*100 as int)) % 26)),char(65+((cast(rand()*100 as int)) % 26)),char(65+((cast(rand()*100 as int)) % 26)),char(65+((cast(rand()*100 as int)) % 26)));

set @n += 1;

end

create table [user](name char(1));

go

insert into [user]

select 'A' as name 

union all select 'B'

union all select 'C'

union all select 'D'

union all select 'E'

union all select 'F'

union all select 'G'

union all select 'H'

union all select 'I'

union all select 'J'

union all select 'K'

union all select 'L'

union all select 'M'

union all select 'N'

union all select 'O'

union all select 'P'

union all select 'Q'

union all select 'R'

union all select 'S'

union all select 'T'

union all select 'U'

union all select 'V'

union all select 'W'

union all select 'X'

union all select 'Y'

union all select 'Z'

GO

select t.name,sum(t.c1Cnt) as c1,sum(t.c2Cnt) as c2,sum(t.c3Cnt) as c3,sum(t.c4Cnt) as c4,sum(t.c5Cnt) as c5 from

(

select u.name, case when t.c1 = u.name then t.Cnt end as c1Cnt

,case when t.c2 = u.name then t.Cnt end as c2Cnt

,case when t.c3 = u.name then t.Cnt end as c3Cnt

,case when t.c4 = u.name then t.Cnt end as c4Cnt

,case when t.c5 = u.name then t.Cnt end as c5Cnt

from [user] u,(select c1,c2,c3,c4,c5,count(1) as Cnt

from test

group by grouping sets((c1),(c2),(c3),(c4),(c5))) as t

) as t

group by t.name

 以上示例只是应一位大虾写出来的,在大数据量操作上,不敢恭维.

本示例纯属戏作.正规应用下,要保证数据量足够小,方可使用. 

转载于:https://www.cnblogs.com/laoyumi/archive/2011/06/27/2091551.html