56,679
社区成员
发帖
与我相关
我的任务
分享
create table t
(
code varchar(10),
price int
);
insert t select
'a',2 union all select
'a',3 union all select
'a',4 union all select
'a',5 union all select
'a',9 union all select
'a',8 union all select
'b',2 union all select
'b',3 union all select
'b',1 union all select
'b',5 union all select
'b',6 union all select
'b',0 union all select
'c',2 union all select
'c',1 ;
delimiter $$
create procedure sCode ()
begin
declare icode varchar(10);
declare cur_sCode cursor for select distinct code from t ;
declare exit HANDLER for not found close cur_sCode;
open cur_sCode ;
repeat
fetch cur_sCode into icode;
select * from t where code=icode order by price limit 5;
until 0 end repeat;
close cur_sCode;
end;
$$
delimiter ;
mysql> call sCode();
+------+-------+
| code | price |
+------+-------+
| a | 2 |
| a | 3 |
| a | 4 |
| a | 5 |
| a | 8 |
+------+-------+
5 rows in set (0.04 sec)
+------+-------+
| code | price |
+------+-------+
| b | 0 |
| b | 1 |
| b | 2 |
| b | 3 |
| b | 5 |
+------+-------+
5 rows in set (0.12 sec)
+------+-------+
| code | price |
+------+-------+
| c | 1 |
| c | 2 |
+------+-------+
2 rows in set (0.18 sec)