insert into packcollectionrank(Pack_ID) select distinct Pack_ID from products ;-- 插入所有Pack_ID到统计表packcollectionrank</P>
<P> select MAX(ID) into Pmax from packcollectionrank;
select MIN(ID) into Pmin from packcollectionrank;</P>
<P> while Pmin<=Pmax do-- 插入所有Pack_ID的人气,没订阅默认为0
select Pack_ID into PID from tdxtb_packcollectionrank where ID=Pmin;</P>
<P> 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; </P>
<P> set Pmin=Pmin+1;
end while;
-- 插入
END
</P>
复制代码
本机执行:时间: 0.250ms
Procedure executed successfully
受影响的行: 1
换机子执行:
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;
MYSQL