凌晨四点的蓝

distinct和rownum使用

首先，要明白rownum的真正含义。rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1,第二行是2,依此类推，这个伪字段可以用于限制查询返回的总行数.oracle在select出一条记录后便加上一个rownum,   而不等所有的结果都select出来后再加上rownum。

然后是distinct的工作原理。distinct是等记录集全部查询出后，然后进行排序，最后再删除重复数据，而不是边查边删除重复数据，因为这是不切实际的，毕竟又太多的位置数据。

有了对oracle这两个关键词的准确理解后，就不难弄清楚为什么，当distinct和rownum一起使用的时候就得不到想要的记录条数了，具体现象还是举例子说明吧，语言可能有点空洞。

一:先来看下面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得到的记录集如下：

二:如果这个查询语句加上district的话，我想大家都知道，在oracle会返回两条记录：

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进一步加上rownum限制条件的话，可能就有人不理解为什么得到的结果不是预期估计结果，比如，我们加上rownum<3，可能会觉得应该返回两条结果：

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运行的结果确实只有一条记录，呵呵，为什么会这样，我想如果你对刚开始讲的那两点理解的话，对这个结果也就不会感到意外了：

只要稍微分析一下就知道为什么会出现这样的结果了。

1.首先rownum对查询出来的4条记录标号，第一条为1，第二条为2，到第三条的时候因为rownum<3条件为false，查询停止；

2.然后distinct开始工作，因为第1，2条记录重复，所以删除一条(具体是删除哪条就不太清楚了，但不是标号为1的被删除就是标号为2的被删除)，这样一来，就只剩一条记录，满足rownum<3的记录就只有一条了；

如此一分析，我想就很清楚了，所以oracle的distinct和rownum一般不会这么使用。如果想得到不重复的两条记录一般都是采取子查询的方式，即，先distinct返回结果，然后在rownum限制一下：

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