BEGIN
DECLARE Pmax INT; -- 记录统计表中最大ID
DECLARE Pmin INT; -- 记录统计表中最小ID
DECLARE PID SMALLINT(6);-- 记录当前统计Pack_ID
DELETE FROM packcollectionrank;-- 清空表
INSERT INTO packcollectionrank(Pack_ID) SELECT DISTINCT Pack_ID FROM products ;-- 插入所有Pack_ID到统计表packcollectionrank SELECT MAX(ID) INTO Pmax FROM packcollectionrank;
SELECT MIN(ID) INTO Pmin FROM packcollectionrank; WHILE Pmin<=Pmax DO-- 插入所有Pack_ID的人气,没订阅默认为0
SELECT Pack_ID INTO PID FROM packcollectionrank WHERE ID=Pmin; SET @sql1=CONCAT("UPDATE packcollectionrank SET Collection_count=(SELECT COUNT(*) FROM userservicerelation WHERE Pack_ID=",PID," AND Relation_Status=6) WHERE Pack_ID=",PID,";");
PREPARE rankpack1 FROM @sql1;
EXECUTE rankpack1; SET Pmin=Pmin+1;
END WHILE;
-- 插入
END大神们看下哪有问题吧!!自己机子上执行好好的,到别人机子上一直报错:[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare Pmax int;
我用的是Navicat for MySQL对方用的是mysql-pront.
解决方案 »
DELIMITER $$
CREATE PROCEDURE ff1()
BEGIN
DECLARE Pmax INT; -- 记录统计表中最大ID
DECLARE Pmin INT; -- 记录统计表中最小ID
DECLARE PID SMALLINT(6);-- 记录当前统计Pack_ID
DELETE FROM packcollectionrank;-- 清空表
INSERT INTO packcollectionrank(Pack_ID) SELECT DISTINCT Pack_ID FROM products ;-- 插入所有Pack_ID到统计表packcollectionrank SELECT MAX(ID) INTO Pmax FROM packcollectionrank;
SELECT MIN(ID) INTO Pmin FROM packcollectionrank; WHILE Pmin<=Pmax DO-- 插入所有Pack_ID的人气,没订阅默认为0
SELECT Pack_ID INTO PID FROM packcollectionrank WHERE ID=Pmin; SET @sql1=CONCAT("UPDATE packcollectionrank SET Collection_count=(SELECT COUNT(*) FROM userservicerelation WHERE Pack_ID=",PID," AND Relation_Status=6) WHERE Pack_ID=",PID,";");
PREPARE rankpack1 FROM @sql1;
EXECUTE rankpack1; SET Pmin=Pmin+1;
END WHILE;
-- 插入
END $$
DELIMITER ;检查一下MYSQL版本
--转自