mysql 生成交叉表_生成交叉表的SQL语句

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/*--生成交叉表的简单通用存储过程

根据指定的表名,纵横字段,统计字段,自动生成交叉表

并可根据需要生成纵横两个方向的合计

注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段

如果不要此功能,则去掉交换处理部分

--邹建   204.06--*/

/*--调用示例

exec   p_qry   'syscolumns','id','colid','colid','name   like   ''s%''',1,1

--*/

create   proc   p_qry

@TableName   sysname, --表名

@纵轴   sysname, --交叉表最左面的列

@横轴   sysname, --交叉表最上面的列

@表体内容   sysname, --交叉表的数数据字段

@条件   varchar(1000),--查询的处理条件

@是否加横向合计   bit, --为1时在交叉表横向最右边加横向合计

@是否家纵向合计   bit --为1时在交叉表纵向最下边加纵向合计

as

declare   @s   nvarchar(4000),@sql   varchar(8000)

--规范条件

set   @条件=case   when   @条件<>''   then   '   where   ('+@条件+')'   else   ''   end

--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段

--set   @s='declare   @a   sysname

--if(select   case   when   count(distinct   ['+@纵轴+'])

--from   ['+@TableName+']   '+@条件+')=1

--select   @a=@纵轴,@纵轴=@横轴,@横轴=@a'

--exec   sp_executesql   @s

--,N'@纵轴   sysname   out,@横轴   sysname   out'

--,@纵轴   out,@横轴   out

--生成交叉表处理语句

set   @s='

set   @s=''''

select   @s=@s+'',[''+cast(['+@横轴+']   as   varchar)+'']=sum(case   ['+@横轴

+']   when   ''''''+cast(['+@横轴+']   as   varchar)+''''''   then   ['+@表体内容+']   else   0   end)''

from   ['+@TableName+']

'+@条件+'

group   by   ['+@横轴+']'

exec   sp_executesql   @s

,N'@s   varchar(8000)   out'

,@sql   out

--是否生成合计字段的处理

declare   @sum1   varchar(200),@sum2   varchar(200),@sum3   varchar(200)

select   @sum1=case   @是否加横向合计

when   1   then   ',[合计]=sum(['+@表体内容+'])'

else   ''   end

,@sum2=case   @是否家纵向合计

when   1   then   '['+@纵轴+']=case   grouping(['

+@纵轴+'])   when   1   then   ''合计''   else   cast(['

+@纵轴+']   as   varchar)   end'

else   '['+@纵轴+']'   end

,@sum3=case   @是否家纵向合计

when   1   then   '   with   rollup'

else   ''   end

--生成交叉表

exec('select   '+@sum2+@sql+@sum1+'

from   ['+@TableName+']

'+@条件+'

group   by   ['+@纵轴+']'+@sum3)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

说明：

这个存储过程只能针对单表，所以要生成存储过程，首先创建一个复杂的查询，输出交叉表的标准的3列，然后将输出保存为一个

视图，最后将视图，传给存储过程，于是结果就出来了