数据库|mysql教程
mysql
临时表
游标
数据库-mysql教程
mysql临时表游标
最新合买彩票源码,vscode如何完全卸载,ubuntu终端注释,运行tomcat怎么配置,爬虫算了,php开源产品,北京seo收费贵吗,java 网站源码下载,蝉知模板制作lzw
DELIMITER $$
彩虹秒赞监控平台源码,vscode是什么啊,ubuntu 开ssh,tomcat怎么设置源码,严打爬虫,php security,seo优化谁靠谱,卖手机网站模板,个人集团网站模板lzw
USElaolao
$$
诱惑源码视频,ubuntu迁移根目录,爬虫c源代码,php 重载->,seo发文标准lzw
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)
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的时候返回一条存到临时表,
现在就是不知道这种情况临时表该怎么用。
困扰了两天了,求大神们狠狠的教育