怎么找出连续数字中的缺少的数字?

Question

怎么找出连续数字中的缺少的数字?
例如:
在表tmp_shz_test中id_begin代表开始的数字,id_end代表结束的数字,
number代表id_begin 和 id_end之间的任意数字,怎么找出缺少的数字?create table tmp_shz_test
(
id_begin number(20) ,
id_end number(20) ,
num number(20));insert into tmp_shz_test values (1,100, 1);
insert into tmp_shz_test values (1,100, 2);
insert into tmp_shz_test values (1,100, 4);
insert into tmp_shz_test values (1,100, 20);
insert into tmp_shz_test values (1,100, 38);
insert into tmp_shz_test values (1,100, 67);
insert into tmp_shz_test values (1,100, 80);

Answer 1

--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>a.col2 
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
-1
FROM(
SELECT col1,col2 FROM tb
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          1           1
a          4           5
b          2           4
--*/

Answer 2

create table tmp_shz_test
(
id_begin int ,
id_end int ,
num int);
goinsert into tmp_shz_test values (1,100, 1);
insert into tmp_shz_test values (1,100, 2);
insert into tmp_shz_test values (1,100, 4);
insert into tmp_shz_test values (1,100, 20);
insert into tmp_shz_test values (1,100, 38);
insert into tmp_shz_test values (1,100, 67);
insert into tmp_shz_test values (1,100, 80);
insert into tmp_shz_test values (105,200, 110);
godeclare @cnt int
select @cnt=max(id_end) from tmp_shz_test
set rowcount @cnt
select identity(int) id into # from sysobjects,syscolumnsselect * from # a where id not in(select num from tmp_shz_test) 
and exists(select 1 from tmp_shz_test where a.id between id_begin and id_end)go
drop table #
drop table tmp_shz_test
go