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

Question

此题应该有解。
但是我想了半天也没有想到什么好方法。
如果是在PL/SQL中，可以很容易的用一条SQL写出来的。

Answer 1

我的思路如下：
查阅出max(a)和min(a),然后在(min(a),max(a))这个范围内比较数据表中的数据。但是SQL比较难写，先探讨方法是否可行。

Answer 2

zhujiechang(小朱) :你觉得无聊，但不一定所有人都觉得无聊。
就像这个问题不是我想出来的，但是我在一个地方看到。
就想将其解决，但是现在还没想到好方法。你说这个问题不实际，但我告诉你现实生活中有可能遇到。
而大部分人遇到后一定是会用光标去解决的。

Answer 3

这个问题以前我花时间想过，我是没想出来的。
用PL/SQL怎么写？

Answer 4

select a.a+1 as the_begin,
(select min(a)-1 from test1 where a>a.a+1) the_end 
from test1 a left join test1 b on a.a+1=b.a 
where b.a is null and a.a+1<>(select max(a)+1 from test1)
the_begin   the_end     
----------- ----------- 
3           9
11          999（所影响的行数为 2 行）

Answer 5

select a.a+1 as the_begin,
(select min(a)-1 from test1 where a>a.a+1) the_end 
from test1 a left join test1 b on a.a+1=b.a 
where b.a is null and a.a+1<>(select max(a)+1 from test1)
union 
select 1,(select min(a)-1 from test1 where a>1) 
from test1 
where (select min(a) from test1)<>1

Answer 6

有没有答案了？？我这里有一个：insert test1
select c from 
(
select t1.b+t2.b*10+t3.b*100+t4.b*1000 as c
from
(
select 0 as b
union all
select 1 as b
union all
select 2 as b
union all
select 3 as b
union all
select 4 as b
union all
select 5 as b
union all
select 6 as b
union all
select 7 as b
union all
select 8 as b
union all
select 9 as b
) as t1,
(
select 0 as b
union all
select 1 as b
union all
select 2 as b
union all
select 3 as b
union all
select 4 as b
union all
select 5 as b
union all
select 6 as b
union all
select 7 as b
union all
select 8 as b
union all
select 9 as b
) as t2,
(
select 0 as b
union all
select 1 as b
union all
select 2 as b
union all
select 3 as b
union all
select 4 as b
union all
select 5 as b
union all
select 6 as b
union all
select 7 as b
union all
select 8 as b
union all
select 9 as b
) as t3,
(
select 0 as b
union all
select 1 as b
union all
select 2 as b
union all
select 3 as b
union all
select 4 as b
union all
select 5 as b
union all
select 6 as b
union all
select 7 as b
union all
select 8 as b
union all
select 9 as b
) as t4
) as t5
where c<>0
and c not in (select a from test1)

Answer 7

呵呵，記年去年是小狼大俠(x_zou)解了這道題。後悔當時沒收藏。

Answer 8

http://www.csdn.net/expert/topic/548/548203.xml?temp=.8846399
x_zou在那里说了一个贴好，但是打不开！

Answer 9

Yang_(扬帆破浪)的方法很好哦。记得以前的没有限制条件的，但是打不开那个贴子了，都是改版前的贴子，是不是被删除了？

Answer 10

http://www.csdn.net/Expert/TopicView1.asp?id=474338
这个，有谁知道怎么打开这样的贴子吗？

Answer 11

yang_的应该还加一条件：.....
.....
where c<>0
and c not in (select a from test1) and c <(select max(a) from test1)
order by c

Answer 12

Yang_(扬帆破浪)：是打不开了。
N_chow(一剑飘香++) ： 没错！
不过当时只是求断号的最小值，但要按铁兄的规则在sql里是没法实现的，
oracle里有sequence的概念，所以很容易一句sql搞定。

Answer 13

按钮说的队，我的那个一下给填满了。
还有可以用left join.

Answer 14

想知道ORACLE里怎么搞定的？应该不是用的YANG_的方法，因为YANG_的写法是通用的。
sequence也是要创建才行的啊。

Answer 15

用all_objects取也有限制，不能通用哦~

Answer 16

不过按铁诺的条件，够用了，从那个贴学一招：
select * from 
(select rownum a from all_objects) 
where a > (select min(a) from test1)
and a < (select max(a) from test1)
and a not in (select a from test1);

Answer 17

all_objects算不算是附加的数据库对象？

Answer 18

没想到今天来了后居然有这么多条回复，我本以为只有十来条的。
谢谢大家的关注！在PL/SQL中好处理是因为Oracle中有rownum的存在。
但是如果是一个新的DB的话，用系统表all_objects来处理是不合适的。
因为all_objects中间的纪录数有可能不够，而用ALL_TAB_COLUMNS这个系统表在任何情况下都不要担心数据不够的问题。因此我用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欢迎大家能想出更好的方法。

Answer 19

1.SYSMESSAGES记录不够9999
2.也是速度
还是Yang_那种速度快，且通用，也不会比PL/SQL的慢

Answer 20

我刚才看了上面的回复。
海兄的方法确实好，而且没有用到其它的数据库对象。
并且效率高，虽比PL/SQL的慢些，但也只要1秒多钟。j9988说的确实不错。
最关键的是如果sysmessages没有修改过，那么纪录只有7564，不够9999

Answer 21

海兄的语句如果想在PL/SQL中用，那么必须改成下面的形式：select  c  from    
(  
select  t1.b+t2.b*10+t3.b*100+t4.b*1000    c  
from  
(  
select  0    b  from dual
union  all  
select  1    b  from dual
union  all  
select  2    b  from dual
union  all  
select  3    b  from dual
union  all  
select  4    b  from dual
union  all  
select  5    b  from dual
union  all  
select  6    b  from dual
union  all  
select  7    b  from dual
union  all  
select  8    b  from dual
union  all  
select  9    b  from dual
)    t1,  
(  
select  0    b  from dual
union  all  
select  1    b  from dual
union  all  
select  2    b  from dual
union  all  
select  3    b  from dual
union  all  
select  4    b  from dual
union  all  
select  5    b  from dual
union  all  
select  6    b  from dual
union  all  
select  7    b  from dual
union  all  
select  8    b  from dual
union  all  
select  9    b  from dual
)    t2,  
(  
select  0    b  from dual
union  all  
select  1    b  from dual
union  all  
select  2    b  from dual
union  all  
select  3    b  from dual
union  all  
select  4    b  from dual
union  all  
select  5    b  from dual
union  all  
select  6    b  from dual
union  all  
select  7    b  from dual
union  all  
select  8    b  from dual
union  all  
select  9    b  from dual
)    t3,  
(  
select  0    b  from dual
union  all  
select  1    b  from dual
union  all  
select  2    b  from dual
union  all  
select  3    b  from dual
union  all  
select  4    b  from dual
union  all  
select  5    b  from dual
union  all  
select  6    b  from dual
union  all  
select  7    b  from dual
union  all  
select  8    b  from dual
union  all  
select  9    b  from dual
)    t4  
)    t5  
where  c  <  >0  
and  c  not  in  (select  a  from  test1)  and  c <(select  max(a)  from  test1) 
order  by  c;不过这条语句在Oracle中要花去1202ms
我的那条PL/SQL语句要花去230ms
按钮JJ的那条语句则要花去120ms

Answer 22

Yang_的改成LEFT JOIN 可能要快点

Answer 23

铁斑竹：not  in  改成LEFT JOIN 再试一下

Answer 24

Yang_的语句在PL/SQL中改成外连接形式：
select  c  from    
(  
select  t1.b+t2.b*10+t3.b*100+t4.b*1000    c  
from  
(  
select  0    b  from dual
union  all  
select  1    b  from dual
union  all  
select  2    b  from dual
union  all  
select  3    b  from dual
union  all  
select  4    b  from dual
union  all  
select  5    b  from dual
union  all  
select  6    b  from dual
union  all  
select  7    b  from dual
union  all  
select  8    b  from dual
union  all  
select  9    b  from dual
)    t1,  
(  
select  0    b  from dual
union  all  
select  1    b  from dual
union  all  
select  2    b  from dual
union  all  
select  3    b  from dual
union  all  
select  4    b  from dual
union  all  
select  5    b  from dual
union  all  
select  6    b  from dual
union  all  
select  7    b  from dual
union  all  
select  8    b  from dual
union  all  
select  9    b  from dual
)    t2,  
(  
select  0    b  from dual
union  all  
select  1    b  from dual
union  all  
select  2    b  from dual
union  all  
select  3    b  from dual
union  all  
select  4    b  from dual
union  all  
select  5    b  from dual
union  all  
select  6    b  from dual
union  all  
select  7    b  from dual
union  all  
select  8    b  from dual
union  all  
select  9    b  from dual
)    t3,  
(  
select  0    b  from dual
union  all  
select  1    b  from dual
union  all  
select  2    b  from dual
union  all  
select  3    b  from dual
union  all  
select  4    b  from dual
union  all  
select  5    b  from dual
union  all  
select  6    b  from dual
union  all  
select  7    b  from dual
union  all  
select  8    b  from dual
union  all  
select  9    b  from dual
)    t4  
)    t5  ,test1
where  c  <>0
and c=test1.a(+) and test1.a is null  
and  c <(select  max(a)  from  test1) 
order  by  c;执行时间为821ms，确实要快1/3。
但是由于数据量不大，效果不明显。

Answer 25

学习,这里气份真好.btw: sql有没有类似oracle的rowid的东西？

Answer 26

jlandzpa(欧高黎嘉陈):可惜没有哦。继续想想，若没有9999的限制，可不可以做出来？

Answer 27

我不懂sql,我来这主要是学习一些思路的.

Answer 28

在oracle中，all_objects里的记录有几千,如果嫌小，可以自关联三次,
那就有几亿了.

Answer 29

jlandzpa：关联一次就比较慢了。
关联两次就比较够呛了。
关联三次还得了？