SET @a=0; SET @b=0; SELECT * FROM TTQA; SELECT id-1 INTO @a FROM ttqa LIMIT 1; UPDATE TTQA A INNER JOIN ( SELECT PM,MIN(ID1) AS MI,MAX(ID1) AS MX FROM ( SELECT *, @b:=IF( @a+1=id ,@b,@b+1) AS PM, @a:=ID FROM ttqa) G GROUP BY PM HAVING COUNT(*)>=5) C ON A.`id1` BETWEEN MI AND MX SET A.`备注`='*'; SELECT * FROM TTQA
insert into test (id,describe) values(记录号,记录描述);
记录号连续出现,但是起始点和终止点不同,例如有从3到15的连续数据,后面也会有从4到9的连续数据,例表如下:
id describe
1 string
2 string
3 string
2 string
3 string
6 string
3 string
4 string
5 string
6 string
7 string
9 string
10 string
想要的结果是从中选取id从3连续到18的数据
id describe 备注(想要取出的结果后标*)
2 string
3 string
4 string
5 string
1 string *
2 string *
3 string *
4 string *
5 string *
3 string
4 string
5 string
就是想把上述结果中id从1到5连续的数据取出(想要的结果后标*)
SET @b=0;
SELECT * FROM TTQA;
SELECT id-1 INTO @a FROM ttqa LIMIT 1;
UPDATE TTQA A INNER JOIN (
SELECT PM,MIN(ID1) AS MI,MAX(ID1) AS MX FROM (
SELECT *,
@b:=IF( @a+1=id ,@b,@b+1) AS PM,
@a:=ID
FROM ttqa) G GROUP BY PM HAVING COUNT(*)>=5) C
ON A.`id1` BETWEEN MI AND MX
SET A.`备注`='*';
SELECT * FROM TTQA