ホームページ >データベース >mysql チュートリアル >mysql 存储过程中游标临时表问题

mysql 存储过程中游标临时表问题

WBOY
WBOYオリジナル
2016-06-06 09:41:161174ブラウズ

mysql临时表游标

DELIMITER $$

USE laolao$$

DROP PROCEDURE IF EXISTS parent_sport_sort1$$

CREATE DEFINER=root@% PROCEDURE parent_sport_sort1(IN jidb VARCHAR(64),IN uname VARCHAR(64),IN starttime VARCHAR(64),IN endtime VARCHAR(64),
IN startmonth VARCHAR(64),IN endmonth VARCHAR(64),IN startday VARCHAR(64),
OUT totala INT,OUT ranking INT,OUT totalamonth INT,OUT rankmonth INT,OUT totaladay INT,OUT rankday INT)
BEGIN
DECLARE usname VARCHAR(64);

DECLARE done INT DEFAULT FALSE;

DECLARE cur_usname CURSOR FOR SELECT parentname FROM user_chilld WHERE childname=uname;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_usname;

read_loop: LOOP

FETCH cur_usname INTO usname;

IF done THEN
LEAVE read_loop;
END IF;
SET @mytemp = 0;
SELECT newid,stotal INTO ranking,totala FROM(
SELECT (@mytemp:=@mytemp+1) AS newid,stotal,username FROM (
SELECT SUM(total) stotal, username FROM exercise
WHERE ( username
IN(
SELECT username FROM ofRoster
WHERE jid=CONCAT(usname,jidb)
OR username =usname)
AND createtime BETWEEN starttime AND endtime
)GROUP BY username ORDER BY stotal DESC
)a
) a1 WHERE username=usname;

SET @mytemp = 0;
SELECT newid,stotal INTO rankmonth,totalamonth FROM(
SELECT (@mytemp:=@mytemp+1) AS newid,stotal,username FROM (
SELECT SUM(total) stotal, username FROM exercise
WHERE ( username
IN(
SELECT username FROM ofRoster
WHERE jid=CONCAT(usname,jidb)
OR username =usname)
AND createtime BETWEEN startmonth AND endmonth
)GROUP BY username ORDER BY stotal DESC
)a
) a1 WHERE username=usname;
SET @mytemp = 0;
SELECT newid,stotal INTO rankday,totaladay FROM(
SELECT (@mytemp:=@mytemp+1) AS newid,stotal,username FROM (
SELECT SUM(total) stotal, username FROM exercise
WHERE ( username
IN(
SELECT username FROM ofRoster
WHERE jid=CONCAT(usname,jidb)
OR username =usname)
AND createtime BETWEEN startday AND startday
)GROUP BY username ORDER BY stotal DESC
)a
) a1 WHERE username=usname;

END LOOP;

CLOSE cur_usname;
END$$

DELIMITER ;

现在的这个存储过程,只能根据游标最后一条数据,返回一行,
我现在需要的是DECLARE cur_usname CURSOR FOR SELECT parentname FROM user_chilld WHERE childname=uname; 这个游标返回的是多个,根据返回的多个参数来当做下面那三条主SQL 的条件。
我想的是需要建个临时表,游标等于1的时候返回1条数据存到临时表然后游标等于2的时候在返回一条也存到临时表游标等于3的时候返回一条存到临时表,

现在就是不知道这种情况临时表该怎么用。
困扰了两天了,求大神们狠狠的教育

声明:
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。