mysql 交叉表 存储过程_交叉表存储过程 结果集与新的表连接? 问题

CREATEprocp_fyjcb/*动态生成交叉表*/@qs_rqnvarchar(10),@zz_rqnvarchar(10),@TableNamesysname,--表名@Yznamesysname,--交叉表最左面的列(分组列)@Xznamesysname,--交叉表最上面的列...

CREATE proc p_fyjcb

/*

动态生成交叉表

*/

@qs_rq nvarchar(10),

@zz_rq nvarchar(10),

@TableName sysname, --表名

@Yzname sysname, --交叉表最左面的列(分组列)

@Xzname sysname, --交叉表最上面的列(变成横项的数据列)

@qsum sysname, --交叉表的数数据字段(求和列)

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

@ynysum bit, --为1时在交叉表纵向最下边加纵向合计

@t_tab1 sysname,

@t_tab2 sysname

as

-- 判断数据表是否存在

if exists (select * from sysobjects where id = object_id(N'[dbo].['+@t_tab1+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Execute('Drop table '+@t_tab1)

if exists (select * from sysobjects where id = object_id(N'[dbo].['+@t_tab2+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Execute('Drop table '+@t_tab2)

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

--生成交叉表处理语句

set @s='

set @s=''''

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

+'] when ''''''+cast(['+@Xzname+'] as varchar)+'''''' then ['+@qsum+'] else 0 end)''

from ['+@TableName+']

where (convert(varchar(10),开单日期,120) between ''' +@qs_rq+ ''' and ''' +@zz_rq + ''')

group by ['+@Xzname+'] order by [' + @Xzname + ']'

exec sp_executesql @s

,N'@s varchar(8000) out'

,@sql out

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

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

select @sum1=case @ynxsum

when 1 then ',[合计]=sum(['+@qsum+'])'

else '' end

,@sum2=case @ynysum

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

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

+@Yzname+'] as varchar) end'

else '['+@Yzname+']' end

,@sum3=case @ynysum

when 1 then ' with rollup'

else '' end

,@sum4= case @ynxsum

when 1 then ',出车次数 =0 '

else '' end

--生成交叉表

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

into '+@t_tab1+' from ['+@TableName+']

where (convert(varchar(10),开单日期,120) between ''' +@qs_rq+ ''' and ''' +@zz_rq + ''')

group by ['+@Yzname+']'+@sum3)

exec('SELECT CASE WHEN (GROUPING(t_fygl.车辆编号) = 1) THEN ''合计''

ELSE ISNULL(车辆编号, ''UNKNOWN'')

END AS 车辆编号,

count( 单据编号) AS 出车次数

into ' + @t_tab2 +' FROM t_chucdgl

where (convert(varchar(10),开单日期,120) between ''' +@qs_rq+ ''' and ''' +@zz_rq + ''')

GROUP BY 车辆编号 WITH CUBE')

GO

我想把这两个 exec的经果 更好的连接在一起,但有可能左边比右边多,也可能右边比左边多! 这是两个表中的数据 能不能在一个过程中结决这个问题?

展开