我在MySQL中有这个表,举例来说:
ID | Name
1 | Bob
4 | Adam
6 | Someguy
如果您注意到,没有ID编号(2、3和5)。
我该如何编写查询,使MySQL仅回答缺失的ID,例如:"2,3,5"?
我在MySQL中有这个表,举例来说:
ID | Name
1 | Bob
4 | Adam
6 | Someguy
如果您注意到,没有ID编号(2、3和5)。
我该如何编写查询,使MySQL仅回答缺失的ID,例如:"2,3,5"?
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM testtable AS a, testtable AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)
希望这个链接也有所帮助:http://www.codediesel.com/mysql/sequence-gaps-in-mysql/
一条更有效率的查询:
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
SELECT id+1 FROM table WHERE id NOT IN (SELECT id-1 FROM table) ORDER BY 1
这个查询非常高效。但是,它还包括一个额外的行,在末尾,它等于最高ID号加1。可以通过检查返回的行数(mysqli_num_rows)并在行数大于1时使用for
循环来忽略服务器脚本中的最后一行(该查询将始终返回至少一行)。
编辑: 我最近发现原始解决方案没有返回所有缺失的ID号,对于缺失号码是连续的情况(即相邻的数字),这个解决方案仍然有用,可以快速地判断是否存在缺失的ID号码,并节省时间,如果与hagensoft的查询(排名第一的答案)一起使用,可以帮助识别确切的缺失ID号码(不会节省时间,但几乎不会变慢)。如果未发现任何内容,则可能节省了大量时间,因为无需运行hagensoft的查询。
AND id != (
SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'my_db'
AND TABLE_NAME = 'users'
) ORDER BY 1;```
- Waseem AlmolikySELECT 1 as gap_starts_at,
(SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;
在一次查询中获取间隙的起始点和在另一次查询中获取间隙的结束点将更加高效。
当我尝试在一个查询中同时获取这两个结果时,由于数据量达到了1800万条,查询超时了一小时。但是分别获取这两个结果,每个查询时间不到1秒。
获取间隙的起始点:
SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id + 1);
SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id - 1);
select start from
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) c order by start;
475
、477
、506
、508
、513
,但是使用双列版本,它给我 [475,475]
、[477,506]
、[508,513]
,这告诉我我错过了 475
、477-506
和 508-513
这些数字。 - Jo.SET @st=0,@diffSt=0,@diffEnd=0;
SELECT res.startID, res.endID, res.diff
, CONCAT(
"SELECT * FROM lost_consumer WHERE ID BETWEEN "
,res.startID+1, " AND ", res.endID-1) as `query`
FROM (
SELECT
@diffSt:=(@st) `startID`
, @diffEnd:=(a.ID) `endID`
, @st:=a.ID `end`
, @diffEnd-@diffSt-1 `diff`
FROM consumer a
ORDER BY a.ID
) res
WHERE res.diff>0;
通过使用 窗口函数(在mysql 8中可用),可以将在id
列中找到的间隙表示为:
WITH gaps AS
(
SELECT
LAG(id, 1, 0) OVER(ORDER BY id) AS gap_begin,
id AS gap_end,
id - LAG(id, 1, 0) OVER(ORDER BY id) AS gap
FROM test
)
SELECT
gap_begin,
gap_end
FROM gaps
WHERE gap > 1
;
SELECT
gap_begin,
gap_end
FROM (
SELECT
@id_previous AS gap_begin,
id AS gap_end,
id - @id_previous AS gap,
@id_previous := id
FROM (
SELECT
t.id
FROM test t
ORDER BY t.id
) AS sorted
JOIN (
SELECT
@id_previous := 0
) AS init_vars
) AS gaps
WHERE gap > 1
;