改这个太累了,不想改, 1.建立临时表循环插入数据, select convert(varchar(200),123) as scol ,convert(varchar(100),123) as serial into #tmp from tbDivision 2.游标操作不一致须修改 WHILE @@FETCH_STATUS = 0 BEGIN 3.IF语法,结束符不同 if 。。then end if;delimiter //
delimter ;
begin declare Productno varchar(100); declare s varchar(8000); declare s_sum varchar(8000); declare Cursori int; declare SQL1 varchar(5000); declare SQL3 varchar(8000); declare SQL2 varchar(12000) ; -- 声明游标 DECLARE MyCursor CURSOR FOR select scol from tmp; -- 声明游标结束变量 DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cursori=1; -- 删除临时表 DROP table IF EXISTS tmp; -- 创建临时表 CREATE TEMPORARY TABLE tmp ( scol VARCHAR(100) , serials VARCHAR(100) ); set SQL1=concat('select ',inRow, ' as srow,',col,' as scol ,','SUM(',sum,') AS Qty ,',OrderBy,' as serials ', SQL_Condition) ; set sql3=concat(' insert into tmp select distinct scol,serials from (',SQL1,' ) as pp order by Serials'); -- 动态SQL变量声明 SET @s1 =SQL3; PREPARE stmt2 FROM @s1 ; -- 将数据写入临时表,用于计算列的个数 EXECUTE stmt2 ; deallocate PREPARE stmt2; SET Cursori=0; -- 设置游标变量初始值 OPEN MyCursor ; -- 打开游标 FETCH MyCursor INTO Productno ; if ( Cursori<>1 ) then if (Productno is not null) then set s=concat(' sum(case p.scol when '', (productno),' ' then p.Qty else 0 end ) as Q', (productno)); set s_sum=concat(' P1.Q', (productno)); end if; end if ; -- 组装交叉表查询字符串 while ( Cursori<>1 ) do FETCH MyCursor INTO Productno ; if (Productno is not null) then set s=concat(s,' ,sum(case p.scol when '' , (productno),'' then p.Qty else 0 end ) as Q', (productno)); set s_sum=concat (s_sum,' + P1.Q', (productno)); end if; set Productno=null; end while; CLOSE MyCursor; -- 关闭游标 set SQL2=concat(' SELECT P1.*, (',s_sum,') AS YearTotal FROM (SELECT P.srow,',s, ' FROM (',SQL1,') P GROUP BY P.srow) P1 order by p1.srow ') ; -- 执行查询 set @s2=SQL2; PREPARE stmt3 FROM @s2 ; EXECUTE stmt3 ; deallocate PREPARE stmt3; -- 删除临时表 drop table tmp; end 问题解决,晚贴上来了
1.建立临时表循环插入数据,
select convert(varchar(200),123) as scol ,convert(varchar(100),123) as serial
into #tmp
from tbDivision
2.游标操作不一致须修改
WHILE @@FETCH_STATUS = 0
BEGIN
3.IF语法,结束符不同
if 。。then
end if;delimiter //
delimter ;
declare s varchar(8000);
declare s_sum varchar(8000);
declare Cursori int;
declare SQL1 varchar(5000);
declare SQL3 varchar(8000);
declare SQL2 varchar(12000) ; -- 声明游标
DECLARE MyCursor CURSOR FOR select scol from tmp; -- 声明游标结束变量
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cursori=1; -- 删除临时表
DROP table IF EXISTS tmp;
-- 创建临时表
CREATE TEMPORARY TABLE tmp (
scol VARCHAR(100) ,
serials VARCHAR(100)
);
set SQL1=concat('select ',inRow, ' as srow,',col,' as scol ,','SUM(',sum,') AS Qty ,',OrderBy,' as serials ', SQL_Condition) ; set sql3=concat(' insert into tmp select distinct scol,serials from (',SQL1,' ) as pp order by Serials'); -- 动态SQL变量声明
SET @s1 =SQL3; PREPARE stmt2 FROM @s1 ;
-- 将数据写入临时表,用于计算列的个数
EXECUTE stmt2 ; deallocate PREPARE stmt2; SET Cursori=0;
-- 设置游标变量初始值
OPEN MyCursor ;
-- 打开游标
FETCH MyCursor INTO Productno ;
if ( Cursori<>1 ) then
if (Productno is not null) then
set s=concat(' sum(case p.scol when '', (productno),' ' then p.Qty else 0 end ) as Q', (productno));
set s_sum=concat(' P1.Q', (productno));
end if;
end if ;
-- 组装交叉表查询字符串
while ( Cursori<>1 ) do FETCH MyCursor INTO Productno ;
if (Productno is not null) then
set s=concat(s,' ,sum(case p.scol when '' , (productno),'' then p.Qty else 0 end ) as Q', (productno));
set s_sum=concat (s_sum,' + P1.Q', (productno));
end if;
set Productno=null;
end while;
CLOSE MyCursor;
-- 关闭游标 set SQL2=concat(' SELECT P1.*, (',s_sum,') AS YearTotal FROM (SELECT P.srow,',s, ' FROM (',SQL1,') P GROUP BY P.srow) P1 order by p1.srow ') ;
-- 执行查询
set @s2=SQL2; PREPARE stmt3 FROM @s2 ; EXECUTE stmt3 ; deallocate PREPARE stmt3;
-- 删除临时表
drop table tmp;
end
问题解决,晚贴上来了