尋找MySQL中順序編號的間隙

尋找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