问题描述

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,OUT usname VARCHAR(64))
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;

创建临时表
 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sportdata(
    totala INT(11),
    ranking INT(11),
    totalamonth INT(11),
    rankmonth INT(11),
    totaladay INT(11),
    rankday INT(11),
    usname VARCHAR(64)
);

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,usname INTO ranking,totala,usname 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;

    INSERT INTO tmp_sportdata VALUES(totala,ranking,totalamonth,rankmonth,totaladay,rankday,usname);

    END LOOP;

    CLOSE cur_usname;

    SELECT * FROM tmp_sportdata;

    DROP TABLE IF EXISTS tmp_sportdata;
END$$

DELIMITER ;

需求是这样的
一个儿女账号，绑定多个老人账号，然后一个儿女要查出绑定所有老人的每月的运动数据，每天的运动数据，当天的运动数据。其中运动数据中包括老人在好友中的排名，和运动步数

现在上面的存储过程基本上已经完成了这个功能，但是出现了bug，就是如果儿女绑定了两个老人，其中一个老人在今天没数据，另一个老人有数据的话，这个存储过程执行之后返回只有1条数据，如果两个老人在今天都有数据，就显示正常