我想请教一下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)