56,677
社区成员
发帖
与我相关
我的任务
分享
create table pro(product int,price int,`time` date);
delete from pro;
insert into pro values
(1 ,0, '2010-01-01'),
(1 ,1 ,'2010-01-02'),
(1 ,2 ,'2010-01-03'),
(1 ,30 ,'2010-01-04'),
(1 ,20 ,'2010-01-05'),
(1 ,10 ,'2010-01-06'),
(1 ,8 ,'2010-01-07'),
(2 ,6 ,'2010-01-01'),
(2 ,4 ,'2010-01-02'),
(2 ,5 ,'2010-01-03'),
(2 ,3 ,'2010-01-05'),
(3 ,0 ,'2010-01-01'),
(4 ,3 ,'2010-01-01'),
(5 ,50 ,'2010-01-01'),
(5, 20 ,'2010-01-02'),
(5, 10 ,'2010-01-03'),
(5, 9 , '2010-01-04')
drop procedure if exists get_price_down;
create procedure get_price_down()
begin
DECLARE done INT DEFAULT 0; #游标的标志位
DECLARE vproduct int;
DECLARE vprice int;
DECLARE vtime date;
# DECLARE cur1 CURSOR FOR SELECT product,price,`time` FROM pro ;# where price>0 ;
DECLARE cur1 CURSOR FOR SELECT product,price,`time` FROM pro where price>0 order by product , `time` desc ,price desc ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
create TEMPORARY table tmp(product int,price int,`time` date);
OPEN cur1;
REPEAT
FETCH cur1 INTO vproduct,vprice,vtime;
IF NOT done THEN
if exists(select 1 from pro where product=vproduct and `time`<vtime and price>vprice) then # and not exists(select 1 from pro where product=vproduct and `time`>vtime and price<vprice) then
if (select count(*) from pro where product=vproduct)=2 then
insert into tmp select * from pro where product=vproduct order by price desc;
else
if (select count(*) from tmp where product=vproduct) <2 then
insert into tmp values(vproduct,vprice,vtime);
end if;
end if;
end if;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
select * from tmp order by product,price desc limit 20;
drop table tmp;
end;
mysql> call get_price_down();
+---------+-------+------------+
| product | price | time |
+---------+-------+------------+
| 1 | 10 | 2010-01-06 |
| 1 | 8 | 2010-01-07 |
| 2 | 5 | 2010-01-03 |
| 2 | 3 | 2010-01-05 |
| 5 | 10 | 2010-01-03 |
| 5 | 9 | 2010-01-04 |
+---------+-------+------------+
6 rows in set (0.45 sec)
Query OK, 0 rows affected (0.50 sec)
mysql>
mysql> select * from pro;
+---------+-------+------------+
| product | price | time |
+---------+-------+------------+
| 1 | 0 | 2010-01-01 |
| 1 | 1 | 2010-01-02 |
| 1 | 2 | 2010-01-03 |
| 1 | 3 | 2010-01-04 |
| 1 | 2 | 2010-01-05 |
| 1 | 1 | 2010-01-06 |
| 1 | 0 | 2010-01-07 |
| 2 | 2 | 2010-01-01 |
| 2 | 4 | 2010-01-02 |
| 3 | 0 | 2010-01-01 |
| 4 | 1 | 2010-01-01 |
| 5 | 1 | 2010-01-01 |
| 5 | 2 | 2010-01-02 |
+---------+-------+------------+
13 rows in set (0.00 sec)
mysql>
mysql> select product,atime,btime ,aprice,bprice
-> from (
-> select a.product,a.time as atime,b.time as btime ,a.price as aprice,b.price as bprice
-> from pro a ,pro b
-> where a.product=b.product and a.time>b.time
-> order by a.product,a.time desc,b.time desc
-> ) t
-> where aprice<bprice
-> group by product
-> order by atime desc limit 10;
+---------+------------+------------+--------+--------+
| product | atime | btime | aprice | bprice |
+---------+------------+------------+--------+--------+
| 1 | 2010-01-07 | 2010-01-06 | 0 | 1 |
+---------+------------+------------+--------+--------+
1 row in set (0.00 sec)
mysql>
select a.product,a.time,b.time ,a.price,b.price
from (
select a.product,a.time,b.time ,a.price,b.price
from 一个价格表 a ,一个价格表 b
where a.product=b.product and a.time>b.time
order by a.product,a.time desc,b.time desc
) t
where a.price<b.price
and a.price>0
group by a.product
select a.product,a.time,b.time ,a.price,b.price
from (
select a.product,a.time,b.time ,a.price,b.price
from 一个价格表 a ,一个价格表 b
where a.product=b.product and a.time>b.time
order by a.product,a.time desc,b.time desc
) t
where a.price<b.price
group by a.product