比较复杂的动态SQL语句功能一例

来源：互联网发布：写小说的软件编辑：程序博客网时间：2024/05/16 06:17

今天在论坛里见到一个关于动态SQL问题，觉得有点意思，于是解答了一下，顺便把我的解决方案转到这里。

----------------------------------------------------------------------------------------------------------------

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