Question:
本人想做一个组合统计,谁可以告诉我怎么实现。
表如下形式:
id sex school government area
-- ---------------------------------------
*** 男 中学 团员 北京
*** 男 大学 党员 上海
*** 女 小学 群众 北京
*** 女 中学 群众 天津
*** 男 小学 党员 上海
*** 女 大学 团员 北京
-- -----------------------------------------
现在想统计出一个列表,列表横轴和纵轴 分别可由以上4个字段中的一个或几个组合而成。实现横纵都可以动态定制。
比如:
/**/ /*
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:
-- 生成测试数据
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
本人想做一个组合统计,谁可以告诉我怎么实现。
表如下形式:
id sex school government area
-- ---------------------------------------
*** 男 中学 团员 北京
*** 男 大学 党员 上海
*** 女 小学 群众 北京
*** 女 中学 群众 天津
*** 男 小学 党员 上海
*** 女 大学 团员 北京
-- -----------------------------------------
现在想统计出一个列表,列表横轴和纵轴 分别可由以上4个字段中的一个或几个组合而成。实现横纵都可以动态定制。
比如:
/**/ /*
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:
-- 生成测试数据
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