Здравствуйте!

Есть задачка:

Пропущенные интервалы натурального ряда — решение принимается в виде скрипта, содержащего Transact-SQL с запросом(-ами) для получения ожидаемой выборки.
В базе данных имеется таблица, хранящая натуральные числа без повторений. Необходимо вывести отсутствующие интервалы.
declare @values as table
(
[number] int not null -- значение (уникальное значение в рамках таблицы)
)

/* Тестовая ситуация */
insert into @values
(
[number]
)
values (1), (2), (3), (5), (9)

/* Ожидаемый результат */
/*
left right
4 4
6 8
*/

	Мое решение
	declare @values as table ( [number] int not null) insert into @values ( [number] ) values (1) , (2) , (3) , (5) , (9) declare @t2 as table ([a] int not null, [b] int not null default 0) insert into @t2 ([a]) select * from @values order by [number]; select * from @t2 UPDATE t SET b = isnull((select top 1 a from @t2 tt where tt.[a]>t.a order by a), a) — a FROM @t2 t select * from @t2 where b > 1 declare @res as table ( [left] int not null, [right] int not null ) insert @res([left], [right]) select [a] + 1, [a] + [b] — 1 from @t2 where b > 1 select * from @res

Я не очень хорошо знаю sql.
Умные люди говорят, что можно сделать это без временных таблиц и UPDATE.
Подскажите как?

Здравствуйте, it.sting, Вы писали:

IS>Я не очень хорошо знаю sql.
IS>Умные люди говорят, что можно сделать это без временных таблиц и UPDATE.
IS>Подскажите как?

Оконные функции.

Вот решение на PostgreSQL:

SELECT lv + 1 AS left, v - 1 AS right
FROM (
    SELECT lag( v ) OVER ( ORDER BY v ) AS lv, v
    FROM (
        SELECT unnest( ARRAY[ 1, 2, 3, 5, 9 ] ) v
    ) _1
) _2
WHERE lv != v - 1

На MSSQL всё должно быть аналогично (кроме синтаксиса массивов)

Здравствуйте, it.sting, Вы писали:

IS>Я не очень хорошо знаю sql.
IS>Умные люди говорят, что можно сделать это без временных таблиц и UPDATE.
IS>Подскажите как?
Как вариант решения в лоб:

declare @values as table ( [number] int not null)

insert into @values ( [number] ) values  (1) , (2) , (3) , (5) , (9)

select 
  [left]  = number + 1 
, [right] = nextValue - 1
from @values v
cross apply (select min(v2.number) as nextValue from @values nv where nv.number > v.number) n
where n.nextValue <> v.number + 1

Здравствуйте, it.sting, Вы писали:

IS>Я не очень хорошо знаю sql.
IS>Умные люди говорят, что можно сделать это без временных таблиц и UPDATE.
IS>Подскажите как?

Ваша задача называется Gaps and Islands. В интернет по этим ключевым словам можно найти обширное количество решений, но многие из них грешат тем, что дырки и острова считают с первого минимального значения в массиве, а это не всегда правильно. Возможно, при решении задачи стоит уточнить у авторов, с какого значения выполнять вычисления. Я свои решения делал в предположении, что дырки начинаются с 1, именно поэтому в запросах ниже вы найдете дополнительный union all 0. Надеюсь ошибок не допустил, требуется проверка.

declare @values as table
 (
 [number] int not null -- значение (уникальное значение в рамках таблицы)
 )

 /* Тестовая ситуация */
insert into @values
 (
 [number]
 )
 values (2), (3), (5), (9)
 
-- Решение для 2000+
select number + 1,
    (
        select min(b.number) as n
        from 
        (
            select *
            from @values
            union all
            select 0
        ) b
        where b.number > a.number
    ) - 1
from 
(
        select *
        from @values
        union all
        select 0
) a
where not exists
(
    select *
    from 
    (
        select *
        from @values
        union all
        select 0
    ) c
    where c.number = a.number + 1
) and a.number < 
    (
        select max(number) 
        from 
        (
            select *
            from @values
            union all
            select 0
        ) d
    )

 -- Решение для 2005+
 ;with cte as
(
    select *, row_number() over(order by number) as n
    from
    (
        select *
        from @values
        union all
        select 0
    ) a
)
select t2.number + 1, t1.number - 1
from cte t1
join cte t2 on t1.n = t2.n + 1
where t1.number - t2.number > 1

-- Решение для 2012+
select number + 1, n
from
(
    select number, lead(number, 1, 0) over(order by number) - 1 as n
    from
    (
        select *
        from @values
        union all
        select 0
    ) a
) a
where a.number < n

Здравствуйте, Olaf, Вы писали:

O>Ваша задача называется Gaps and Islands. В интернет по этим ключевым словам можно найти обширное количество решений, но многие из них грешат тем, что дырки и острова считают с первого минимального значения в массиве, а это не всегда правильно. Возможно, при решении задачи стоит уточнить у авторов, с какого значения выполнять вычисления.
Ожидаемый результат дает ответ на этот вопрос.

Здравствуйте, _ABC_, Вы писали:

_AB>Здравствуйте, Olaf, Вы писали:

O>>Ваша задача называется Gaps and Islands. В интернет по этим ключевым словам можно найти обширное количество решений, но многие из них грешат тем, что дырки и острова считают с первого минимального значения в массиве, а это не всегда правильно. Возможно, при решении задачи стоит уточнить у авторов, с какого значения выполнять вычисления.
_AB>Ожидаемый результат дает ответ на этот вопрос.

Согласен. Значит union all select 0 нужно убрать из решения.

MS SQL 2008

with s as -- исходные данные
(select 1  id union all
 select 2  id union all
 select 3  id union all
 select 5  id union all
 select 9  id union all
 select 15 id union all
 select 19 id
),
rn as 
(select id, ROW_NUMBER()  over (order by id) rn
 from s
)
select t1.id + 1 as d1, t2.id - 1 as d2
from rn t1, rn t2
where t1.rn = t2.rn - 1
and t2.id - t1.id > 1;

Иль>На MSSQL всё должно быть аналогично (кроме синтаксиса массивов)
Функция lag появилась в MS SQL 2012.

Здравствуйте, AmKad, Вы писали:

Иль>>На MSSQL всё должно быть аналогично (кроме синтаксиса массивов)
AK>Функция lag появилась в MS SQL 2012.
А row_number в 2005. И, вообще, я один вижу пост Olaf'a? Он всё это уже
продемонстрировал за несколько дней до.