三分钟内是什么意思啊? 1、与当前时间相比的三分钟内? 2、每间隔3分钟内,即0-3,3-6,6-9,……,57-60。 3、与colB的值动态偏移的3分钟内。这种情况估计是很费力气。估计楼内要的也是这个吧。以下是针对第三种情况的test.sql文件内容,已经过测试 ————————————————————create database testdb; use testdb;create table testtable (cola varchar(2),colb datetime);create index a on testtable(cola,colb);insert into testtable values ('A','2009-06-15 10:58:23'), ('B','2009-06-15 10:58:31'), ('C','2009-06-15 10:53:36'), ('C','2009-06-15 10:54:36'), ('C','2009-06-15 10:55:36'), ('C','2009-06-15 10:56:36'), ('C','2009-06-15 10:57:36'), ('C','2009-06-15 10:58:36'), ('C','2009-06-15 10:59:36'), ('A','2009-06-15 10:58:39'), ('A','2009-06-15 10:59:23'), ('B','2009-06-15 11:00:22'), ('B','2009-06-15 10:55:31'), ('B','2009-06-15 10:59:31'), ('B','2009-06-15 11:03:31'), ('B','2009-06-15 11:04:31'), ('A','2009-06-15 11:04:25'), ('A','2009-06-15 11:05:25'), ('A','2009-06-15 11:06:25'); select * from testtable;#显示一下样本数据 set @t=DATE('1900-01-01 00:00:00');#设置一个比表中任何一个时间都小的日期 set @a=""; #以下是记录合并查询语句 select cola,colb,(@t:=if(DATE_SUB(colb,INTERVAL 3 MINUTE)>=@t || cola!=@a,colb,@t)) as mergetime,(@a:=cola) from testtable group by cola,mergetime;
mysql> select * from tx; +------+---------------------+ | ColA | ColB | +------+---------------------+ | A | 2009-06-15 10:58:23 | | B | 2009-06-15 10:58:31 | | C | 2009-06-15 10:58:36 | | A | 2009-06-15 10:58:39 | | A | 2009-06-15 10:59:23 | | B | 2009-06-15 11:00:22 | | A | 2009-06-15 11:00:25 | +------+---------------------+ 7 rows in set (0.00 sec)mysql> mysql> delimiter // mysql> mysql> CREATE PROCEDURE simpleproc (itvl INT) -> BEGIN -> DECLARE a,a1 CHAR default ''; -> DECLARE b,b1 Datetime default '1900-01-01 00:00:00'; -> DECLARE c INT DEFAULT 0; -> -> DECLARE done INT DEFAULT 0; -> DECLARE cur1 CURSOR FOR SELECT ColA,ColB FROM tx order by ColA,ColB; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -> -> -> OPEN cur1; -> -> delete from ty; -> -> REPEAT -> FETCH cur1 INTO a, b; -> IF a != a1 or MINUTE(TIMEDIFF(b,b1))>itvl THEN -> insert into ty values (a,b); -> set a1 = a; -> set b1 = b; -> set c = 0; -> ELSE -> if a='A' and c=0 then -> insert into ty values (a,b); -> set c = 1; -> end if; -> END IF; -> -> UNTIL done END REPEAT; -> -> END; -> // Query OK, 0 rows affected (0.01 sec)mysql> mysql> delimiter ; mysql> mysql> CALL simpleproc(3); Query OK, 1 row affected (0.13 sec)mysql> select * from ty; +------+---------------------+ | ColA | ColB | +------+---------------------+ | A | 2009-06-15 10:58:23 | | A | 2009-06-15 10:58:39 | | B | 2009-06-15 10:58:31 | | C | 2009-06-15 10:58:36 | +------+---------------------+ 4 rows in set (0.00 sec)mysql>
忘了要求逢A取2,修改如下:create database testdb; use testdb;create table testtable (cola varchar(2),colb datetime);create index a on testtable(cola,colb);insert into testtable values ('A','2009-06-15 10:58:23'), ('B','2009-06-15 10:58:31'), ('C','2009-06-15 10:53:36'), ('C','2009-06-15 10:54:36'), ('C','2009-06-15 10:55:36'), ('C','2009-06-15 10:56:36'), ('C','2009-06-15 10:57:36'), ('C','2009-06-15 10:58:36'), ('C','2009-06-15 10:59:36'), ('A','2009-06-15 10:58:39'), ('A','2009-06-15 10:59:23'), ('B','2009-06-15 11:00:22'), ('B','2009-06-15 10:55:31'), ('B','2009-06-15 10:59:31'), ('B','2009-06-15 11:03:31'), ('B','2009-06-15 11:04:31'), ('A','2009-06-15 11:04:25'), ('A','2009-06-15 11:05:25'), ('A','2009-06-15 11:06:25'); select * from testtable;#显示一下样本数据 set @t=DATE('1900-01-01 00:00:00');#设置一个比表中任何一个时间都小的日期 set @a=""; set @Anum=0; #以下是记录合并查询语句 select cola,colb,(@Anum:=if(DATE_SUB(colb,INTERVAL 3 MINUTE)<=@t && cola='A',@Anum+1,0)) as Anum,(@isa:=if(cola='A' && @Anum<2,true,false)) as isa,(@t:=if(DATE_SUB(colb,INTERVAL 3 MINUTE)>=@t || cola!=@a || @isa,colb,@t)) as mergetime,(@a:=cola) from testtable group by cola,mergetime;drop table testtable; drop database testdb;
SELECT A1.cola,A1.colb FROM ( select A.*,timediff(a.colb,b.colb) from tta2 a left join tta2 b on a.colb + INTERVAL 3 minute =b.colb and a.cola=b.cola WHERE B.cola IS NULL) A1 LEFT JOIN ( select A.*,timediff(a.colb,b.colb) from tta2 a left join tta2 b on a.colb + INTERVAL 3 minute =b.colb and a.cola=b.cola WHERE B.cola IS NULL) A2ON A1.cola=A2.cola AND A1.COLB>=A2.COLB GROUP BY A1.cola,A1.colb HAVING(COUNT(A2.COLA))<=IF(A1.COLA='A',2,1)
三分钟是参数如参数是三分的话. 下面是B,不是特别处理的AB 2009-06-15 10:58:23 B 2009-06-15 10:59:23 B 2009-06-15 11:00:23 B 2009-06-15 11:01:24 B 2009-06-15 11:02:23 B 2009-06-15 11:03:23 B 2009-06-15 11:04:25 结果应为B 2009-06-15 10:58:23 B 2009-06-15 11:01:24 B 2009-06-15 11:04:25是A的话,就要两条,如三分钟内有多条,就要三分钟内的最早一条和最后一条
按照你六楼的测试了一下,修正了一下程序。mysql> delimiter // mysql> mysql> CREATE PROCEDURE simpleproc (itvl INT) -> BEGIN -> DECLARE a,a1 CHAR default ''; -> DECLARE b,b1 Datetime default '1900-01-01 00:00:00'; -> DECLARE c INT DEFAULT 0; -> -> DECLARE done INT DEFAULT 0; -> DECLARE cur1 CURSOR FOR SELECT ColA,ColB FROM tx order by ColA,ColB; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -> -> -> OPEN cur1; -> -> delete from ty; -> -> REPEAT -> FETCH cur1 INTO a, b; -> IF a != a1 or TIMEDIFF(b,b1)>itvl*100 THEN -> insert into ty values (a,b); -> set a1 = a; -> set b1 = b; -> set c = 0; -> ELSE -> if a='A' and c=0 then -> insert into ty values (a,b); -> set c = 1; -> end if; -> END IF; -> -> UNTIL done END REPEAT; -> -> END; -> // Query OK, 0 rows affected (0.00 sec)mysql> mysql> delimiter ; mysql> select * from tx; +------+---------------------+ | ColA | ColB | +------+---------------------+ | B | 2009-06-15 10:58:23 | | B | 2009-06-15 10:59:23 | | B | 2009-06-15 11:00:23 | | B | 2009-06-15 11:01:24 | | B | 2009-06-15 11:02:23 | | B | 2009-06-15 11:03:23 | | B | 2009-06-15 11:04:25 | +------+---------------------+ 7 rows in set (0.00 sec)mysql> call simpleproc(3); Query OK, 1 row affected (0.14 sec) mysql> select * from ty; +------+---------------------+ | ColA | ColB | +------+---------------------+ | B | 2009-06-15 10:58:23 | | B | 2009-06-15 11:01:24 | | B | 2009-06-15 11:04:25 | +------+---------------------+ 3 rows in set (0.00 sec)mysql>
怎么我的执行的是下面那样呢| C | 2009-06-15 10:53:36 | | B | 2009-06-15 10:55:31 | | C | 2009-06-15 10:57:36 | | A | 2009-06-15 10:58:23 | | A | 2009-06-15 10:58:39 | | B | 2009-06-15 10:59:31 | | B | 2009-06-15 11:03:31 | | A | 2009-06-15 11:04:25 | | A | 2009-06-15 11:05:25 |
对喔,我用的是4楼的数据,呵呵~~对了,MYSQL可以可以像MSSQL那样,可以 select * from t1 where a in (select a from t2)因为像A的情况不止一个,都在t2里
MySQL 支持 select * from t1 where a in (select a from t2) 这种标准的ANSI的SQL语句。
就是说可以改成下面的??mysql> delimiter // mysql> mysql> CREATE PROCEDURE simpleproc (itvl INT) -> BEGIN -> DECLARE a,a1 CHAR default ''; -> DECLARE b,b1 Datetime default '1900-01-01 00:00:00'; -> DECLARE c INT DEFAULT 0; -> -> DECLARE done INT DEFAULT 0; -> DECLARE cur1 CURSOR FOR SELECT ColA,ColB FROM tx order by ColA,ColB; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -> -> -> OPEN cur1; -> -> delete from ty; -> -> REPEAT -> FETCH cur1 INTO a, b; -> IF a != a1 or TIMEDIFF(b,b1)>itvl*100 THEN -> insert into ty values (a,b); -> set a1 = a; -> set b1 = b; -> set c = 0; -> ELSE -> if (a in (select cloa from othertable) and c=0 then //改成这样?? -> insert into ty values (a,b); -> set c = 1; -> end if; -> END IF; -> -> UNTIL done END REPEAT; -> -> END; -> //
不能这么改,因为你这不是SQL语句。 一种方法是改成如下:mysql> delimiter // mysql> mysql> CREATE PROCEDURE simpleproc (itvl INT) -> BEGIN -> DECLARE a,a1 CHAR default ''; -> DECLARE b,b1 Datetime default '1900-01-01 00:00:00'; -> DECLARE c INT DEFAULT 0; -> -> declare sAList varchar(1000); -> -> DECLARE done INT DEFAULT 0; -> DECLARE cur1 CURSOR FOR SELECT ColA,ColB FROM tx order by ColA,ColB; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -> -> select group_concat(cloa) into sAList from othertable; -- changed -> -> OPEN cur1; -> -> delete from ty; -> -> REPEAT -> FETCH cur1 INTO a, b; -> IF a != a1 or TIMEDIFF(b,b1)>itvl*100 THEN -> insert into ty values (a,b); -> set a1 = a; -> set b1 = b; -> set c = 0; -> ELSE -> if FIND_IN_SET(a,sAList) and c=0 then -- changed -> insert into ty values (a,b); -> set c = 1; -> end if; -> END IF; -> -> UNTIL done END REPEAT; -> -> END; -> // Query OK, 0 rows affected (0.00 sec)mysql> mysql> delimiter ; mysql>
我个人则更喜欢改成这种。mysql> delimiter // mysql> mysql> CREATE PROCEDURE simpleproc (itvl INT) -> BEGIN -> DECLARE a,a1,ao CHAR default ''; -> DECLARE b,b1 Datetime default '1900-01-01 00:00:00'; -> DECLARE c INT DEFAULT 0; -> -> declare sAList varchar(1000); -> -> DECLARE done INT DEFAULT 0; -> DECLARE cur1 CURSOR FOR -> SELECT tx.ColA,tx.ColB,othertable.cloa -> FROM tx left join othertable on tx.ColA=othertable.cloa -> order by ColA,ColB; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -> -> OPEN cur1; -> -> delete from ty; -> -> REPEAT -> FETCH cur1 INTO a, b, ao; -> IF a != a1 or TIMEDIFF(b,b1)>itvl*100 THEN -> insert into ty values (a,b); -> set a1 = a; -> set b1 = b; -> set c = 0; -> ELSE -> if ao is not null and c=0 then -- changed -> insert into ty values (a,b); -> set c = 1; -> end if; -> END IF; -> -> UNTIL done END REPEAT; -> -> END; -> // Query OK, 0 rows affected (0.00 sec)mysql> mysql> delimiter ; mysql>
1、与当前时间相比的三分钟内?
2、每间隔3分钟内,即0-3,3-6,6-9,……,57-60。
3、与colB的值动态偏移的3分钟内。这种情况估计是很费力气。估计楼内要的也是这个吧。以下是针对第三种情况的test.sql文件内容,已经过测试
————————————————————create database testdb;
use testdb;create table testtable (cola varchar(2),colb datetime);create index a on testtable(cola,colb);insert into testtable values
('A','2009-06-15 10:58:23'),
('B','2009-06-15 10:58:31'),
('C','2009-06-15 10:53:36'),
('C','2009-06-15 10:54:36'),
('C','2009-06-15 10:55:36'),
('C','2009-06-15 10:56:36'),
('C','2009-06-15 10:57:36'),
('C','2009-06-15 10:58:36'),
('C','2009-06-15 10:59:36'),
('A','2009-06-15 10:58:39'),
('A','2009-06-15 10:59:23'),
('B','2009-06-15 11:00:22'),
('B','2009-06-15 10:55:31'),
('B','2009-06-15 10:59:31'),
('B','2009-06-15 11:03:31'),
('B','2009-06-15 11:04:31'),
('A','2009-06-15 11:04:25'),
('A','2009-06-15 11:05:25'),
('A','2009-06-15 11:06:25');
select * from testtable;#显示一下样本数据
set @t=DATE('1900-01-01 00:00:00');#设置一个比表中任何一个时间都小的日期
set @a="";
#以下是记录合并查询语句
select cola,colb,(@t:=if(DATE_SUB(colb,INTERVAL 3 MINUTE)>=@t || cola!=@a,colb,@t)) as
mergetime,(@a:=cola) from testtable group by cola,mergetime;
+------+---------------------+
| ColA | ColB |
+------+---------------------+
| A | 2009-06-15 10:58:23 |
| B | 2009-06-15 10:58:31 |
| C | 2009-06-15 10:58:36 |
| A | 2009-06-15 10:58:39 |
| A | 2009-06-15 10:59:23 |
| B | 2009-06-15 11:00:22 |
| A | 2009-06-15 11:00:25 |
+------+---------------------+
7 rows in set (0.00 sec)mysql>
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE simpleproc (itvl INT)
-> BEGIN
-> DECLARE a,a1 CHAR default '';
-> DECLARE b,b1 Datetime default '1900-01-01 00:00:00';
-> DECLARE c INT DEFAULT 0;
->
-> DECLARE done INT DEFAULT 0;
-> DECLARE cur1 CURSOR FOR SELECT ColA,ColB FROM tx order by ColA,ColB;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
->
-> OPEN cur1;
->
-> delete from ty;
->
-> REPEAT
-> FETCH cur1 INTO a, b;
-> IF a != a1 or MINUTE(TIMEDIFF(b,b1))>itvl THEN
-> insert into ty values (a,b);
-> set a1 = a;
-> set b1 = b;
-> set c = 0;
-> ELSE
-> if a='A' and c=0 then
-> insert into ty values (a,b);
-> set c = 1;
-> end if;
-> END IF;
->
-> UNTIL done END REPEAT;
->
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> delimiter ;
mysql>
mysql> CALL simpleproc(3);
Query OK, 1 row affected (0.13 sec)mysql> select * from ty;
+------+---------------------+
| ColA | ColB |
+------+---------------------+
| A | 2009-06-15 10:58:23 |
| A | 2009-06-15 10:58:39 |
| B | 2009-06-15 10:58:31 |
| C | 2009-06-15 10:58:36 |
+------+---------------------+
4 rows in set (0.00 sec)mysql>
A,2009-06-15 10:22:13
A,2009-06-15 10:23:13
A,2009-06-15 10:24:13
A,2009-06-15 10:25:13
A,2009-06-15 10:26:13
A,2009-06-15 10:27:13
A,2009-06-15 10:30:13
A,2009-06-15 10:31:13
A,2009-06-15 10:32:13
A,2009-06-15 10:33:13
A,2009-06-15 10:34:13
A,2009-06-15 10:35:13
A,2009-06-15 10:42:13
A,2009-06-15 10:43:13
A,2009-06-15 10:44:13
A,2009-06-15 10:45:13
A,2009-06-15 10:46:13
A,2009-06-15 10:47:13
A,2009-06-15 10:48:13
A,2009-06-15 23:54:13
A,2009-06-15 23:55:13
A,2009-06-15 23:56:13
A,2009-06-15 23:57:13
A,2009-06-15 23:58:13
A,2009-06-15 23:59:13
A,2009-06-16 24:00:13
A,2009-06-16 24:01:13
A,2009-06-16 24:02:13
A,2009-06-16 24:03:13
use testdb;create table testtable (cola varchar(2),colb datetime);create index a on testtable(cola,colb);insert into testtable values
('A','2009-06-15 10:58:23'),
('B','2009-06-15 10:58:31'),
('C','2009-06-15 10:53:36'),
('C','2009-06-15 10:54:36'),
('C','2009-06-15 10:55:36'),
('C','2009-06-15 10:56:36'),
('C','2009-06-15 10:57:36'),
('C','2009-06-15 10:58:36'),
('C','2009-06-15 10:59:36'),
('A','2009-06-15 10:58:39'),
('A','2009-06-15 10:59:23'),
('B','2009-06-15 11:00:22'),
('B','2009-06-15 10:55:31'),
('B','2009-06-15 10:59:31'),
('B','2009-06-15 11:03:31'),
('B','2009-06-15 11:04:31'),
('A','2009-06-15 11:04:25'),
('A','2009-06-15 11:05:25'),
('A','2009-06-15 11:06:25'); select * from testtable;#显示一下样本数据
set @t=DATE('1900-01-01 00:00:00');#设置一个比表中任何一个时间都小的日期
set @a="";
set @Anum=0;
#以下是记录合并查询语句
select cola,colb,(@Anum:=if(DATE_SUB(colb,INTERVAL 3 MINUTE)<=@t && cola='A',@Anum+1,0)) as
Anum,(@isa:=if(cola='A' && @Anum<2,true,false)) as isa,(@t:=if(DATE_SUB(colb,INTERVAL 3
MINUTE)>=@t || cola!=@a || @isa,colb,@t)) as mergetime,(@a:=cola) from testtable group by cola,mergetime;drop table testtable;
drop database testdb;
select A.*,timediff(a.colb,b.colb) from tta2 a left join tta2 b
on a.colb + INTERVAL 3 minute =b.colb
and a.cola=b.cola WHERE B.cola IS NULL) A1 LEFT JOIN
(
select A.*,timediff(a.colb,b.colb) from tta2 a left join tta2 b
on a.colb + INTERVAL 3 minute =b.colb
and a.cola=b.cola WHERE B.cola IS NULL) A2ON A1.cola=A2.cola AND A1.COLB>=A2.COLB GROUP BY A1.cola,A1.colb
HAVING(COUNT(A2.COLA))<=IF(A1.COLA='A',2,1)
B 2009-06-15 10:59:23
B 2009-06-15 11:00:23
B 2009-06-15 11:01:24
B 2009-06-15 11:02:23
B 2009-06-15 11:03:23
B 2009-06-15 11:04:25 结果应为B 2009-06-15 10:58:23
B 2009-06-15 11:01:24
B 2009-06-15 11:04:25是A的话,就要两条,如三分钟内有多条,就要三分钟内的最早一条和最后一条
mysql>
mysql> CREATE PROCEDURE simpleproc (itvl INT)
-> BEGIN
-> DECLARE a,a1 CHAR default '';
-> DECLARE b,b1 Datetime default '1900-01-01 00:00:00';
-> DECLARE c INT DEFAULT 0;
->
-> DECLARE done INT DEFAULT 0;
-> DECLARE cur1 CURSOR FOR SELECT ColA,ColB FROM tx order by ColA,ColB;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
->
-> OPEN cur1;
->
-> delete from ty;
->
-> REPEAT
-> FETCH cur1 INTO a, b;
-> IF a != a1 or TIMEDIFF(b,b1)>itvl*100 THEN
-> insert into ty values (a,b);
-> set a1 = a;
-> set b1 = b;
-> set c = 0;
-> ELSE
-> if a='A' and c=0 then
-> insert into ty values (a,b);
-> set c = 1;
-> end if;
-> END IF;
->
-> UNTIL done END REPEAT;
->
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;
mysql> select * from tx;
+------+---------------------+
| ColA | ColB |
+------+---------------------+
| B | 2009-06-15 10:58:23 |
| B | 2009-06-15 10:59:23 |
| B | 2009-06-15 11:00:23 |
| B | 2009-06-15 11:01:24 |
| B | 2009-06-15 11:02:23 |
| B | 2009-06-15 11:03:23 |
| B | 2009-06-15 11:04:25 |
+------+---------------------+
7 rows in set (0.00 sec)mysql> call simpleproc(3);
Query OK, 1 row affected (0.14 sec)
mysql> select * from ty;
+------+---------------------+
| ColA | ColB |
+------+---------------------+
| B | 2009-06-15 10:58:23 |
| B | 2009-06-15 11:01:24 |
| B | 2009-06-15 11:04:25 |
+------+---------------------+
3 rows in set (0.00 sec)mysql>
| B | 2009-06-15 10:55:31 |
| C | 2009-06-15 10:57:36 |
| A | 2009-06-15 10:58:23 |
| A | 2009-06-15 10:58:39 |
| B | 2009-06-15 10:59:31 |
| B | 2009-06-15 11:03:31 |
| A | 2009-06-15 11:04:25 |
| A | 2009-06-15 11:05:25 |
mysql>
mysql> CREATE PROCEDURE simpleproc (itvl INT)
-> BEGIN
-> DECLARE a,a1 CHAR default '';
-> DECLARE b,b1 Datetime default '1900-01-01 00:00:00';
-> DECLARE c INT DEFAULT 0;
->
-> DECLARE done INT DEFAULT 0;
-> DECLARE cur1 CURSOR FOR SELECT ColA,ColB FROM tx order by ColA,ColB;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
->
-> OPEN cur1;
->
-> delete from ty;
->
-> REPEAT
-> FETCH cur1 INTO a, b;
-> IF a != a1 or TIMEDIFF(b,b1)>itvl*100 THEN
-> insert into ty values (a,b);
-> set a1 = a;
-> set b1 = b;
-> set c = 0;
-> ELSE
-> if (a in (select cloa from othertable) and c=0 then //改成这样??
-> insert into ty values (a,b);
-> set c = 1;
-> end if;
-> END IF;
->
-> UNTIL done END REPEAT;
->
-> END;
-> //
一种方法是改成如下:mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE simpleproc (itvl INT)
-> BEGIN
-> DECLARE a,a1 CHAR default '';
-> DECLARE b,b1 Datetime default '1900-01-01 00:00:00';
-> DECLARE c INT DEFAULT 0;
->
-> declare sAList varchar(1000);
->
-> DECLARE done INT DEFAULT 0;
-> DECLARE cur1 CURSOR FOR SELECT ColA,ColB FROM tx order by ColA,ColB;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> select group_concat(cloa) into sAList from othertable; -- changed
->
-> OPEN cur1;
->
-> delete from ty;
->
-> REPEAT
-> FETCH cur1 INTO a, b;
-> IF a != a1 or TIMEDIFF(b,b1)>itvl*100 THEN
-> insert into ty values (a,b);
-> set a1 = a;
-> set b1 = b;
-> set c = 0;
-> ELSE
-> if FIND_IN_SET(a,sAList) and c=0 then -- changed
-> insert into ty values (a,b);
-> set c = 1;
-> end if;
-> END IF;
->
-> UNTIL done END REPEAT;
->
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;
mysql>
mysql>
mysql> CREATE PROCEDURE simpleproc (itvl INT)
-> BEGIN
-> DECLARE a,a1,ao CHAR default '';
-> DECLARE b,b1 Datetime default '1900-01-01 00:00:00';
-> DECLARE c INT DEFAULT 0;
->
-> declare sAList varchar(1000);
->
-> DECLARE done INT DEFAULT 0;
-> DECLARE cur1 CURSOR FOR
-> SELECT tx.ColA,tx.ColB,othertable.cloa
-> FROM tx left join othertable on tx.ColA=othertable.cloa
-> order by ColA,ColB;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
->
-> delete from ty;
->
-> REPEAT
-> FETCH cur1 INTO a, b, ao;
-> IF a != a1 or TIMEDIFF(b,b1)>itvl*100 THEN
-> insert into ty values (a,b);
-> set a1 = a;
-> set b1 = b;
-> set c = 0;
-> ELSE
-> if ao is not null and c=0 then -- changed
-> insert into ty values (a,b);
-> set c = 1;
-> end if;
-> END IF;
->
-> UNTIL done END REPEAT;
->
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;
mysql>