The cursor and temporary table are used in the mysql stored procedure, and the returned temporary table data is inaccurate.

DELIMITER $ USE 'laolao' $ dropprocedureifexists' parent _ sport_sort1 '$ CREATEDEFINER 'root' @' % 'procedure 'parent _ sport_sort1' (INjidbVARCHAR (64 ), INunameVARCHAR (64), INstarttimeVARCHAR (64), INendtimeVARCHAR (64), INstartmonthVARCHAR (64... mysql cursor stored procedure temporary database table

DELIMITER $

USElaolao$

DROP PROCEDURE IF EXISTSparent_sport_sort1$

Create definer =root@%PROCEDUREparent_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; create the cursor 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: LOOPFETCH 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;

This is the requirement.
A child account is bound to multiple elderly accounts. then a child needs to find the monthly sports data, daily sports data, and daily sports data of all elderly people. The exercise data includes the ranking of the elderly in friends and the number of exercise steps.

Now the above stored procedure has basically completed this function, but there is a bug, that is, if the children are bound to two elderly people, one of them has no data today, and the other has data, after the stored procedure is executed, only one data record is returned. if both old people have data today, it will be displayed as normal.