一条SQL语句

[Lovely_baby]

表（IDTable），只存在ID一列，现有数据（1, 2, 5, 7）
用一条SQL语句，查找断层（3，6）

[子陌红尘]

--不考虑起始号码断号的情况：
declare @t table(id int)
insert into @t select 1 union select 2 union select 5 union select 7

select
    rtrim(a.ID)+case when min(b.ID)=a.ID then '' else '-'+rtrim(min(b.ID)) end 断号区间
from
    (select ID+1 as ID from @t t where not exists(select 1 from @t where ID=t.ID+1)) a,
    (select ID-1 as ID from @t t where not exists(select 1 from @t where ID=t.ID-1)) b
where
    a.ID<=b.ID
group by
    a.ID

/*
断号区间                      
------------------------- 
3-4
6
*/

[Lovely_baby]

谢谢~~我得研究下 嘿嘿

[wangying110166]

declare @told table(id int)
insert into @told select 1 union select 2 union select 5 union select 7

declare @lastnum int
select top 1 @lastnum=id from @told order by id desc

declare @tall table(id int)


while(@lastnum>0)
begin
insert into @tall (id) values(@lastnum)
set @lastnum=@lastnum-1
end

select * from @tall where id not in (select id from @told) order by id

[shuxuzui]

没看懂！

[a15208312800]

有意思

[LONGZHEZHILONG]

不错

[sky_too_sky]

存储过程.我都快晕死了

[ilovey4]

博大精深