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得到的记录集如下:

image

二:如果这个查询语句加上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'

得到的结果如下图所示:

image

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

image

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

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]

小雪转中雪

12 Comments On distinct和rownum使用

  1. avatar

    感谢您的帮助后!我也不会获得这样的,否则!

  2. avatar

    感谢伟大的信息!我也不会发现这一点,否则!

  3. avatar

    what I was looking for, thanks

  4. avatar

    看是看明白了,突然来一篇技术日志,有点不适应

    • avatar

      @北山 @北山
      SQL谈不上是什么技术啦,稍微涉及到数据的玩意,一般都会用SQL。
      所以说SQL应该想普通话一样普及了,哈哈。

  5. avatar

    看的很是迷糊

  6. avatar

    天书啊,我还是打酱油去吧

  7. avatar

    你都用oracle了
    我还在用access、SQL、MySQL

  8. avatar

    sql跟代码区别还是很大的,属于第四代语言,简单的一腿的。

  9. avatar

    我先占个沙发吧,代码的我是看不懂

| 真的AJAX提交哦

发表评论