请教一下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()//
_nntotal
androidiphone47

1 row in set (0.01 sec)

_nntotal
androidandroid33

1 row in set (0.01 sec)

_nntotal
iphoneiphone47

1 row in set (0.01 sec)

_nntotal
iphoneandroid33

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//
namesum
android33
iphone47

2 rows in set (0.00 sec)

参与3

1同行回答

renou2012renou2012  数据库管理员 , KE
逻辑有点问题 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
浏览1665

提问者

ktcloud
系统工程师SYSTEX Rainbow Tech inc.
擅长领域: 存储服务器存储虚拟化

相关问题

相关资料

相关文章

问题状态

  • 发布时间:2019-06-06
  • 关注会员:1 人
  • 问题浏览:2146
  • 最近回答:2019-06-10
  • X社区推广