select top 10 a=identity(int,1,1) into #test from sysobjects a,sysobjects b declare @i int select @i=max([id]) from dbo.tabselect test.a from #test as test left join dbo.tab on a=[id] where a<=@i and [id] is null
Create table #tb (id int) insert #tb(id)Values(1) insert #tb(id)Values(2) insert #tb(id)Values(4) insert #tb(id)Values(5) insert #tb(id)Values(7) insert #tb(id)Values(9)declare @str varchar(1000) set @str='('declare @int int set @int=1select @str=case when @int=id then @str else @str+convert(varchar,@int)+',' end, @int=@int+case when @int=id then 1 else 2 end from #tb order by idset @str=left(@str,len(@str)-1)+')' print @strdrop table #tb --执行结果 --(3,6,8)
declare @tab table(id int) insert into @tab select 1 insert into @tab select 2 insert into @tab select 4 insert into @tab select 5 insert into @tab select 7select case when (a.id+1)=min(b.id)-1 then rtrim(a.id+1) else rtrim(a.id+1)+'-'+rtrim(min(b.id)-1) end as 断号区间 from (select * from @tab m where not exists(select 1 from @tab where id=m.id+1)) a, (select * from @tab m where not exists(select 1 from @tab where id=m.id-1)) b where a.id<b.id group by a.id/* 断号区间 ------------ 3 6 */
declare @tab table(id int) insert @tab select 1 union all select 2 union all select 4 union all select 5 union all select 7select id=a.id+1 from @tab b join @tab a on b.id-1=a.id+1 where not exists(select 1 from @tab where id=b.id-1) and not exists(select 1 from @tab where id=a.id+1)(5 行受影响) id ----------- 3 6(2 行受影响)
declare @tab table(id int) insert @tab select 1 union all select 2 union all select 4 union all select 5 union all select 7select [间隔断号区]=rtrim(a.id)+'——'+rtrim(b.id) from @tab b join @tab a on b.id-1=a.id+1 where not exists(select 1 from @tab where id=b.id-1) and not exists(select 1 from @tab where id=a.id+1)间隔断号区 ---------------------------- 2——4 5——7(2 行受影响)
declare @i int
select @i=max([id]) from dbo.tabselect test.a from #test as test left join dbo.tab on a=[id]
where a<=@i and [id] is null
insert #tb(id)Values(1)
insert #tb(id)Values(2)
insert #tb(id)Values(4)
insert #tb(id)Values(5)
insert #tb(id)Values(7)
insert #tb(id)Values(9)declare @str varchar(1000)
set @str='('declare @int int
set @int=1select @str=case when @int=id then @str else @str+convert(varchar,@int)+',' end,
@int=@int+case when @int=id then 1 else 2 end
from #tb order by idset @str=left(@str,len(@str)-1)+')'
print @strdrop table #tb
--执行结果
--(3,6,8)
insert into @tab select 1
insert into @tab select 2
insert into @tab select 4
insert into @tab select 5
insert into @tab select 7select
case when (a.id+1)=min(b.id)-1 then rtrim(a.id+1) else rtrim(a.id+1)+'-'+rtrim(min(b.id)-1) end as 断号区间
from
(select * from @tab m where not exists(select 1 from @tab where id=m.id+1)) a,
(select * from @tab m where not exists(select 1 from @tab where id=m.id-1)) b
where
a.id<b.id
group by
a.id/*
断号区间
------------
3
6
*/
insert @tab select 1
union all select 2
union all select 4
union all select 5
union all select 7select id=a.id+1 from @tab b join @tab a on b.id-1=a.id+1
where not exists(select 1 from @tab where id=b.id-1)
and
not exists(select 1 from @tab where id=a.id+1)(5 行受影响)
id
-----------
3
6(2 行受影响)
insert @tab select 1
union all select 2
union all select 4
union all select 5
union all select 7select [间隔断号区]=rtrim(a.id)+'——'+rtrim(b.id)
from @tab b join @tab a on b.id-1=a.id+1
where not exists(select 1 from @tab where id=b.id-1)
and
not exists(select 1 from @tab where id=a.id+1)间隔断号区
----------------------------
2——4
5——7(2 行受影响)
print"(3,6)"
哈哈
------------------------------
呼呼