Реализация запроса:

[vario_rus]

Уважаемые знатоки, подскажите, как красивее решить следующую задачу:

Запрос возвращает следующее:
имя, номер
XXX 3
XXX 4
XXX 5
XXX 6
YYY 1
YYY 5
YYY 7

задача - вернуть недостающие цифры в порядковой нумерации, т.е. результат должен быть следующим:

XXX 1
XXX 2
YYY 2
YYY 3
YYY 4
YYY 6

Пока реализовал через курсор, но получилось довольно громоздко. Вероятно есть и другие решения.

[motorway]

Ну в PHP, например, есть функции для работы с массивами. Можно вычесть из массива эти элементы, что останется - то выводить. Или в цикле проверять на совпадение...

[soleil@mmc]

может еще озвучишь СУБД? тогда решение будет самым красивым

[vario_rus]

Сообщение от soleil@mmc

может еще озвучишь СУБД? тогда решение будет самым красивым

Извините, MS SQL 2008

[soleil@mmc]

может и дождемся варианта от МССКЛ-ьщиков
и ваш вариант тоже хотелось бы посмотреть
а "курсор" - это же запрос или так ХП обзывают в МССКЛ?

вот вариант для Оракла

with 
  t as (
  select 'XXX' i_name, 3 i_number from dual union all
  select 'XXX', 4 from dual union all
  select 'XXX', 5 from dual union all
  select 'XXX', 6 from dual union all
  select 'YYY', 1 from dual union all
  select 'YYY', 5 from dual union all
  select 'YYY', 7 from dual
  ),
  t2 as (
  select 
    case when min(i_number) > 1 then 1 else min(i_number) end num_min, 
    max(i_number) num_max, 
    i_name
  from t
  group by i_name)

select   
  i_name,
  num_min + level - 1 i_number
from t2
connect by 
  connect_by_root i_name = i_name
  and level <= num_max
minus
select   
  i_name,
  i_number
from t
order by 1, 2

с "коннект бай" до 1 млн. строк вполне себе шустро летает

[soleil@mmc]

а если отображать интервалами, то будет проще и красивее таким запросом

with 
  t as (
  select 'XXX' i_name, 3 i_number from dual union all
  select 'XXX', 4 from dual union all
  select 'XXX', 5 from dual union all
  select 'XXX', 6 from dual union all
  select 'YYY', 1 from dual union all
  select 'YYY', 5 from dual union all
  select 'YYY', 7 from dual
  )

select 
  i_name, 
  i_number_from, 
  i_number_to 
from
  ( select 
      i_name,
      i_number,
      lag(i_number, 1, 0) over (partition by i_name order by i_number) + 1 as i_number_from,
      i_number - 1 as i_number_to
    from t
  )
where i_number_from <= i_number_to

[Serge_Bliznykov]

ну, если TC устроит вариант с указанием пропущенных диапазонов, то аналог (чуть расширенный - дополнительно указывается количество пропущенных) запроса из пост #6 в MS SQL будет выглядеть примерно так:

-- drop table #xxx
create table #xxx([i_name] [varchar](20) NOT NULL, [id] [numeric](3, 0) NOT NULL)

insert into #xxx([i_name], [id]) values ('xxx', 3)
insert into #xxx([i_name], [id]) values ('xxx', 4)
insert into #xxx([i_name], [id]) values ('xxx', 5)
insert into #xxx([i_name], [id]) values ('xxx', 6)
insert into #xxx([i_name], [id]) values ('yyy', 1)
insert into #xxx([i_name], [id]) values ('yyy', 5)
insert into #xxx([i_name], [id]) values ('yyy', 7)


select t1.i_name, t1.id+1 NumBeg, min(t2.id)-1 NumEnd, min(t2.id)-t1.id-1 Cnt
from 
  (select i_name, 0 as id from #xxx
       group by i_name
   union all 
   select i_name, id from #xxx) t1, #xxx t2
where t1.id<t2.id
  and t1.i_name = t2.i_name
group by t1.i_name, t1.id
having min(t2.id)-t1.id>1

p.s. создание и заполнение временной таблицы можно, разумеется, выкинуть, это для отладки/проверки вставлено...

p.p.s. автор оригинального запроса не я, копирайт некоего автора с sql.ru

[vario_rus]

Сообщение от soleil@mmc

может и дождемся варианта от МССКЛ-ьщиков
и ваш вариант тоже хотелось бы посмотреть
а "курсор" - это же запрос или так ХП обзывают в МССКЛ?

Приведу свой вариант c курсором. Так как строк обрабатывается от силы 100-150, такой вариант приемлем, с большим количеством строк, конечно, будут тормоза.

insert into @Table ([name], [num]) values ('xxx', 3)
insert into @Table ([name], [num]) values ('xxx', 4)
insert into @Table ([name], [num]) values ('xxx', 5)
insert into @Table ([name], [num]) values ('xxx', 6)
insert into @Table ([name], [num]) values ('yyy', 1)
insert into @Table ([name], [num]) values ('yyy', 5)
insert into @Table ([name], [num]) values ('yyy', 7)

Declare @name as VarChar(50),
@MaxNum as int,
@I as int

DECLARE names_cursor CURSOR FOR 
{
Здесь SELECT возвращает таблицу имен с максимальным значением NUM:
В данном случае будет 
 xxx 6
 yyy 7
}
OPEN names_cursor

FETCH NEXT FROM names_cursor INTO @name, @MaxNum
WHILE @@FETCH_STATUS = 0
BEGIN
SET @I=1

WHILE @i<=@MaxNum BEGIN
// циклом бежим по таблице и вставляем недостающие строки
update @Table 
set name=name 
where name=@name
AND num=@I
if @@ROWCOUNT=0
   BEGIN
insert into @Table ([name], [num]) values (@Name, @I)
   END

SET @i=@i+1
END

FETCH NEXT FROM names_cursor INTO @name, @MaxNum
END
CLOSE names_cursor  
DEALLOCATE names_cursor

Громоздко, но результат выдает нужный.

[soleil@mmc]

имхо запросом с найденными интервалами "дырок" будет шустрее
стоит только сравнить лист из 1 млн. записей, в котором есть 100-150 дырок (пусть "дырки" будут распределены равномерно по всему диапазону записей) - ваш "курсор" будет молотить все эти записи и при этом "потрогает" каждую (может там еще и триггеры будут задействованы), а через запрос получите лист из тех же 100-150 дырок

[vario_rus]

Сообщение от soleil@mmc

имхо запросом с найденными интервалами "дырок" будет шустрее
стоит только сравнить лист из 1 млн. записей, в котором есть 100-150 дырок (пусть "дырки" будут распределены равномерно по всему диапазону записей) - ваш "курсор" будет молотить все эти записи и при этом "потрогает" каждую (может там еще и триггеры будут задействованы), а через запрос получите лист из тех же 100-150 дырок

Оно и понятно, но элегантного варианта для MS SQL пока никто не предложил.