เคสแรกหล่ะกัน กรณีกำหนด Key เป็น Auto increment ใน MySQL แล้วเลขลำดับ/Key/IDs บางตัวหายไป (หลายสาเหตุ) ลองมาทำ Dummy Table ทดสอบดูกัน
CREATE TABLE numbers(
startNumber INTEGER UNSIGNED,
PRIMARY KEY (`startNumber`)
);
ALTER TABLE numbers ADD INDEX idx1 (startNumber);
INSERT INTO numbers VALUES
(1),(2),(3),(4),(5),(6),(8),(9),(20),(21),(22),(53),(54),
(71),(72),(74),(80);
CREATE TABLE numbers(
startNumber INTEGER UNSIGNED,
PRIMARY KEY (`startNumber`)
);
ALTER TABLE numbers ADD INDEX idx1 (startNumber);
INSERT INTO numbers VALUES
(1),(2),(3),(4),(5),(6),(8),(9),(20),(21),(22),(53),(54),
(71),(72),(74),(80);
CREATE TABLE numbers( startNumber INTEGER UNSIGNED, PRIMARY KEY (`startNumber`) ); ALTER TABLE numbers ADD INDEX idx1 (startNumber); INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(8),(9),(20),(21),(22),(53),(54), (71),(72),(74),(80);
จากตารางดัมมี่ข้างต้นผลลัพธ์ที่เราต้องการคือตัวเลข
7, 10-19, 23-52, 55-70, 73, 75-79
7, 10-19, 23-52, 55-70, 73, 75-79
ลองเขียนคำสั่งเพื่อให้ได้คำตอบด้านบนหลายวิธี วิธีแรกต้องใช้ตาราง Sequence Number ใน MySQL อาจต้องทำ Manual ไปก่อน
วิธีแรก
CREATE TABLE seq (`seqno` INTEGER UNSIGNED, PRIMARY KEY (`seqno`));
INSERT INTO `seq` VALUES (0),(1),(2),(3),(4),(5),(6),(7);
INSERT INTO `seq` SELECT seqno+8 from `seq`;
INSERT INTO `seq` SELECT seqno+16 from `seq`;
INSERT INTO `seq` SELECT seqno+32 from `seq`;
INSERT INTO `seq` SELECT seqno+64 from `seq`;
INSERT INTO `seq` SELECT seqno+128 from `seq`;
INSERT INTO `seq` SELECT seqno+256 from `seq`;
INSERT INTO `seq` SELECT seqno+512 from `seq`;
CREATE TABLE seq (`seqno` INTEGER UNSIGNED, PRIMARY KEY (`seqno`));
INSERT INTO `seq` VALUES (0),(1),(2),(3),(4),(5),(6),(7);
INSERT INTO `seq` SELECT seqno+8 from `seq`;
INSERT INTO `seq` SELECT seqno+16 from `seq`;
INSERT INTO `seq` SELECT seqno+32 from `seq`;
INSERT INTO `seq` SELECT seqno+64 from `seq`;
INSERT INTO `seq` SELECT seqno+128 from `seq`;
INSERT INTO `seq` SELECT seqno+256 from `seq`;
INSERT INTO `seq` SELECT seqno+512 from `seq`;
CREATE TABLE seq (`seqno` INTEGER UNSIGNED, PRIMARY KEY (`seqno`)); INSERT INTO `seq` VALUES (0),(1),(2),(3),(4),(5),(6),(7); INSERT INTO `seq` SELECT seqno+8 from `seq`; INSERT INTO `seq` SELECT seqno+16 from `seq`; INSERT INTO `seq` SELECT seqno+32 from `seq`; INSERT INTO `seq` SELECT seqno+64 from `seq`; INSERT INTO `seq` SELECT seqno+128 from `seq`; INSERT INTO `seq` SELECT seqno+256 from `seq`; INSERT INTO `seq` SELECT seqno+512 from `seq`;
ตามด้วยใช้วิธีการ Left Join
SELECT
s.*
FROM seq s
LEFT JOIN numbers n ON s.seqno = n.startNumber
WHERE n.startNumber IS NULL
AND s.seqno < (SELECT MAX(startNumber) FROM numbers);
SELECT
s.*
FROM seq s
LEFT JOIN numbers n ON s.seqno = n.startNumber
WHERE n.startNumber IS NULL
AND s.seqno < (SELECT MAX(startNumber) FROM numbers);
SELECT s.* FROM seq s LEFT JOIN numbers n ON s.seqno = n.startNumber WHERE n.startNumber IS NULL AND s.seqno < (SELECT MAX(startNumber) FROM numbers);
วิธีที่ 2
SELECT nstart,
nend
FROM (SELECT m.startNumber + 1 AS nstart,
(SELECT MIN(startNumber) - 1
FROM numbers x
WHERE x.startNumber > m.startNumber) AS nend
FROM numbers m
LEFT JOIN
(SELECT startNumber-1 startNumber
FROM numbers r) r
ON (m.startNumber = r.startNumber)
WHERE r.startNumber IS NULL
) x
WHERE nend IS NOT NULL
ORDER BY nstart;
SELECT nstart,
nend
FROM (SELECT m.startNumber + 1 AS nstart,
(SELECT MIN(startNumber) - 1
FROM numbers x
WHERE x.startNumber > m.startNumber) AS nend
FROM numbers m
LEFT JOIN
(SELECT startNumber-1 startNumber
FROM numbers r) r
ON (m.startNumber = r.startNumber)
WHERE r.startNumber IS NULL
) x
WHERE nend IS NOT NULL
ORDER BY nstart;
SELECT nstart,
nend
FROM (SELECT m.startNumber + 1 AS nstart,
(SELECT MIN(startNumber) - 1
FROM numbers x
WHERE x.startNumber > m.startNumber) AS nend
FROM numbers m
LEFT JOIN
(SELECT startNumber-1 startNumber
FROM numbers r) r
ON (m.startNumber = r.startNumber)
WHERE r.startNumber IS NULL
) x
WHERE nend IS NOT NULL
ORDER BY nstart;
วิธีที่ 3 ใช้ NOT IN
SELECT
s.*
FROM seq s
WHERE s.seqno NOT IN (SELECT startNumber FROM numbers)
AND s.seqno < (SELECT MAX(startNumber) FROM numbers);
SELECT
s.*
FROM seq s
WHERE s.seqno NOT IN (SELECT startNumber FROM numbers)
AND s.seqno < (SELECT MAX(startNumber) FROM numbers);
SELECT s.* FROM seq s WHERE s.seqno NOT IN (SELECT startNumber FROM numbers) AND s.seqno < (SELECT MAX(startNumber) FROM numbers);
วิธีที่ 4 ใช้ GROUP BY เพื่อใช้ SQL aggregate functions
SELECT a.startNumber+1 AS start, MIN(b.startNumber) - 1 AS end
FROM numbers AS a, numbers AS b
WHERE a.startNumber < b.startNumber
GROUP BY a.startNumber
HAVING start < MIN(b.startNumber);
SELECT a.startNumber+1 AS start, MIN(b.startNumber) - 1 AS end
FROM numbers AS a, numbers AS b
WHERE a.startNumber < b.startNumber
GROUP BY a.startNumber
HAVING start < MIN(b.startNumber);
SELECT a.startNumber+1 AS start, MIN(b.startNumber) - 1 AS end FROM numbers AS a, numbers AS b WHERE a.startNumber < b.startNumber GROUP BY a.startNumber HAVING start < MIN(b.startNumber);
ผลลัพธ์ที่ได้ก็ไปลองเล่นตามนี้ ได้เลย http://sqlfiddle.com/#!9/bbc3e7/3
ท้ายทีสุดแล้วมันอาจมีวิธีการที่มากกว่านี้แหล่ะ ผลลัพธ์ก็ได้ได้เช่นกันแต่ความแตกต่างคือ Big O คนซีเรียสก็ควรซีเรียส 5555
