微软BI开拓者

找到序列中的缺号

--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址

用三种不同的格式返回缺号：

第一种：
1-7
10-12
12-20
20-25
25-45
45-60
第二种：
2
3
4
5
6
11
...

第三种：
2,3,4,5,6,11...

产生相应表和数据的脚本：
create table #nos (i int)
insert #nos select 1
insert #nos select 7
insert #nos select 8
insert #nos select 9
insert #nos select 10
insert #nos select 12
insert #nos select 20
insert #nos select 25
insert #nos select 45
insert #nos select 60
给出第一种形式的结果：
select convert(varchar(10),imin.i) + ' - ' +  convert(varchar(10),
(select min(i) from (select i = i from #nos where not exists (select * from #nos a2 where #nos.i-1 =
a2.i) and #nos.i <> (select min(i) from #nos)) as imax where imax.i > imin.i))
from
(select i = i from #nos where not exists (select * from #nos a2 where #nos.i+1 = a2.i) and #nos.i <>
(select max(i) from #nos)) as imin
也可以用：
select convert(varchar(10), t1.i) + '-' +  convert(varchar(10),t3.i)
from #nos t1
left join #nos t2
on t1.i = t2.i-1
join #nos t3
on t3.i = (select min(t4.i) from #nos t4 where t4.i > t1.i)
where t2.i is null

给出第二种形式的结果：

用一条语句完成
这需要一个合适的表去连接到最大的上限：
select ints.i
from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6
) as ints left outer join #nos on ints.i = #nos.i
where #nos.i is null
and ints.i <= 60 and ints.i <> 0
order by ints.i

--译者注：i1、i2……i6的字段 i 相加的值应该比原始表的最大序列还大为止，如果小的话，继续添加表i7、i8，注意规律

用循环语句完成：
创建一个临时表
create table #a (i int)
declare @i int
select @i = 0
while @i < (select max(i) from #nos)
begin
set @i = @i + 1
if not exists (select * from #nos where i = @i)
insert #a select @i
end
select * from #a

第三种形式：
declare @s varchar(1000)
select @s = coalesce(@s+',' + convert(varchar(10),i),convert(varchar(10),i)) from #a --from (select top 100 percent i from #a order by i) as a
select @s

感觉那个循环比较容易理解！

dingding!

SQL SERVER中缺少ORACLE中CONNECT BY那样的语句，所以第二种方法要构造CONNECT BY 的格式比较累:default6: