尋找MySQL中順序編號的間隙
SELECT (t1.column_name + 1) as gap_starts_at,
(SELECT MIN(t3.column_name) -1 FROM table_name t3 WHERE t3.column_name > t1.column_name) as gap_ends_at
FROM table_name t1
WHERE NOT EXISTS (SELECT t2.column_name FROM table_name t2 WHERE t2.column_name = t1.column_name + 1)
HAVING gap_ends_at IS NOT NULL
Ex. 假設有張表內容是如下圖，分別找出當bank_id為1, 2, 3時，bank_number的間隙
mysql> SELECT (t1.bank_number + 1) AS gap_starts_at, (SELECT MIN(t3.bank_number) -1 FROM bank_card t3 WHERE t3.bank_number > t1.bank_number
AND bank_id = 1) AS gap_ends_at FROM bank_card t1 WHERE NOT EXISTS (SELECT t2.bank_number FROM bank_card t2 WHERE t2.bank_number = t1.bank_number + 1
AND bank_id = 1)
AND bank_id = 1 HAVING gap_ends_at IS NOT NULL ;
+---------------+-------------+
| gap_starts_at | gap_ends_at |
+---------------+-------------+
| 2 | 4 |
| 6 | 8 |
+---------------+-------------+
mysql> SELECT (t1.bank_number + 1) AS gap_starts_at, (SELECT MIN(t3.bank_number) -1 FROM bank_card t3 WHERE t3.bank_number > t1.bank_number AND bank_id = 2) AS gap_ends_at FROM bank_card t1 WHERE NOT EXISTS (SELECT t2.bank_number FROM bank_card t2 WHERE t2.bank_number = t1.bank_number + 1 AND bank_id = 2) AND bank_id = 2 HAVING gap_ends_at IS NOT NULL;
+---------------+-------------+
| gap_starts_at | gap_ends_at |
+---------------+-------------+
| 5 | 7 |
+---------------+-------------+
mysql> SELECT (t1.bank_number + 1) AS gap_starts_at, (SELECT MIN(t3.bank_number) -1 FROM bank_card t3 WHERE t3.bank_number > t1.bank_number AND bank_id = 3) AS gap_ends_at FROM bank_card t1 WHERE NOT EXISTS (SELECT t2.bank_number FROM bank_card t2 WHERE t2.bank_number = t1.bank_number + 1 AND bank_id = 3) AND bank_id = 3 HAVING gap_ends_at IS NOT NULL;
+---------------+-------------+
| gap_starts_at | gap_ends_at |
+---------------+-------------+
| 3 | 8 |
+---------------+-------------+
結論：
1. 當bank_number起始不為1，且前面有間隙時，會找不到。
2. 會找到多個間隙
附上表結構和資料：
CREATE TABLE IF NOT EXISTS `bank_card` (
`id` int(11) NOT NULL,
`bank_id` int(11) NOT NULL,
`bank_number` int(11) NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `bank_card` (`id`, `bank_id`, `bank_number`, `name`) VALUES
(1, 1, 1, 'bear1'),
(2, 1, 5, 'bear5'),
(4, 2, 8, 'ted8'),
(5, 1, 9, 'bear9'),
(6, 3, 9, 'ss9'),
(7, 3, 2, 'ss2'),
(8, 2, 4, 'ted4');
附上sqlfiddle範例：
http://sqlfiddle.com/#!9/34cb0/1/0
參考資料： http://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql
，然後點選或按一下 [管理附加元件]。