关于组合统计的问题(高手进)
本人想做一个组合统计，谁可以告诉我怎么实现
表如下形式：
id sex school government area
-----------------------------------------
*** 男 中学 团员 北京
*** 男 大学 党员 上海
*** 女 小学 群众 北京
*** 女 中学 群众 天津
*** 男 小学 党员 上海
*** 女 大学 团员 北京
-------------------------------------------
现在想统计出一个列表，列表横轴和纵轴 分别可由以上4个字段中的一个或几个组合而成。
列表形式如下：
比如 由性别和学历组成的：
性别
-------- -----------
男 女
小学 0 1
中学 2 3
大学 1 2 （图中数字代表交叉统计的结果数）
请高手帮帮忙。。。
[解决办法]
--借用libin_ftsafe(子陌红尘：当libin告别ftsafe) 数据一用
create table t(id varchar(6),sex varchar(6),school varchar(6),government varchar(6),area varchar(6))
insert into t select '*** ', '男 ', '中学 ', '团员 ', '北京 '
insert into t select '*** ', '男 ', '大学 ', '党员 ', '上海 '
insert into t select '*** ', '女 ', '小学 ', '群众 ', '北京 '
insert into t select '*** ', '女 ', '中学 ', '群众 ', '天津 '
insert into t select '*** ', '男 ', '小学 ', '党员 ', '上海 '
insert into t select '*** ', '女 ', '大学 ', '团员 ', '北京 '
go
1:
select
school,
团员=sum(case when government= '团员 ' then 1 else 0 end),
党员=sum(case when government= '党员 ' then 1 else 0 end),
群众=sum(case when government= '群众 ' then 1 else 0 end)
from t
group by school
2:
select
school,
北京=sum(case when area= '北京 ' then 1 else 0 end),
上海=sum(case when area= '上海 ' then 1 else 0 end),
天津=sum(case when area= '天津 ' then 1 else 0 end)
from t
group by school
3:
select ta.*,tb.北京,tb.上海,tb.天津 from
(
select
school,
团员=sum(case when government= '团员 ' then 1 else 0 end),
党员=sum(case when government= '党员 ' then 1 else 0 end),
群众=sum(case when government= '群众 ' then 1 else 0 end)
from t
group by school
) ta left join
(
select
school,
北京=sum(case when area= '北京 ' then 1 else 0 end),
上海=sum(case when area= '上海 ' then 1 else 0 end),
天津=sum(case when area= '天津 ' then 1 else 0 end)
from t
group by school
)tb
on ta.school=tb.school
4.
select ta.*,tb.北京,tb.上海,tb.天津 from
(
select
school,
团员=sum(case when government= '团员 ' then 1 else 0 end),
党员=sum(case when government= '党员 ' then 1 else 0 end),
群众=sum(case when government= '群众 ' then 1 else 0 end)
from t
group by school
) ta left join
(
select
school,
北京=sum(case when area= '北京 ' then 1 else 0 end),
上海=sum(case when area= '上海 ' then 1 else 0 end),
天津=sum(case when area= '天津 ' then 1 else 0 end)
from t
group by school
)tb
on ta.school=tb.school
union all
select school= '男 ',
团员=sum(case when (government= '团员 ' and sex= '男 ') then 1 else 0 end),
党员=sum(case when (government= '党员 ' and sex= '男 ') then 1 else 0 end),
群众=sum(case when (government= '群众 ' and sex= '男 ') then 1 else 0 end),
北京=sum(case when (government= '北京 ' and sex= '男 ') then 1 else 0 end),
上海=sum(case when (government= '上海 ' and sex= '男 ') then 1 else 0 end),
天津=sum(case when (government= '天津 ' and sex= '男 ') then 1 else 0 end)
from t
union all
select school= '女 ',
团员=sum(case when (government= '团员 ' and sex= '女 ') then 1 else 0 end),
党员=sum(case when (government= '党员 ' and sex= '女 ') then 1 else 0 end),
群众=sum(case when (government= '群众 ' and sex= '女 ') then 1 else 0 end),
北京=sum(case when (government= '北京 ' and sex= '女 ') then 1 else 0 end),
上海=sum(case when (government= '上海 ' and sex= '女 ') then 1 else 0 end),
天津=sum(case when (government= '天津 ' and sex= '女 ') then 1 else 0 end)
from t
--说明: 如果知道规则,然后修改成动态SQL就可以了.
--也可以用一个存储过程,根据不同需求,产生不同报表.
--drop table t
[解决办法]
--生成测试数据
create table t(id varchar(6),sex varchar(6),school varchar(6),government varchar(6),area varchar(6))
insert into t select '*** ', '男 ', '中学 ', '团员 ', '北京 '
insert into t select '*** ', '男 ', '大学 ', '党员 ', '上海 '
insert into t select '*** ', '女 ', '小学 ', '群众 ', '北京 '
insert into t select '*** ', '女 ', '中学 ', '群众 ', '天津 '
insert into t select '*** ', '男 ', '小学 ', '党员 ', '上海 '
insert into t select '*** ', '女 ', '大学 ', '团员 ', '北京 '
go
--创建存储过程
--@str_col：用于横向排列的列，以 ', '作为结束符
--@str_row：用户纵向分组的列，以 ', '作为结束符
create procedure sp_test(@str_col varchar(80),@str_row varchar(80))
as
begin
declare @sql varchar(8000),
@str1 varchar(8000),
@str2 varchar(8000),
@temp nvarchar(4000),
@col varchar(20),
@row varchar(20)
set @sql = ' '
set @str1= ' '
while charindex( ', ',@str_col)> 0
begin
set @col=left(@str_col,charindex( ', ',@str_col)-1)
set @str_col=stuff(@str_col,1,charindex( ', ',@str_col), ' ')
set @temp=N 'set @s= ' ' ' '
select @s=@s+ ' ',[ ' '+ '+@col+ '+ ' ']=sum(case '+@col+ ' when ' ' ' ' ' '+ '+@col+ '+ ' ' ' ' ' ' then 1 else 0 end) ' '
from t group by '+@col
exec sp_executesql @temp,N '@s varchar(8000) out ',@str2 out
set @str1=@str1+@str2
end
while charindex( ', ',@str_row)> 0
begin
set @row=left(@str_row,charindex( ', ',@str_row)-1)
set @str_row=stuff(@str_row,1,charindex( ', ',@str_row), ' ')
set @sql=@sql+ ' union all select '+@row+ ' as 项目 '+@str1+ ' from t group by '+@row
end
set @sql=stuff(@sql,1,11, ' ')
exec(@sql)
end
go
--执行测试
exec sp_test 'school,sex, ', 'government,area, '
go
--输出测试结果
/*
项目 大学 小学 中学 男 女
------ ----------- ----------- ----------- ----------- -----------
党员 1 1 0 2 0
群众 0 1 1 0 2
团员 1 0 1 1 1
北京 1 1 1 1 2
上海 1 1 0 2 0
天津 0 0 1 0 1
*/
--删除测试环境
drop table t
drop procedure sp_test
go
