sql查询在范围内数据,若数据丢失,要挑选出来

[wenchuan408]

表A (start number, end number)=>是设立的一个范围的表格
(1 1000)

表B (number) =>记录了在表A 范围内(1~1000) 里面的数据集合,但是有可能只记录了998个
1
2
5
6
....1000

现在B表格里面是有998个数据,但是A表格是1000个数据范围,
现在要找出缺失的这两个数据,并将缺失的数据储存在一个临时表中,如上表缺失的是3和4


SQL 怎么写啊,请大神帮忙啊,万分感谢!

[二月十六]

不知道能不能满足需求，这个范围只能到1-2047

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([startnumber] int,[endnumber] int)
Insert #A
select 1,10
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([number] int)
Insert #B
select 1 union all
select 2 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
Go
--测试数据结束
SELECT  number
FROM    master.dbo.spt_values
WHERE   type = 'P'
        AND number BETWEEN ( SELECT startnumber
                             FROM   #A
                           )
                   AND     ( SELECT endnumber
                             FROM   #A
                           )
        AND number NOT IN ( SELECT  number
                            FROM    #B )
                       

[wenchuan408]

这个范围只能到1-2047 =>为什么啊 ,我最大的范围不超过10万,有没有其它语句可以做到啊,谢谢！

[二月十六]

这样应该可以，但是觉得好像有点绕写的，先凑活着用，我再看看：

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([startnumber] int,[endnumber] int)
Insert #A
select 1,20
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([number] int)
Insert #B
select 1 union all
select 2 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
Go
--测试数据结束
;WITH cte AS (
SELECT startnumber AS number FROM #A
UNION ALL
SELECT cte.number+1 FROM #A JOIN cte ON cte.number<#A.endnumber
)
SELECT  number
FROM    cte
WHERE    number NOT IN ( SELECT  number
                            FROM    #B )
                       

[wenchuan408]

大神怎么范围只能1~100啊,我这个范围有点儿大哦,最起码要1~10万哦,还有没有其它方法啊,在线等啊.

引用 3 楼 sinat_28984567 的回复:

这样应该可以，但是觉得好像有点绕写的，先凑活着用，我再看看：

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([startnumber] int,[endnumber] int)
Insert #A
select 1,20
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([number] int)
Insert #B
select 1 union all
select 2 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
Go
--测试数据结束
;WITH cte AS (
SELECT startnumber AS number FROM #A
UNION ALL
SELECT cte.number+1 FROM #A JOIN cte ON cte.number<#A.endnumber
)
SELECT  number
FROM    cte
WHERE    number NOT IN ( SELECT  number
                            FROM    #B )
                       

[二月十六]

不好意思，忘加限制了：

;WITH cte AS (
SELECT startnumber AS number FROM #A
UNION ALL
SELECT cte.number+1 FROM #A JOIN cte ON cte.number<#A.endnumber
)
SELECT  number
FROM    cte 
WHERE    number NOT IN ( SELECT  number
                            FROM    #B )
							OPTION(MAXRECURSION 0) 

12万