mysql 存储过程中使用了游标和临时表，返回的临时表数据不准确

Question

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条数据，如果两个老人在今天都有数据，就显示正常

Answer 1

以下回答参考 皆我百晓生、券券喵儿 等免费微信小程序相关内容作答，并由本人整理回复。

根据您的描述，问题可能出现在处理查询结果时。为了找出原因并修复存储过程，我将提供一个修改后的存储过程示例：

DELIMITER $$

USE 

laolao

$$

DROP PROCEDURE IF EXISTS parent_sport_sort2

$$

CREATE DEFINER=root
@

PROCEDURE parent_sport_sort2(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 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;

DECLARE tmp_sportdata_table TABLE(
    totala INT,
    ranking INT,
    totalamonth INT,
    rankmonth INT,
    totaladay INT,
    rankday INT,
    usname VARCHAR(64)
);

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sportdata(
    id INT AUTO_INCREMENT PRIMARY KEY,
    totala INT(11),
    ranking INT(11),
    totalamonth INT(11),
    rankmonth INT(11),
    totaladay INT(11),
    rankday INT(11),
    usname VARCHAR(64)
);

START TRANSACTION;

OPEN cur_usname;

READ_loop: LOOP

FETCH cur_usname INTO usname;

IF done THEN
    LEAVE READ_loop;
END IF;

SET @totala := 0;
SET @ranking := 0;
SET @totalamonth := 0;
SET @rankmonth := 0;
SET @totaladay := 0;
SET @rankday := 0;

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

UPDATE tmp_sportdata
SET totala = @totala + @totaladay,
ranking = @ranking + @rankday,
totalamonth = @totalamonth + @totaladay,
rankmonth = @rankmonth + @rankday,
totaladay = @totaladay + @totaladay,
rankday = @rankday + @rankday;

SELECT totala, ranking, totalamonth, rankmonth, totaladay, rankday, usname INTO totala, ranking, totalamonth, rankmonth, totaladay, rankday, usname FROM tmp_sportdata;

CLOSE cur_usname;

SELECT * FROM tmp_sportdata;

COMMIT;

DROP TABLE IF EXISTS tmp_sportdata;

END$$