code/mysqlquery.sql at master · yucl80/code · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
use test;
selectl.id+1as start, min(fr.id) -1as stop
from testa as l
left outer join testa as r onl.id=r.id-1
left outer join testa as fr onl.id<fr.id
wherer.id is nullandfr.idis not null
group byl.id, r.id;

selectl.id+1as start
from testa as l
left outer join testa as r onl.id+1=r.id
wherer.id is null;

select start, stop from (
selectm.id+1as start,
 (selectmin(id) -1from testa as x wherex.id>m.id) as stop
from testa as m
left outer join testa as r onm.id=r.id-1
wherer.id is null
) as x
where stop is not null;

SELECT (t1.id+1) as gap_starts_at
FROM testa t1
WHERE NOT EXISTS (SELECTt2.idFROM testa t2 WHEREt2.id=t1.id+1);


SELECT (t1.id+1) as gap_starts_at, (SELECTMIN(t3.id) -1FROM testa t3 WHEREt3.id>t1.id) as gap_ends_at
FROM testa t1
WHERE NOT EXISTS (SELECTt2.idFROM testa t2 WHEREt2.id=t1.id+1)
HAVING gap_ends_at IS NOT NULL;

insert into sequence(id) values
 ('a'), ('b'), ('c'), ('e'),
 ('f'), ('g'), ('l'), ('m'), ('n');

select start, stop from (
selectchar(ascii(m.id) +1) as start,
 (selectchar(min(ascii(id)) -1) from sequence as x wherex.id>m.id) as stop
from sequence as m
left outer join sequence as r on ascii(m.id) = ascii(r.id) -1
wherer.id is null
) as x
where stop <>'';


select id, count(*) from sequence
group by id
havingcount(*) >1;


https://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/