奇怪的SQL语句问题！紧急求助！

Question

XMDM
--------------------------------------------------
WN-2000-00011
WN-2001-00005
WN-2001-00006
WN-2001-00007
WN-2001-00009
WN-2002-00001
WN-2002-00012
XA-2003-00002
XA-2003-00003
XA-2003-00004
表中由上列数据查询语句一:SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=0)
结果一:XMDM
--------------------------------------------------
WN-2002-00001
WN-2000-00011
查询语句二:SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=2)结果二:XMDM
--------------------------------------------------
WN-2002-00001
XA-2003-00004
查询语句三:SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=4)结果三:XMDM
--------------------------------------------------
WN-2002-00001
XA-2003-00004
不管怎么查里面都有重复的.加了order by也一样！求教达人！

Answer 1

还有按说语句一查出来的应该是
XMDM
--------------------------------------------------
WN-2000-00011
WN-2001-00005不知道怎么就出来了
XMDM
--------------------------------------------------
WN-2002-00001
WN-2000-00011

Answer 2

什么意思？什么重复？
我只看到rownum<=2出来的不是前面两条。

Answer 3

没有问题啊，看我下面执行的SQL> create table GZ_XMXX
  2  (
  3    XMDM VARCHAR2(20)
  4  )
  5  ;Table createdSQL> 
SQL> insert into GZ_XMXX (XMDM)
  2  values ('WN-2000-00011');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('WN-2001-00005');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('WN-2001-00006');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('WN-2001-00007');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('WN-2001-00009');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('WN-2002-00001');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('WN-2002-00012');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('XA-2003-00002');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('XA-2003-00003');1 row insertedSQL> insert into GZ_XMXX (XMDM)
  2  values ('XA-2003-00004');1 row insertedSQL> commit;Commit completeSQL> SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=0);XMDM
--------------------
WN-2000-00011
WN-2001-00005SQL> SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=2);XMDM
--------------------
WN-2001-00006
WN-2001-00007SQL> SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=4);XMDM
--------------------
WN-2001-00009
WN-2002-00001

Answer 4

出来的不是指定的数据,胡乱跳行.
结果1，2，3里都有WN-2002-00001这条数据，数据库中这条数据只有一条。不光是这条数据，其他的数据也这样！
Visual_Studio_Net(打鼠英雄) 老兄，我这里跳行的！

Answer 5

用group by 可以去除掉吧

Answer 6

这是跳行问题，用group by可以么？

Answer 7

忘了说了，xmdm是主键，跟这个有关系么？

Answer 8

我想是不是因为rownum出来的序号是按照数据库物理保存的顺序来编排的，与后面增加的group by、order by是不相关的。
Visual_Studio_Net(打鼠英雄) 在插入数据库时是按照固定顺序的，我想楼主的记录在数据库保存的顺序可能不是你看到的顺序的原因引起的。仅仅抛砖引玉，以供大家参考。

Answer 9

建议楼主根据ROWID排序看看实际记录在物理表中的保存顺序。

Answer 10

用rowid排序取出来的是
ROWID                  ROWNUM XMDM
------------------ ---------- --------------------------------------------------
AAAH3lAALAAAAmoAAA          7 WN-2002-00012
AAAH3lAALAAAAmoAAB          3 WN-2001-00006
AAAH3lAALAAAAmoAAC          4 WN-2001-00007
AAAH3lAALAAAAmoAAD          5 WN-2001-00009
AAAH3lAALAAAAmoAAE          6 WN-2002-00001
AAAH3lAALAAAAmoAAF          2 WN-2001-00005
AAAH3lAALAAAAmoAAG         10 XA-2003-00004
AAAH3lAALAAAAmoAAH          1 WN-2000-00011
AAAH3lAALAAAAmoAAI          9 XA-2003-00003
AAAH3lAALAAAAmoAAJ          8 XA-2003-00002

Answer 11

增加order by试试！
查询语句一:
SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=0 order by xmdm ) order by xmdm 查询语句二:
SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=2 order by xmdm ) order by xmdm 
查询语句三:
SELECT xmdm FROM GZ_XMXX WHERE ROWNUM<=2 AND XMDM NOT IN(SELECT XMDM FROM GZ_XMXX WHERE ROWNUM<=4 order by xmdm ) order by xmdm

Answer 12

紧急求助呀！实在不明白这是什么意思!
AAAH3lAALAAAAmoAAH          1 WN-2000-00011
AAAH3lAALAAAAmoAAF          2 WN-2001-00005
AAAH3lAALAAAAmoAAB          3 WN-2001-00006
AAAH3lAALAAAAmoAAC          4 WN-2001-00007
AAAH3lAALAAAAmoAAD          5 WN-2001-00009
AAAH3lAALAAAAmoAAE          6 WN-2002-00001
AAAH3lAALAAAAmoAAA          7 WN-2002-00012
AAAH3lAALAAAAmoAAJ          8 XA-2003-00002
AAAH3lAALAAAAmoAAI          9 XA-2003-00003
AAAH3lAALAAAAmoAAG         10 XA-2003-00004
SQL> SELECT rowid,rownum,a.xmdm FROM GZ_XMXX a WHERE xmdm not in(select xmdm FROM GZ_XMXX where rownum<=6)order by xmdm;ROWID                  ROWNUM XMDM
------------------ ---------- --------------------------------------------------
AAAH3lAALAAAAmoAAA          3 WN-2002-00012
AAAH3lAALAAAAmoAAJ          2 XA-2003-00002
AAAH3lAALAAAAmoAAI          4 XA-2003-00003
AAAH3lAALAAAAmoAAG          1 XA-2003-00004SQL> SELECT rowid,rownum,a.xmdm FROM GZ_XMXX a WHERE xmdm not in(select xmdm FROM GZ_XMXX where rownum<=4)order by xmdm;      
ROWID                  ROWNUM XMDM
------------------ ---------- --------------------------------------------------
AAAH3lAALAAAAmoAAD          5 WN-2001-00009
AAAH3lAALAAAAmoAAE          1 WN-2002-00001
AAAH3lAALAAAAmoAAA          4 WN-2002-00012
AAAH3lAALAAAAmoAAJ          3 XA-2003-00002
AAAH3lAALAAAAmoAAI          6 XA-2003-00003
AAAH3lAALAAAAmoAAG          2 XA-2003-00004
rownum跳得实在太厉害了吧!

Answer 13

yuyu1980() 老兄，ORDER BY不能用在子查询里，否则报没有右括号错误！

Answer 14

select rowid,rownum,a.xmdm from GZ_XMXX a order by rowid;
ROWID                  ROWNUM XMDM
------------------ ---------- --------------------------------------------------
AAAH4XAALAAAAmkAAA         22 WN-2002-00012
AAAH4XAALAAAAmkAAB         18 WN-2001-00006
AAAH4XAALAAAAmkAAC         19 WN-2001-00007
AAAH4XAALAAAAmkAAD         20 WN-2001-00009
AAAH4XAALAAAAmkAAE         21 WN-2002-00001
AAAH4XAALAAAAmkAAF         17 WN-2001-00005
AAAH4XAALAAAAmkAAG         27 XA-2003-00004
AAAH4XAALAAAAmkAAH         16 WN-2000-00011
AAAH4XAALAAAAmkAAI         26 XA-2003-00003
AAAH4XAALAAAAmkAAJ         25 XA-2003-00002
AAAH4XAALAAAAmkAAK         23 WN-TGXYGDZ-04ROWID                  ROWNUM XMDM
------------------ ---------- --------------------------------------------------
AAAH4XAALAAAAmkAAL         32 XGD-GXNDZCL-2002-07
AAAH4XAALAAAAmkAAM          5 HY-YZDG-2002--03
AAAH4XAALAAAAmkAAN         12 SX-XY-2001-12
AAAH4XAALAAAAmkAAO         11 SX-TJB-2004-14
AAAH4XAALAAAAmkAAP          8 SX-JCY-2004-15
AAAH4XAALAAAAmlAAA         29 XA-JZKJDX-2004-17
AAAH4XAALAAAAmlAAB         14 TC-ZXXWFGZ-2001-09
AAAH4XAALAAAAmlAAC          7 SL-ZJRMFY-2003-13
AAAH4XAALAAAAmlAAD          6 HZ-MPJS-2003-02
AAAH4XAALAAAAmlAAF          9 SX-RMYY-2004-18
AAAH4XAALAAAAmnAAA         34 XY-YMW-2001-20ROWID                  ROWNUM XMDM
------------------ ---------- --------------------------------------------------
AAAH4XAALAAAAmnAAB         33 XY-WLY-2001-22
AAAH4XAALAAAAmoAAA          1 200511012
AAAH4XAALAAAAmoAAB          3 BJ-CCGSGC-2003-06
AAAH4XAALAAAAmoAAE         28 XA-DBYYGKJ-2005-19
AAAH4XAALAAAAmoAAF         30 XA-LGDX-2003-11
AAAH4XAALAAAAmoAAG         31 XA-ZXXWF-2002-01
AAAH4XAALAAAAmoAAH         24 WN-YGDZ-2001-2003-08
AAAH4XAALAAAAmoAAJ         13 SY-GLJS-05
AAAH4XAALAAAAmoAAK         15 TC-ZXXWFGZ-2002-09
AAAH4XAALAAAAmoAAL          4 HY-2002--03
AAAH4XAALAAAAmoAAN         10 SX-SL-2003-12ROWID                  ROWNUM XMDM
------------------ ---------- --------------------------------------------------
AAAH4XAALAAAAmoAAO          2 AK-MPJS-2000-10select rowid,rownum,a.dgid from GZ_JCGZDG a order by rowid;ROWID                  ROWNUM       DGID
------------------ ---------- ----------
AAAH4EAALAAAABsAAA          1        262
AAAH4EAALAAAABsAAB          2        186
AAAH4EAALAAAABsAAC          3        189
AAAH4EAALAAAABsAAD          4        190
AAAH4EAALAAAABsAAE          5        188
AAAH4EAALAAAABsAAF          6        191
AAAH4EAALAAAABsAAG          7        192
AAAH4EAALAAAABsAAH          8        193
AAAH4EAALAAAABsAAI          9        201
AAAH4EAALAAAABsAAJ         10        200
AAAH4EAALAAAABsAAK         11        199ROWID                  ROWNUM       DGID
------------------ ---------- ----------
AAAH4EAALAAAABsAAL         12        181
AAAH4EAALAAAABsAAM         13        182
AAAH4EAALAAAABsAAN         14        183
AAAH4EAALAAAABsAAO         15        187
AAAH4EAALAAAABsAAP         16        198
AAAH4EAALAAAABsAAR         17        285
AAAH4EAALAAAABsAAS         18        286
AAAH4EAALAAAABsAAT         19        297
AAAH4EAALAAAABsAAU         20        298
AAAH4EAALAAAABsAAV         21        306
AAAH4EAALAAAABsAAW         22        307ROWID                  ROWNUM       DGID
------------------ ---------- ----------
AAAH4EAALAAAABtAAB         23        299
AAAH4EAALAAAABtAAC         24        300
AAAH4EAALAAAABtAAD         25        301
AAAH4EAALAAAABuAAA         26        287
AAAH4EAALAAAABwAAB         27        303
AAAH4EAALAAAABwAAC         28        283
AAAH4EAALAAAABwAAD         29        293
AAAH4EAALAAAABwAAE         30        304
AAAH4EAALAAAABwAAF         31        296
AAAH4EAALAAAABwAAG         32        302
AAAH4EAALAAAABwAAH         33        305
对比很明显,GZ_XMXX里的ROWNUM已经乱了,有什么办法解决!谢谢!