Mysql在临时表中找到id间隙

Question

我需要在临时表的整数字段中查找 id 间隙。由于临时表限制，我无法打开该表两次并将其与自身连接。

这是例子:

CREATE TEMPORARY TABLE tmp_table (
    id int(8) unsigned,
    PRIMARY KEY (id)
);

INSERT INTO tmp_table VALUES (1),(2),(4),(7),(10),(11),(13);

在这里我想找到这个结果集:

3, 5, [6], 8, [9], 12

[#]=可选

Answer 1

一种有效但可能更好的解决方案。

这会生成一系列数字(因此您需要将其修改为更大的范围 - 这最多可处理 1000)，并将该范围与临时表交叉连接。它从临时表中找到大于或等于或小于或等于(分别)生成的值的最大和最小 ID。然后在 HAVING 子句中检查更大/更小的值是否不相等且不为空

SELECT 1 + units.i + tens.i * 10 + hundreds.i * 100 AS poss_num, 
        MAX(IF(tmp_table.id <= 1 + units.i + tens.i * 10 + hundreds.i * 100, tmp_table.id, NULL)) AS max_id, 
        MIN(IF(tmp_table.id >= 1 + units.i + tens.i * 10 + hundreds.i * 100, tmp_table.id, NULL)) AS min_id
FROM 
(
    SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) units
CROSS JOIN
(
    SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) tens
CROSS JOIN
(
    SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) hundreds
CROSS JOIN tmp_table
GROUP BY poss_num
HAVING min_id != max_id
AND min_id IS NOT NULL
AND max_id IS NOT NULL