交叉表 mysql_交叉表-mysql教程-PHP中文网

交叉表 交叉表 CREATE PROCEDURE prCrosstab @chrRowHead char(30),/*表示列，在交叉表的结果中作为第一列出现*/ @chrColHead char(30),/*表示列，在交叉表的结果中该列中的数据被变换为新列名称*/ @chrvalue char(30),/*表示列，在该列中执行聚合函数*/ @ch

交叉表

交叉表

CREATE PROCEDURE prCrosstab

@chrRowHead char(30),/*表示列，在交叉表的结果中作为第一列出现*/

@chrColHead char(30),/*表示列，在交叉表的结果中该列中的数据被变换为新列名称*/

@chrvalue char(30),/*表示列，在该列中执行聚合函数*/

@chrSource char(30),/*源表或视图*/

@inyType tinyint=1,/*1-求和，2-平均值，3-最小值，4-最大值，5-计数*/

@inyGrouping tinyint=0/*1-工作日，2-年内的周数，3-月份，4-季度，5-年份*/

AS

/*过程变量*/

Declare

@chvRow varchar(255),

@chvCol varchar(255),

@chvVal varchar(255),

@chvType varchar(10),

@chvRowType varchar(10),

@chvColType varchar(255),

@chvTemp varchar(255),

@chvColTemp varchar(255),

@chvRowTemp varchar(255),

@intType int,

@intRowType int,

@intColType int,

@chvExec varchar(255),

@chvGroup varchar(255),

@fltTemp float,

@dtmTemp Datetime,

@insR smallint,

@intColumn int,

@intReturn int,

@intTemp int,

@intColNameLen int,

@intMaxRowHead int

Set NoCount On

/*检查数据源是否存在*/

if not Exists

(select * From sysobjects

where name=@chrSource and type in('v','u'))

Begin

Raiserror 51001 '数据源不存在'

Return -1

End

/*检查列是否存在*/

if not Exists

(select sc.name from syscolumns sc

join sysobjects so on sc.id=so.id

where so.name=@chrSource

and sc.name=@chrColHead)

Begin

Raiserror 51002 '无效 @chrColHead 名称'

Return -1

End

if not Exists

(select sc.name from syscolumns sc

join sysobjects so on sc.id=so.id

where so.name=@chrSource

and sc.name=@chrRowHead)

Begin

Raiserror 51002 '无效 @chrRowHead名称'

Return -1

End

if not Exists

(select sc.name from syscolumns sc

join sysobjects so on sc.id=so.id

where so.name=@chrSource

and sc.name=@chrvalue)

Begin

Raiserror 51002 '无效 @chrvalue 名称'

Return -1

End

/*检查聚合函数类型，是否是有效值*/

if @inyType<1 or @inyType>5

Begin

Raiserror 51000 '无效聚合函数类型'

Return -1

End

/*确定聚合函数类型*/

Select @chvType=

Case @inyType

when 1 then 'SUM'

when 2 then 'AVG'

when 3 then 'MAX'

when 4 then 'MIN'

when 5 then 'COUNT'

else 'SUM'

End

/*取得@chrvalue的数据类型*/

Select @chvTemp=t2.name

From sysobjects o

join syscolumns c on (o.id=c.id)

join systypes t1 on (t1.usertype=c.usertype)

join systypes t2 on (t1.type=t2.type)

where t2.usertype<100

and t2.usertype<>18

and t2.usertype<>80

and o.type in ('u','v')

and o.name=@chrSource

and c.name=@chrvalue

/*数据类型分类*/

Select @intTemp=

Case

when @chvTemp in ('int','smallint','tinyint','float','real','decimal','numeric','money','smallmoney') then 1

when @chvTemp in ('datetime','smalldatetime') then 3

when @chvTemp in ('bit','char','varchar') then 5

else 100

End

/*检查数据类型与聚合类型是否匹配*/

if @inyType5

Begin

Raiserror 51010 '无效的数据分组'

Return -1

End

/*取得@chrColHead列的合法数据类型*/

Select @chvTemp=t2.name

From sysobjects o

join syscolumns c on (o.id=c.id)

join systypes t1 on (t1.usertype=c.usertype)

join systypes t2 on (t1.type=t2.type)

Where t2.usertype<100

and t2.usertype<>18

and t2.usertype<>80

and o.type in ('u','v')

and o.name=@chrSource

and c.name=@chrColHead

if upper(@chvTemp) not in ('CHAR','VARCHAR')

Select @intColType=1

else

Select @intColType=0

/*取得@chrRowHead的合法数据类型*/

Select @chvRowType=t2.name

From sysobjects o

join syscolumns c on (o.id=c.id)

join systypes t1 on (t1.usertype=c.usertype)

join systypes t2 on (t1.type=t2.type)

Where t2.usertype<100

and t2.usertype<>18

and t2.usertype<>80

and o.type in ('u','v')

and o.name=@chrSource

and c.name=@chrRowHead

if upper(@chvRowTemp) not in ('CHAR','VARCHAR')

Select @intRowType=1

else

Select @intRowType=0

/*检查组分类类型*/

Select @intTemp=

Case

when @chvTemp in ('int','smallint','tinyint','float','real','decimal','numeric','money','smallmoney') then 1

when @chvTemp in ('datetime','smalldatetime') then 3

when @chvTemp in ('bit','char','varchar') then 5

else 100

End

/*验证数据类型与日期分组类型的一致性*/

/*将来可扩充成其他数据类型分组*/

if (@intTemp=5 and @inyGrouping>0) or (@intTemp=1 and @inyGrouping>0) or (@intTemp=3 and @inyGrouping=0)

Begin

Raiserror 51030 '分组数据与分组类型不一致'

Return -1

End

/*安全性检查*/

/*此部分以后完成

if user_id()<>1

Begin

if (Select Count(distinct c.name)

From syscolumns c,sysobjects o,sysprotects p,sysusers u,master..spt_values v

Where c.name in (@chrColHead,@chrRowHead,@chrvalue)

*/

/*定义临时表*/

Create Table #colNames(colname varchar(255),colnumber int Null)

Create Table #rownames(rowname varchar(255) null)

/*创建colnames表*/

Select @chvExec='insert #colnames select col1,col2 from'

+'(select distinct col1='+

case @intTemp

when 3 then

case

when @inyGrouping in (1,3) then 'datename('+

case @inyGrouping

when 1 then 'weekday'

when 3 then 'month'

end +','+RTrim(@chrColHead)+')'

else

Case @inyGrouping

when 2 then '''Week'

when 4 then '''quarth'

when 5 then '''year'

end+'_''+'+'datename('+

case @inyGrouping

when 2 then 'week'

when 4 then 'quarth'

when 5 then'year'

end+','+RTrim(@chrColHead)+')'

end

else

case @intColType

when 1 then 'convert(varchar(255),'+RTrim(@chrColHead)+')'

else RTrim(@chrColHead)

end

end+',col2='+

case @intTemp

when 3 then 'datepart('+

case @inyGrouping

when 1 then 'weekday'

when 2 then 'week'

when 3 then 'month'

when 4 then 'quarter'

when 5 then 'year'

end+','+Rtrim(@chrColHead)+')'

else '0'

end+',col3='+

case @intTemp

when 3 then 'datepart('+

case @inyGrouping

when 1 then 'weekday'

when 2 then 'week'

when 3 then 'month'

when 4 then 'quarter'

when 5 then 'year'

end+','+RTrim(@chrColHead)+')'

else RTrim(@chrColHead)

end+' from '+RTrim(@chrSource)+')xyz order by col3'

--Print @chvExec

Exec(@chvExec)

--select * from #ColNames

/*检查列计数值*/

if (select Count(*) from #colnames)>1023

begin

drop table #colnames

raiserror 51004 'Distinct column count exceeded max of 1023.'

return -1

end

/*检验名称长度*/

if (Select max(DataLength(Rtrim(colname))-1) from #colnames)>29

Begin

Drop Table #colnames

RaisError 51050 'Column data length exceeded max of 30.'

Return -1

End

/*填写RowNames表*/

Select @chvExec='insert #rownames select distinct '+

Case @intRowtype

when 1 then 'convert(varchar255),'

else ''

end+Rtrim(@chrRowHead)+

Case @intRowType

when 1 then ')'

else ''

End+' from '+@chrSource

--Print @chvExec

Exec(@chvExec)

/*创建和修改crosstable*/

Select @intMaxRowHead=

(Select Max(DataLength(RTrim(rowname))) from #rownames)

/*创建Crosstable*/

/*定义Crosstable的RowHead字段*/

Create Table #crosstable(Rowhead varchar(255) null)

/*在Crosstable中加入列*/

Declare colname_cursor2 cursor for

select colname from #colnames

open colname_cursor2

Fetch colname_cursor2 into @chvCol

while @@fetch_status>=0

Begin

Select @chvColTemp=''

if @chvCol Like '%[^A-Z0-9]%'

Begin

Select @insR=1

While @insr<=DataLength(RTrim(@chvCol))

Begin

Select @chvColTemp=Rtrim(@chvColtemp)+

Case

when substring(@chvCol,@insR,1) Like '[A-Z0-9_]'

then substring(@chvCol,@insR,1)

Else ''

End

Select @insR=@insr+1

end

Select @chvCol=@chvColTemp

End

Select @chvExec='alter table #crosstable add '+

Case

when substring(@chvCol,1,1) Like '[^1234567890]' then @chvCol

else '_'+LTrim(@chvCol)

End

+' '+@chvColType+' null default (0)'

--Print @chvExec

Exec(@chvExec)

Fetch colname_cursor2 into @chvCol

End

Close colname_cursor2

Deallocate colname_cursor2

/*加入初始Crosstable数据*/

Select @chvExec='insert #crosstable(rowhead) select rowname from #rownames'

--Print @chvExec

Exec(@chvExec)

/*使用游标填写crosstable的剩余部分*/

/*创建游标*/

Select @chvExec='declare colname_cursor3 cursor for select '+

Case @intRowType

when 1 then 'convert(varchar(255),'+RTrim(@chrRowHead)+')'

Else RTrim(@chrRowHead)

End+','+

Case

when @intTemp=3 then

case

when @inyGrouping in (1,3) then 'Datename('+

case @inyGrouping

when 1 then 'weekday'

when 3 then 'month'

end+','+RTrim(@chrColHead)+')'

else

case @inyGrouping

when 2 then '''Week'

when 4 then '''Quarth'

when 5 then '''Year'

End+'_''+'+'datename('+

case @inyGrouping

when 2 then 'week'

when 4 then 'quarth'

when 5 then 'year'

end+','+RTrim(@chrColHead)+')'

End

Else

Case @intColType

When 1 then 'convert(varchar(255),'+RTrim(@chrColHead)+')'

Else RTrim(@chrColHead)

End

End+',total=Convert(varchar(255),'+RTrim(@chvType)+'('+RTrim(@chrvalue)+')) from '+

RTrim(@chrSource)+' group by '+RTRim(@chrRowHead)+','+

Case @intTemp

when 3 then

case

when @inyGrouping in (1,3) then 'Datename('+

case @inyGrouping

when 1 then 'weekday'

when 3 then 'month'

end+','+RTrim(@chrColHead)+')'

else

case @inyGrouping

when 2 then '''Week'

when 4 then '''Quarth'

when 5 then '''Year'

end+'_''+'+'datename('+

case @inyGrouping

when 2 then 'week'

when 4 then 'quarter'

when 5 then 'year'

end+','+RTrim(@chrColHead)+')'

end

else Rtrim(@chrColHead)

End

--Print @chvExec

Exec(@chvExec)

/*更新Crosstable表*/

Begin Tran

Open colname_cursor3

Fetch colname_cursor3 into @chvRow,@chvCol,@chvVal

while @@fetch_status>=0

Begin

Select @chvColTemp=''

if @chvCol Like '%[^A-Z0-9]%'

Begin

Select @insR=1

While @insR<=DataLength(RTRim(@chvCol))

Begin

Select @chvColTemp=RTRim(@chvColTemp)+

Case

When Substring(@chvCol,@insR,1) Like '[A-Z0-9_]' then Substring(@chvCol,@insR,1)

Else ' '

End

Select @insR=@insR+1

End

Select @chvCol=@chvColTemp

End

Select @chvExec='update #crosstable set '+

Case

when substring(@chvCol,1,1) Like '[^1234567890]' then @chvCol

Else '_'+LTrim(@chvCol)

End+'='+

Case

when @chvVal is Null then '0'

Else RTrim(@chvVal)

End+' where Rowhead='''+RTRim(@chvRow)

Select @chvRow=

Case

When @chvRow is Null Then 'NULL'

Else RTrim(@chvRow)

End

Select @chvRowTemp=''

if @chvRow Like'%'

Begin

Select @insR=1

While @insR<=DataLength(RTrim(@chvRowTemp))-1

Begin

Select @chvRowTemp=RTrim(@chvRowTemp)+

Case

When Substring(@chvRow,@insR,1) Like '[^'']' then Substring(@chvRow,@insR,1)

Else ' '' '' '

End

Select @insR=@insR+1

End

End

Select @chvRow=@chvRowTemp

Select @chvExec=@chvExec+@chvRow+''''

--Print @chvExec

Exec(@chvExec)

Fetch colname_cursor3 into @chvRow,@chvCol,@chvVal

End

Close colname_cursor3

Deallocate colname_cursor3

Commit Tran

Set NoCount off

Select @chvExec='Select * from #crosstable'

--Print @chvExec

Exec(@chvExec)

Drop Table #colnames

Drop Table #rownames

Drop Table #crosstable

本文原创发布php中文网，转载请注明出处，感谢您的尊重！