Как выбрать пропущеные ID?
1,00
р.
Есть поле ID в некой таблице Tab, некоторые номера пропущены, например: 1,2,4,5,6,8
Как выбрать номера 3 и 7?
Я давно делал такой запрос и вот не могу вспомнить как )) Там помню в условии было ID+1
Ответ
Алгоритм. Для каждого Id смотрим следующий, если он не равен текущему+1 - находим пропуск. Пусть есть таблица SomeTable, и у неё записи со значениями id: 5,7,8,11,15 Найдём интервалы пропусков:
SELECT id+1 as start_interval, next_id-1 as finish_interval FROM( SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id FROM SomeTable )T WHERE id+1 <> next_id
выдаст:
start_interval finish_interval 6 6 9 10 12 14
Если нас интересует интервал от 1 до первого Id, искусственно внедрим в запрос id=0:
SELECT id+1 as start_interval, next_id-1 as finish_interval FROM( SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id FROM ( SELECT 0 Id UNION ALL SELECT Id FROM SomeTable )T )T WHERE id+1 <> next_id
выдаст:
start_interval finish_interval 1 4 6 6 9 10 12 14
Примечание: Использовал MS SQL начиная с версии 2012, в Oracle и PostgreSQL вроде тоже должно работать.
Для версий MS SQL ниже 2012 можно использовать следующий код, который практически не уступает по производительности LEAD():
SELECT id1+1 start_interval, id2-1 finish_interval FROM( SELECT MAX(CASE WHEN dN=1 THEN Id END)id1, MAX(CASE WHEN dN=0 THEN Id END)id2 FROM( SELECT ROW_NUMBER()OVER(ORDER BY Id)as N, Id FROM SomeTable )T1 , ( SELECT 0 as dN UNION ALL SELECT 1 )T2 GROUP BY N+dN )T WHERE Id1+1 <> Id2
Для MySQL будет выглядеть примерно так
SELECT last_id+1 as start_interval, currentId-1 as finish_interval FROM( SELECT @Id last_id, @Id := Id currentId FROM SomeTable, (SELECT @Id := NULL)T ORDER BY Id )T WHERE last_id+1 <> current_id
Как выбрать номера 3 и 7?
Я давно делал такой запрос и вот не могу вспомнить как )) Там помню в условии было ID+1
Ответ
Алгоритм. Для каждого Id смотрим следующий, если он не равен текущему+1 - находим пропуск. Пусть есть таблица SomeTable, и у неё записи со значениями id: 5,7,8,11,15 Найдём интервалы пропусков:
SELECT id+1 as start_interval, next_id-1 as finish_interval FROM( SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id FROM SomeTable )T WHERE id+1 <> next_id
выдаст:
start_interval finish_interval 6 6 9 10 12 14
Если нас интересует интервал от 1 до первого Id, искусственно внедрим в запрос id=0:
SELECT id+1 as start_interval, next_id-1 as finish_interval FROM( SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id FROM ( SELECT 0 Id UNION ALL SELECT Id FROM SomeTable )T )T WHERE id+1 <> next_id
выдаст:
start_interval finish_interval 1 4 6 6 9 10 12 14
Примечание: Использовал MS SQL начиная с версии 2012, в Oracle и PostgreSQL вроде тоже должно работать.
Для версий MS SQL ниже 2012 можно использовать следующий код, который практически не уступает по производительности LEAD():
SELECT id1+1 start_interval, id2-1 finish_interval FROM( SELECT MAX(CASE WHEN dN=1 THEN Id END)id1, MAX(CASE WHEN dN=0 THEN Id END)id2 FROM( SELECT ROW_NUMBER()OVER(ORDER BY Id)as N, Id FROM SomeTable )T1 , ( SELECT 0 as dN UNION ALL SELECT 1 )T2 GROUP BY N+dN )T WHERE Id1+1 <> Id2
Для MySQL будет выглядеть примерно так
SELECT last_id+1 as start_interval, currentId-1 as finish_interval FROM( SELECT @Id last_id, @Id := Id currentId FROM SomeTable, (SELECT @Id := NULL)T ORDER BY Id )T WHERE last_id+1 <> current_id
