关于组合统计的问题(高手进)

Question

本人想做一个组合统计，谁可以告诉我怎么实现表如下形式：id    sex    school    government   area  
-----------------------------------------
***    男     中学        团员      北京
***    男     大学        党员      上海
***    女     小学        群众      北京
***    女     中学        群众      天津
***    男     小学        党员      上海
***    女     大学        团员      北京
-------------------------------------------现在想统计出一个列表，列表横轴和纵轴 分别可由以上4个字段中的一个或几个组合而成。列表形式如下：比如  由性别和学历组成的：                    性别
           --------   -----------
              男         女      小学    0           1
 
      中学    2           3
 
      大学    1           2     （图中数字代表交叉统计的结果数）  
请高手帮帮忙

Answer 1

如果需求是固定的，可以用静态SQL:select 
    school,
    sum(case sex when '男' then 1 else 0 end) as 男,
    sum(case sex when '女' then 1 else 0 end) as 女
from
    表
group by
    school否则用动态SQL：declare @sql varchar(8000)
set @sql='select school'select @sql=@sql+',['+sex+']=sum(case sex when '''+sex+''' then 1 else 0 end) as '+['+sex+']'  from 表 group by sexset @sql=@sql+' from 表 group by school'
exec(@sql)

Answer 2

楼主分真多啊.
跟下.:)select 
  学历=school,
  [性别(男)]=sum(case when sex='男' then 1 else 0 end),
  [性别(女)]=sum(case when sex='女' then 1 else 0 end)
from 表
group by school

Answer 3

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 '***','女','大学','团员','北京'
godeclare @sql varchar(8000)
set @sql='select school'select @sql=@sql+',['+sex+']=sum(case sex when '''+sex+''' then 1 else 0 end) '  
from t group by sexset @sql=@sql+' from t group by school'
print @sqlexec(@sql)
go/*
school 男           女           
------ ----------- ----------- 
大学     1           1
小学     1           1
中学     1           1
*/drop table t
go

Answer 4

declare @order nvarchar(20)
set @order='小学,中学,大学'
select 
    school,
    '男'=sum(case sex when '男' then 1 else 0 end),
    '女'=sum(case sex when '女' then 1 else 0 end)
from
    表
group by
    school
order by charindex(school,@order)

Answer 5

是不是只能实现纵轴的动态实现？  如果需要横轴（如上述的性别字段）也能够动态实现呢？想实现横纵都可以动态定制比如：/*
school 群众    团员    党员           
------ ----  ----- ----------- 
大学     1     1      1
小学     1     1      1
中学     1     1      1
*/或着/*
school 群众    团员    党员           
------ ----  ----- ----------- 
北京     1     1      1
上海     1     1      1
天津     1     1      1
*/又或者/*
school 群众    团员    党员     北京  上海   天津      
------ ----  ----- -----------  ----  ----- -----
大学     1     1      1           1     1     1  
小学     1     1      1           1     1     1
中学     1     1      1
*/又或者/*
school 群众    团员    党员     北京  上海   天津      
------ ----  ----- -----------  ----  ----- -----
大学     1     1      1           1     1     1  
小学     1     1      1           1     1     1
中学     1     1      1           1     1     1
男       
女
*/

Answer 6

如下列查询，应该可以分做两个吧：分别按照school和sex查出信息/*
school 群众    团员    党员     北京  上海   天津      
------ ----  ----- -----------  ----  ----- -----
大学     1     1      1           1     1     1  
小学     1     1      1           1     1     1
中学     1     1      1           1     1     1
男       
女
*/

Answer 7

--借用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

Answer 8

呵呵  楼上的兄弟（zlp321002泰迪熊）把我说的几个情况都做了一遍呀。。辛苦了不过以上几个我只是举个例子，项目里实际需要统计的项目有7个，实际的组合可能会有几十种吧，需要实现自由组合统计。。我想可不可以搭建一个可变的SQL语句，大家多费费心呵

Answer 9

--生成测试数据
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
godeclare @sql varchar(8000)
set @sql=@sql+' from t group by school'
print @sqlexec(@sql)
go

Answer 10

--生成测试数据
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

Answer 11

非常感谢 libin_ftsafe(子陌红尘：当libin告别ftsafe) 的答案  我去好好参考下