select
nxt
from (
select
id, next() over (order by id) nxt
from
MyTable
)
where
nxt <> id
Выборка пропущенных индексов
Существует тaблица table (id INTEGER), заполненная, например , так:
Id
-----
1
2
3
5
6
8
...
Нужно вывести пропуски, то есть 4 и 7 в этом случае. Использовать стандартный SQL92.
Совсем запутался уже. Мысли ведут к созданию последовательности от min(id) до max(id) и потом LEFT JOIN, но вот не знаю как сделать
Id
-----
1
2
3
5
6
8
...
Нужно вывести пропуски, то есть 4 и 7 в этом случае. Использовать стандартный SQL92.
Совсем запутался уже. Мысли ведут к созданию последовательности от min(id) до max(id) и потом LEFT JOIN, но вот не знаю как сделать
Цитата: Freeman
Код:
select
nxt
from (
select
id, next() over (order by id) nxt
from
MyTable
)
where
nxt <> id
nxt
from (
select
id, next() over (order by id) nxt
from
MyTable
)
where
nxt <> id
Во первых next(), over - это не sql92. Во вторых, после первого же пропуска у вас все смещения номеров "поедут", и будет выводиться все что идет после первого смещения.
2Squarc:
Вам так или иначе нужно уметь генерировать пропущенные ИД. Для этого можно использовать либо таблицу последовтельных чисел подготовленную заранее(либо любую таблицу в которой будет строк больше чем максимальное значение), либо всяческие навороты типа рекурсивного cte в mssql. Но т.к. вам надо sql92, то наверное подойдет таблица.
Если же вы можете гарантировать что числа идут последовательно, а пропуски не более одного числа, то можно вот так (проверял на mssql)
Код:
declare @t table (id int)
insert into @t values (1)
insert into @t values (2)
insert into @t values (3)
insert into @t values (5)
insert into @t values (6)
insert into @t values (8)
insert into @t values (9)
insert into @t values (11)
-- но если будет вот так, то запрос вернет неверные результаты
--insert into @t values (15)
select
t1.id,
missed = t1.id - 1,
t2.id
from
@t t1
left join @t t2 on t1.id-1 = t2.id
where
t1.id-1 <> 0 and
t2.id is null
insert into @t values (1)
insert into @t values (2)
insert into @t values (3)
insert into @t values (5)
insert into @t values (6)
insert into @t values (8)
insert into @t values (9)
insert into @t values (11)
-- но если будет вот так, то запрос вернет неверные результаты
--insert into @t values (15)
select
t1.id,
missed = t1.id - 1,
t2.id
from
@t t1
left join @t t2 on t1.id-1 = t2.id
where
t1.id-1 <> 0 and
t2.id is null
Однако, если вы не можете это гарантировать, то лучше сделайте таблицу.
Цитата: SomewherSomehow
Во первых next(), over - это не sql92.
Да, с next я нагнал. Такой функции вообще нет. :confused: Одним запросом написал так:
Код:
with t as (
select 1 id from dual union all
select 2 from dual union all
select 3 from dual union all
select 5 from dual union all
select 6 from dual union all
select 10 from dual
)
select
s.id
from
(select
level + (select min(id) from t) - 1 id
from
dual
connect by
level < (select max(id) - min(id) + 1 from t)) s
left join
t
on
s.id = t.id
where
t.id is null
order by
1
select 1 id from dual union all
select 2 from dual union all
select 3 from dual union all
select 5 from dual union all
select 6 from dual union all
select 10 from dual
)
select
s.id
from
(select
level + (select min(id) from t) - 1 id
from
dual
connect by
level < (select max(id) - min(id) + 1 from t)) s
left join
t
on
s.id = t.id
where
t.id is null
order by
1
Работает, выдаёт 4, 7, 8, 9, но это далеко не SQL-92. :)
Обновлено: чуток переписал запрос, теперь летает, в т. ч. на реальных данных. Для проверки вставляем в with нужный запрос вместо тестовой inline-таблицы, типа:
Код:
select my_non_sequential_id id from my_data_table
а что обязательно вернуть все id из диапазонов? просто есть вариант как вернуть границы промежутков, может подойдет?
да там по условию задачи и единичные пропуски пойдут
Цитата: LM(AL/M)
просто есть вариант как вернуть границы промежутков, может подойдет?
Заинтриговал. Если завтра будет время, напишу.