首先,要明白rownum的真正含义。rownum是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数.oracle在select出一条记录后便加上一个rownum, 而不等所有的结果都select出来后再加上rownum。
然后是distinct的工作原理。distinct是等记录集全部查询出后,然后进行排序,最后再删除重复数据,而不是边查边删除重复数据,因为这是不切实际的,毕竟又太多的位置数据。
有了对oracle这两个关键词的准确理解后,就不难弄清楚为什么,当distinct和rownum一起使用的时候就得不到想要的记录条数了,具体现象还是举例子说明吧,语言可能有点空洞。
一:先来看下面sql查询出的记录
[sql]select a.access_id, a.link_id
from asn_access a, asn_res_link b
where a.access_id = '000000000000000000035696'
and a.link_id = b.link_id
and a.delete_state = '0'
and b.delete_state = '0'[/sql]
这个SQL得到的记录集如下:
二:如果这个查询语句加上district的话,我想大家都知道,在oracle会返回两条记录:
[sql]select distinct a.access_id, a.link_id
from asn_access a, asn_res_link b
where a.access_id = '000000000000000000035696'
and a.link_id = b.link_id
and a.delete_state = '0'
and b.delete_state = '0'[/sql]
得到的结果如下图所示:
三:最后,如果再对这个sql进一步加上rownum限制条件的话,可能就有人不理解为什么得到的结果不是预期估计结果,比如,我们加上rownum<3,可能会觉得应该返回两条结果:
[sql]select distinct a.access_id, a.link_id
from asn_access a, asn_res_link b
where a.access_id = '000000000000000000035696'
and a.link_id = b.link_id
and a.delete_state = '0'
and b.delete_state = '0'
and rownum < 3[/sql]
这个sql运行的结果确实只有一条记录,呵呵,为什么会这样,我想如果你对刚开始讲的那两点理解的话,对这个结果也就不会感到意外了:
只要稍微分析一下就知道为什么会出现这样的结果了。
1.首先rownum对查询出来的4条记录标号,第一条为1,第二条为2,到第三条的时候因为rownum<3条件为false,查询停止;
2.然后distinct开始工作,因为第1,2条记录重复,所以删除一条(具体是删除哪条就不太清楚了,但不是标号为1的被删除就是标号为2的被删除),这样一来,就只剩一条记录,满足rownum<3的记录就只有一条了;
如此一分析,我想就很清楚了,所以oracle的distinct和rownum一般不会这么使用。如果想得到不重复的两条记录一般都是采取子查询的方式,即,先distinct返回结果,然后在rownum限制一下:
[sql]select c.access_id, c.link_id
from (select distinct a.access_id, a.link_id
from asn_access a, asn_res_link b
where a.access_id = '000000000000000000035696'
and a.link_id = b.link_id
and a.delete_state = '0'
and b.delete_state = '0') c
where and rownum < 3[/sql]