Oracle查找第n个最大值
来源:互联网 发布:nginx 用户名密码 编辑:程序博客网 时间:2024/06/09 22:41
在国内外许多Oracle DBA站点上,有很多查找表中第n个最大值的小技巧,这些语句确实相当巧妙,它们都独辟蹊径地用到了Oracle表中的一个伪字段“level”。但是在很多情况下,这个语句变成了 美丽的陷阱。如果你想知道为什么,并怎样绕过它,请听我道来!
研究了几天,我发现一种衍生算法,即找出第n个和第m个最大值的差(如下):
列表一:
select max(salary) from empdetails
where level = &n
connect by prior salary>salary
start with salary = ( select max(salary) from empdetails);
此后又发现如下算法,利用Oracle的伪字段level,从最大值开始,按值递减顺序,把记录逐步分层,最后找出第n个最大值和第m个最大值之间的差值。
列表二:
select a.nmax_salary – b.mmax_salary from
(select max(salary) from empdetails
where level = &n
connect by prior salary > salary
start with salary = (select (max(salary)) from empdetails)
) a ,
(select max(salary) mmax_salary from empdetails
where level = &m
connect by prior salary > salary
start with salary = (select (max(salary)) from empdetails)
) b ;
我为此算法的新奇赞叹了很久,值到一天我想利用这个方法时,才发现这个方法并不如想象中那么美妙。
我要从60多个不同值中找出第n个最大值时,输入此语句后,发现如同石沉大海、杳无音讯,屏幕上只有“Statement is running”的提示。为了了解Oracle是怎样分层的,我输入如下语句:
列表三:
select lpad(‘ ‘,level)||salary from empdetails
connect by prior salary > salary
start with salary = (select (max(salary)) from empdetails) ;
发现屏幕在不断输出结果,足足等了十多分钟,还在不停地滚动,我意识到这算法存在某些问题。
试着分析一下列表三的结果,发现这个语句的回送记录数与库中不同数值的个数是指数增长,其公式为 RecordsRe = 2Rdeferent_value – 1 ,要在60多个不同值中用该算法找出第n个最大值,不知要花多长时间,即使机器不停计算几百年也不会得到结果。
还有其它方法吗?当然有,比如定义个游标,自己作一个计数器,用一段script来完成。假设empdetails表以salary建了一个索引salary_I1,可以用下列语句来完成:
列表四:
select salary from
(select /* + Index(empdetails,salary_I1) */ Rownum, Row_num, salary
from empdetails)where Row_num = &1;
在列表四中,用到了Oracle表中的另一个伪字段rownum,还用到了Oracle的优化提示(hint)。要注意Index提示是必须的,如果没有它,rownum是个伪字段,它不能与整数作相等的运算。Oracle一般按照记录插入的顺序来返回rownum,并不排序,如果用到此hint就可按salary字段排序返回。
列表四找到了按salary字段排序后的第n个记录的salary值。若表中有多个记录有相同的salary 值,而我们只要找到第 n 个最大 salary 值,而不是第 n 个记录,可以用列表5 的语句来实现。
列表五:
select salary from
(select rownum aa, salary from
(select distinct salary from empdetails)
)
where aa = ( select count (distinct salary ) + 1 - &n from empdetails);
至此,不会再出现列表一和列表二语句运行时的情况了,找第n个最大值的目的终于达到了。
- Oracle查找第n个最大值
- N个数中的第k个最大值
- 查找单链表中倒数第n个节点
- 存储过程获取第N个最大值方法
- 设计一组N个数,确定其中第k个最大值
- 1、一组N个数,确定其中第k个最大值
- Java源码-N个数字中第k个最大值的求解方法
- 设计一个更优算法查找一n个元素数组中的最大值和最小值
- 从n个元素的列表中查找最大值(C实现)
- 设计一个最优算法来查找n个元素数组中的最大值和最小值
- 单链表查找倒数第N个元素&输出中间元素
- 【data structure】之查找链表中倒数第N个元素
- 查找按某列排序后的第n个值
- 查找链表中的倒数第n个结点
- Oracle如何快速查找第N条记录?
- 查找第N个丑数
- 查找第n个数
- 字符串算法——查找数组第K个最大值( Kth Largest Element in an Array)
- FormCheck.js
- 节约内存:清除任务栏“自定义通知”里的图标
- Qt 属性
- 信息化人员要摆正位置
- JAVA配置环境变量
- Oracle查找第n个最大值
- How to make android app’s background image repeat.
- asp.net4.0的新特性入门概述
- linux下挂载硬盘
- LINK2005"符号已定义"的链接错误
- ODBC API常用函数诠释
- vc模拟键盘输入的一些资料
- Test1pass CompTIA PD1-001 free braindumps
- 华为的研发给我们什么启示?