Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 узнать пропуски чисел в счётчике  [new]
Erik_Kartmann
Member

Откуда: Пиндырдышханда
Сообщений: 948
дан обычный счётчик [ID] [int] IDENTITY(1,1) NOT NULL
как узнать в большой таблице пропуски ?
Например
автор
1
2
4
5
6
11
12
13
14

нужно получить
автор
3
7
8
9
10

есть вариант, не основанный на сжоивании с другой таблицей, чтоб потом по null опредлять пропуски ?
17 сен 15, 10:40    [18159416]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4248
Этот вопрос всплывает раз десять в год.
Посмотрите FAQ, помнится там что-то было на эту тему.
17 сен 15, 10:50    [18159465]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
o-o
Guest
Erik_Kartmann,
стандартный gaps?
declare @t table (id int);
insert into @t values
(1),
(2),
(4),
(5),
(6),
(11),
(12),
(13),
(14);

with c as
(
select id as cur, lead(id) over(order by id) as nxt
from @t
)

select cur + 1 as range_start, nxt - 1 as range_end
from c
where nxt - cur > 1;
---
range_start	range_end
3	3
7	10
17 сен 15, 10:51    [18159471]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
Erik_Kartmann
Member

Откуда: Пиндырдышханда
Сообщений: 948
что за lead ?
17 сен 15, 11:02    [18159529]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
o-o
Guest
LEAD (Transact-SQL)

потом, если надо, можно и в столбец развернуть:
declare @t table (id int);
insert into @t values
(1),
(2),
(4),
(5),
(6),
(11),
(12),
(13),
(14);

with c as
(
select id as cur, lead(id) over(order by id) as nxt
from @t
)

,c1 as 
(
select cur + 1 as range_start, nxt - 1 as range_end
from c
where nxt - cur > 1
)

select range_start + n as id
from c1 cross apply (select n
                     from dbo.Nums
                     where n between 1 and range_end - range_start
                     union all 
                     select 0)a;
---
3
8
9
10
7
17 сен 15, 11:09    [18159574]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
Erik_Kartmann
что за lead ?
Это такая функция в некоторых версиях сиквела.
Предполагается, что если вы не называете версию, то решение можно предлагать для любой.
17 сен 15, 11:09    [18159575]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
Erik_Kartmann
Member

Откуда: Пиндырдышханда
Сообщений: 948
alexeyvg,

блин у меня раньше версия в подписи писалась. щяс куда-то дели её. если что 2008й сервак
17 сен 15, 11:49    [18159864]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
o-o
Guest
Erik_Kartmann
2008й сервак

ну значит не повезло и все решения тормозные.
стандартное:
2 left join таблицы с самой собой со сдвигом на 1 в одну и другую стороны, чтобы найти начало/конец очередной дыры,
предварительно пронумеровав имеющиеся id.
17 сен 15, 12:11    [18160024]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
o-o
Guest
несу что-то не то, зачем 2 джойна, один конечно же:
declare @t table (id int);
insert into @t values
(1),
(2),
(4),
(5),
(6),
(11),
(12),
(13),
(14);

with c as
(
select id, ROW_NUMBER() over (order by id) as rn
from @t
)

,c1 as 
(
select t1.id as cur, t2.id as nxt
from c t1 left join c t2 on t1.rn + 1 = t2.rn
)

select cur + 1 as range_start, nxt - 1 as range_end
from c1
where nxt - cur > 1;
---
range_start	range_end
3	3
7	10
17 сен 15, 12:32    [18160181]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
o-o
Guest
да и зачем left?
перепроверяйте, короче
все, у меня башка болит, вообще уйду домой с обеда.
всем bye
17 сен 15, 12:37    [18160239]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
Сруль.
Member

Откуда:
Сообщений: 121
--drop table #t --на всяк.случай, вдруг пригодится.
go
declare @i bigint, @max_ident bigint
select @i=1,--бегунок
@max_ident=(select max(Id) from T)--верхняя граница

create table #t(ID bigint)--Создание болванки.
--Заполнение болванки правильной последовательностью.
while(@i<=(@max_ident))
begin
insert into #t
(ID)
values(@i)
set @i=@i+1

end
----------в болванке останутся только дыры------------------
delete #t
from #t,T
where #t.ID=T.ID
--А вот и они:
select ID from #t
17 сен 15, 13:03    [18160445]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Границы дырок легко считаются с помощью предиката NOT EXISTS().
Ведь справа от левой границы нет следующего значения,
а слева от правой границы нет предыдущего.
17 сен 15, 13:06    [18160459]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
o-o
Guest
Сруль.
--drop table #t --на всяк.случай, вдруг пригодится.
go
declare @i bigint, @max_ident bigint
select @i=1,--бегунок
@max_ident=(select max(Id) from T)--верхняя граница

create table #t(ID bigint)--Создание болванки.
--Заполнение болванки правильной последовательностью.
while(@i<=(@max_ident))
begin
insert into #t
(ID)
values(@i)
set @i=@i+1

end
----------в болванке останутся только дыры------------------
delete #t
from #t,T
where #t.ID=T.ID
--А вот и они:
select ID from #t

капец.
даже в бреду такое делать не стану и никому не пожелаю.
ТС просил "вариант, не основанный на сжоивании с другой таблицей",
и типа вы не это самое предлагаете,
но блин еще догадаться все делитить -- это круто
(правда думаете, что ваши вставки/удаления логироваться не будут только потому, что временная таблица?)
17 сен 15, 13:24    [18160558]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
o-o
Guest
вот моя вчерашняя миллионная табличка: nums_str_200_copy.
найдем в ней пропуски n обоими способами:
o-o:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'nums_str_200_copy'. Scan count 2, logical reads 52634, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1670 ms, elapsed time = 1673 ms.

Сруль.:
не, ну тут любой спойлер статистика раздерет,
придется картинкой.
там бегунок в окне мессаджей намекает, сколько там на каждуй операцию вывелось.
но в целом 7 минут против 1,6 секунд

а теперь представьте, что там не 1 млн, а хотя бы 300

К сообщению приложен файл. Размер - 57Kb
17 сен 15, 13:49    [18160698]     Ответить | Цитировать Сообщить модератору
 Re: узнать пропуски чисел в счётчике  [new]
Erik_Kartmann
Member

Откуда: Пиндырдышханда
Сообщений: 948
спасибо
17 сен 15, 16:24    [18161907]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить