请教一下mysql5.7 嵌套游标循环问题?

我想请教一下mysql5.7 ,使用嵌套游标,但是他显示多循环了1次的结果...求解
这是Mysql 5.7.19 版本,下面的是创建游标的语句

CREATE PROCEDURE StatisticStore3()
BEGIN
declare _n varchar(20);
declare done int default false;
declare cur cursor for select name from store group by name;
declare continue HANDLER for not found set done = true;
open cur;
read_loop:loop
fetch cur into _n;
if done then
leave read_loop;
end if;
begin
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'iphone';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
iphone_loop:loop
fetch cur into n,c;
if done then
leave iphone_loop;
end if;
set total = total + c;
end loop;
close cur;
select _n,n,total;
end;
begin
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'android';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
android_loop:loop
fetch cur into n,c;
if done then
leave android_loop;
end if;
set total = total + c;
end loop;
close cur;
select _n,n,total;
end;
begin

 end;
 end loop;
 close cur;

END;//

下面这里调用这个存储过程的游标
mysql> call StatisticStore3()//
+---------+--------+-------+
| _n | n | total |
+---------+--------+-------+
| android | iphone | 47 |
+---------+--------+-------+
1 row in set (0.01 sec)

+---------+---------+-------+
| _n | n | total |
+---------+---------+-------+
| android | android | 33 |
+---------+---------+-------+
1 row in set (0.01 sec)

+--------+--------+-------+
| _n | n | total |
+--------+--------+-------+
| iphone | iphone | 47 |
+--------+--------+-------+
1 row in set (0.01 sec)

+--------+---------+-------+
| _n | n | total |
+--------+---------+-------+
| iphone | android | 33 |
+--------+---------+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

这是创建游标的select 语句
mysql> select name from store group by name//
+---------+
| name |
+---------+
| android |
| iphone |
+---------+
2 rows in set (0.00 sec)

需求实际输出是希望如下
mysql> select name,sum(count) sum from store group by name//
+---------+------+
| name | sum |
+---------+------+
| android | 33 |
| iphone | 47 |
+---------+------+
2 rows in set (0.00 sec)

1回答

韩成亮韩成亮  数据库架构师 , 某金融
yinxin赞同了此回答
逻辑有点问题 2*2把 select _n,n,total;修改下 if _n=nthen select _n,n,total;end if;显示全部

逻辑有点问题 2*2
把 select _n,n,total;修改下
if _n=n
then
select _n,n,total;
end if;

收起
 2019-06-10

提问者

ktcloud系统工程师, SYSTEX Rainbow Tech inc.

问题状态

  • 发布时间:2019-06-06
  • 关注会员:1 人
  • 问题浏览:551
  • 最近回答:2019-06-10
  • 关于TWT  使用指南  社区专家合作  厂商入驻社区  企业招聘  投诉建议  版权与免责声明  联系我们
    © 2019  talkwithtrend — talk with trend,talk with technologist 京ICP备09031017号-30