编程论坛 → SQL Server论坛

SQL 查询某缺失数值

kaijin1115 发布于 2012-01-31 15:32， 593 次点击

有一个字段的值是按数值 10001-20000递增的即10001,10002,10003-19999,20000。需查询 15000-20000中间缺失的数值。

4 回复

cnfarer2012-02-01 11:01

在这里:
http://

cnfarer2012-02-01 11:16

WITH t AS
(
    SELECT 15000 AS num
    UNION ALL
    SELECT num+1
    FROM t
    WHERE num<=20000
)
SELECT * FROM t where num not in (select id from xxxx)
OPTION(MAXRECURSION 0)

png2012-02-02 00:23

可以先做一个含15000 - 20000的临时表, 然后外连接.

create table #seq_num(id int)            -- 临时表

declare @seq int
set     @seq = 15000

while @seq < 20001
begin
   insert into #seq_num values(@seq)     -- 临时表赋值
   set         @seq = @seq + 1
end

select    a.*
from      #seq_num a left join YourTB b
          on a.id = b.id
where     b.id is null

drop table #seq_num

kaijin11152012-02-02 19:35

谢谢可以了