一个T-SQL查询的问题！有点难但是很有意思！欢迎大家前来探讨……

Arrayalex_cs

表结构如下：
Create table Test1 (a int not null primary key)

初始记录示例如下（数字可能是变化的，但是最大为4位数即9999）：
insert into test1 values (1)
insert into test1 values (2)
insert into test1 values (10)
insert into test1 values (1000)

任务：该表中本应该存储连续的自然数，要求使用T-SQL找出丢失的数字。

规则：不允许创建和使用任何附加的数据库对象，并且不允许使用动态产生的SQL语句，即SQL应该是静态文本，并且不许使用游标和循环，最好能用一个语句实现！

欢迎大家前来探讨……:rolleyes:

Arrayalex_cs

不允许创建任何附加的数据库对象！当然包括存储过程！

zhuzhichao

擂主，我來攻擂了。　　

這個問題在PL/SQL中非常的好處理
我用PL/SQL处理这个问题的方法是：
select a.r from
(select rownum r from ALL_TAB_COLUMNS where rownum < 10000) a,test1 b
where a.r=b.a(+) and a.r <=(select max(a) from test1) and b.a is null;
这条语句非常的快，1秒中都不要就可以出来正确的结果。

但是在T-SQL中，我只能想出一条比较笨的语句，这条语句要执行1分钟才能有正确的结果：
select a.r from
(select (select count(*) from master.dbo.sysmessages bb where bb.description < aa.description) + 1 r
from master.dbo.sysmessages aa ) a left outer join test1 b on a.r = b.a
where a.r <=(select max(a) from test1) and b.a is null order by a.r

alex_cs，希望你的方法比我好。

zhuzhichao

select  c  from   
(  
select  t1.b+t2.b*10+t3.b*100+t4.b*1000    c  
from  
(  
select  0    b  
union  all  
select  1    b  
union  all  
select  2    b  
union  all  
select  3    b  
union  all  
select  4    b  
union  all  
select  5    b  
union  all  
select  6    b  
union  all  
select  7    b  
union  all  
select  8    b  
union  all  
select  9    b  
)    t1,  
(  
select  0    b  
union  all  
select  1    b  
union  all  
select  2    b  
union  all  
select  3    b  
union  all  
select  4    b  
union  all  
select  5    b  
union  all  
select  6    b  
union  all  
select  7    b  
union  all  
select  8    b  
union  all  
select  9    b  
)    t2,  
(  
select  0    b  
union  all  
select  1    b  
union  all  
select  2    b  
union  all  
select  3    b  
union  all  
select  4    b  
union  all  
select  5    b  
union  all  
select  6    b  
union  all  
select  7    b  
union  all  
select  8    b  
union  all  
select  9    b  
)    t3,  
(  
select  0    b  
union  all  
select  1    b  
union  all  
select  2    b  
union  all  
select  3    b  
union  all  
select  4    b  
union  all  
select  5    b  
union  all  
select  6    b  
union  all  
select  7    b  
union  all  
select  8    b  
union  all  
select  9    b  
)    t4  
)    t5  
where  c  <  >0  
and  c  not  in  (select  a  from  test1)  and  c <(select  max(a)  from  test1)
order  by  c[/COLOR]


這條語句只要1200毫秒，如果改成外連接形式那麼只要800毫秒。
[/COLOR]

lsh66

这有点儿意思，大家都来想一想。。。小三冥思苦想中

Arrayalex_cs

你写的那个联接的SQL挺高明，佩服！佩服！

zhuzhichao

to  alex_cs ：
謝謝，能不能告訴我你的方法。

Arrayalex_cs

我的思路和你的有点儿类似，只不过是采用二进制，你的用十进制。

用十位二进制数，14位二进制数，可以满足上面要求：

select num
from (
select
(bit0 * 1 +
bit1 * 2 +
bit2 * 4 +
bit3 * 8 +
bit4 * 16 +
bit5 * 32 +
bit6 * 64 +
bit7 * 128 +
bit8 * 256 +
bit9 * 512 +
bit10 * 1024 +
bit11 * 2048 +
bit12 * 4096 +
bit13 * 8192 + 1) as num
from
(select 0 as bit0 union select 1) as bits0
cross join
(select 0 as bit1 union select 1) as bits1
cross join
(select 0 as bit2 union select 1) as bits2
cross join
(select 0 as bit3 union select 1) as bits3
cross join
(select 0 as bit4 union select 1) as bits4
cross join
(select 0 as bit5 union select 1) as bits5
cross join
(select 0 as bit6 union select 1) as bits6
cross join
(select 0 as bit7 union select 1) as bits7
cross join
(select 0 as bit8 union select 1) as bits8
cross join
(select 0 as bit9 union select 1) as bits9
cross join
(select 0 as bit10 union select 1) as bits10
cross join
(select 0 as bit11 union select 1) as bits11
cross join
(select 0 as bit12 union select 1) as bits12
cross join
(select 0 as bit13 union select 1) as bits13 ) as bits

where num between (select min(a) from test1) and (select max(a) from test1)
and not exists (select * from test1 where a=num)
order by num

stroller

同样希望ZHUZHICHAO不吝指教