数据库中的交叉表实例

统计分析是软件开发中比较棘手的问题，一是需求变化快且需求多；二是软件层面数据处理读取量大，处理效率低；三是软件代码实现复杂，本文给大家提供了一种数据库层面的解决方案，仅供参考。

——以下为交叉表存储过程源码

CREATEprocedure[dbo].[PS_交叉表_通用](

@源表varchar(1000)

,@条件字段varchar(50),@条件值varchar(50)

,@统计字段varchar(50)

,@统计方式varchar(10)

,@值字段varchar(50)

,@目标表varchar(50)output,@统计字段排序varchar(300)output

)as

declare@排序最大列数integer,@维度integer

set@排序最大列数=10

if@条件字段isnotnulland@条件值isnullreturn-1

if@一维IDisnull

or@统计字段isnullor@源表isnullor@目标表isnullreturn-1

set@目标表=LTRIM(RTRIM(@目标表))+LTRIM(RTRIM(ISNULL(@区域ID,'')))

declare@errnointeger,@errmsgvarchar(255)

setxact_aborton

begintran

declare@sqlvarchar(5000)

set@sql=' declare @sqlstr varchar(4000)

declare cs_Column cursor localfor

select distinct top 100percent '+@统计字段+' from '+@源表

+' t where 1=1

/* and '+@值字段+' is not null */'

if@条件字段isnotnull

set@sql=@sql+' and '+@条件字段+' = '''+@条件值+''''

-----------创建表------------------

select@sql=@sql

+' order by '+@统计字段

+' if exists (select * from sysobjects where name = '''+@目标表+''')

drop table '+@目标表

--创建表

+' set @sqlstr = '' create table dbo.'+@目标表+'( ['

--一维字段

set@维度=1

--二维字段

if@二维IDisnotnullbegin

set@sql=@sql

set@维度=@维度+1

end

--三维字段

if@三维IDisnotnullbegin

set@sql=@sql

set@维度=@维度+1

end

--四维字段

if@四维IDisnotnullbegin

set@sql=@sql

set@维度=@维度+1

end

set@sql=@sql+'''

set @sqlstr = @sqlstr + '' ,合计float''

open cs_Column

declare @static varchar(50), @日期datetime

fetch next from cs_Column into @static

while @@fetch_status = 0 begin

if @static is null set @static = ''空值''

select @sqlstr = @sqlstr + '', ['' +@static + ''] float''

fetch next from cs_Column into @static

end

close cs_Column

deallocate cs_Column'

select@sql=@sql+' set @sqlstr = @sqlstr + '' ) ''

if datalength(@sqlstr)

exec (@sqlstr)'

exec(@sql)

ifnotexists(select*fromsysobjectswherename=@目标表)begin

select@errno=30001,@errmsg='统计的横向列太多，不能创建'

gotoerror

end

------------插入行--------------

declare@维度字段名varchar(1000),@内部值变量varchar(1000),@内部值变量加引号varchar(1000)

,@内部变量值串加逗号varchar(1000)

,@本地变量判断varchar(1000)

,@本地变量赋值varchar(1000)

,@维度字段非空varchar(1000)

select@sql=' declare @sqlstr varchar(4000)

set @sqlstr = ''''

set @一维tmp= '''' '

,@维度字段名=@一维ID

,@内部值变量='@一维ID'

,@内部值变量加引号=''''''' + @一维ID +'''''''

,@本地变量判断=' @一维ID =@一维tmp'

,@本地变量赋值=' @一维tmp= @一维ID'

,@内部变量值串加逗号=''''''''' + @一维ID +'''''''''

if@二维IDisnotnull

set @二维tmp= '''' '

,@维度字段名=@维度字段名+','+@二维ID

,@内部值变量=@内部值变量+',@二维ID'

,@内部值变量加引号=@内部值变量加引号+','''''' + @二维ID +'''''' '

,@本地变量判断=@本地变量判断+' and @二维ID =@二维tmp'

,@本地变量赋值=@本地变量赋值+' , @二维tmp= @二维ID'

,@内部变量值串加逗号=@内部变量值串加逗号+' + '',''+ '''''''' + @二维ID + '''''''''

if@三维IDisnotnull

set @三维tmp= '''' '

,@维度字段名=@维度字段名+','+@三维ID

,@内部值变量=@内部值变量+',@三维ID'

,@内部值变量加引号=@内部值变量加引号+','''''' +@三维ID + '''''' '

,@本地变量判断=@本地变量判断+' and @三维ID =@三维tmp'

,@本地变量赋值=@本地变量赋值+' , @三维tmp= @三维ID'

,@内部变量值串加逗号=@内部变量值串加逗号+'+ '','' +'''''''' + @三维ID + '''''''''

if@四维IDisnotnull

set @四维tmp= '''' '

,@维度字段名=@维度字段名+','+@四维ID

,@内部值变量=@内部值变量+',@四维ID'

,@内部值变量加引号=@内部值变量加引号+','''''' +@四维ID + '''''' '

,@本地变量判断=@本地变量判断+' and @四维ID =@四维tmp'

,@本地变量赋值=@本地变量赋值+' , @四维tmp= @四维ID'

,@内部变量值串加逗号=@内部变量值串加逗号+'+ '','' +'''''''' + @四维ID + '''''''''

set@sql=@sql+' '

--------------编辑数据---------------------

set@sql=@sql+'

declare @static varchar(50)

declare @数量float, @合计float

declare @字段列varchar(2000), @值列varchar(2000), @固定字段列值varchar(500)

select @合计= 0, @字段列='''', @值列= '''', @固定字段列值= ''''

declare cs_data cursor for

select '+@维度字段非空+',isnull(cast('+@统计字段+' asvarchar(50)), ''空值''), '+@统计方式+'(isnull('+@值字段+', 0))

from '+@源表

+' t where 1=1 '

if@条件字段isnotnull

set@sql=@sql+'

and '+@条件字段+' = '''+@条件值+''''

set@sql=@sql+'

group by '+@维度字段名+','+@统计字段+'

order by '+@维度字段名+','+@统计字段+'

open cs_data

fetch next from cs_data into '+@内部值变量+', @static,@数量

while @@fetch_status = 0 begin

if '+@本地变量判断+' begin

if @数量is not null

select @字段列= @字段列+'',['' + @static + '']'', @值列= @值列+ '','' + case when @数量is null then ''NULL'' elsecast(@数量as varchar) end , @合计= @合计+ISNULL(@数量, 0)

end else begin

if @一维tmp '''' begin

set @sqlstr = '' insert into '+@目标表+'

('+@维度字段名+',合计'' +@字段列+ '')

values

('' + @固定字段列值+'',''+ cast(@合计as varchar) + @值列+ '')''

exec (@sqlstr)

end

if @数量is not null

select @字段列='',['' + @static + '']'', @值列= '','' + case when @数量is null then ''NULL'' elsecast(@数量as varchar) end ,@合计= ISNULL(@数量,0), @固定字段列值= '+@内部变量值串加逗号+'

else

select @字段列='''', @值列= '''' ,@合计= 0, @固定字段列值= '+@内部变量值串加逗号+'

select '+@本地变量赋值+'

end

fetch next from cs_data into '+@内部值变量+', @static,@数量

end

set @sqlstr = '' insert into '+@目标表+'

('+@维度字段名+',合计'' +@字段列+ '')

values

('' + @固定字段列值+'',''+ cast(@合计as varchar) + @值列+ '')''

if @固定字段列值 ''''

exec (@sqlstr)

close cs_data

deallocate cs_data'

print@sql

exec(@sql)

--单独读取统计字段排序的列表

declare@innerSqlnvarchar(1000),@iinteger,@namevarchar(50)

select@i=1,@统计字段排序=''

set@innerSql=N'

declare cs cursor local for

select name from syscolumns

where id in

(

select id from sysobjects

where [name] = @目标表)

open cs

fetch next from cs into @name

while @@fetch_status = 0 begin

if @i > @维度+ 1and @排序最大列数> 0 begin

set @排序最大列数= @排序最大列数-1

if @i @维度+ 2

set @统计字段排序= @统计字段排序+'',''

set @统计字段排序= @统计字段排序+''[''+ @name +''] desc ''

end

fetch next from cs into @name

set @i = @i + 1

end

close cs

deallocate cs

'

EXECsp_executesql@innerSql

,N'@统计字段排序varchar(300) output, @目标表varchar(50), @i int, @维度int,@排序最大列数int,@name varchar(50)'

,@统计字段排序output,@目标表,@i,@维度,@排序最大列数,@name

if@统计字段排序=''

set@统计字段排序='合计'

committran

return

error:

raiserror@errno@errmsg

rollbacktran

交叉表代码较长，拷贝后可在数据库（sqlserver）中直接执行使用。这个存储过程我在某项目中已经成功应用，不过从生成交叉表到用户界面展现还有很多代码工作量，本文只是抛砖引玉，不喜勿喷。