Oracle取非零的最小值,Oracle数据库之【Oracle】oracle取最大值和最小值的几个方法汇总...

雨点打透心脏的1/2处 2023-01-18 06:37 165阅读 0赞

本文主要向大家介绍了Oracle数据库之【Oracle】oracle取最大值和最小值的几个方法汇总，通过具体的内容向大家展现，希望对大家学习Oracle数据库有所帮助。

(1)oracle使用keep分析函数取最值记录

\-- 取工资sal最大的雇员姓名及其工资，以及工资sal最少的雇员姓名及其工资

select

deptno,

empno,

ename,

sal,

max(ename) keep(dense\_rank FIRST order by sal) over (partition by deptno) as min\_sal\_man,

max(sal) keep(dense\_rank FIRST order by sal) over (partition by deptno) as min\_sal,

max(ename) keep(dense\_rank LAST order by sal) over (partition by deptno) as max\_sal\_man,

max(sal) keep(dense\_rank LAST order by sal) over (partition by deptno) as max\_sal

from emp

where deptno=10

结果如下：

从语句中可以看到，ename和sal都是用的max()，这样做的目的是为了去除由于keep()函数得到的有重复值的数据结果集。这样用有一个弊端，加入部门20有两个相同的最大SAL的人，部门30有两个相同的最小SAL的人，如果按照这种方法取出来的数据，就不一定准确了，重复的人会被去除掉。

我们用下面的语句来修改一下：

select

deptno,

empno,

ename,

sal,

max(ename) keep(dense\_rank FIRST order by sal) over (partition by deptno) as min\_sal\_man,

max(sal) keep(dense\_rank FIRST order by sal) over (partition by deptno) as min\_sal,

max(ename) keep(dense\_rank LAST order by sal) over (partition by deptno) as max\_sal\_man,

max(sal) keep(dense\_rank LAST order by sal) over (partition by deptno) as max\_sal,

wmsys.wm\_concat(ename) keep(dense\_rank LAST order by sal) over (partition by deptno) as 工资最高的人,

wmsys.wm\_concat(ename) keep(dense\_rank FIRST order by sal) over (partition by deptno) as 工资最低的人

from emp

where deptno=20

order by 1, 2 ;

我们新增了两个列：工资最高的人，工资最低的人。执行看一下结果：

可以看到，deptno=20时，SCOTT和FORD两个人的工资SAL都是3000，如果用MAX()就只能取出其中一个人的姓名，显然是不对的。

然后，我们再来看一下deptno=30时的情况：

select

deptno,

empno,

ename,

sal,

max(ename) keep(dense\_rank FIRST order by sal) over (partition by deptno) as min\_sal\_man,

max(sal) keep(dense\_rank FIRST order by sal) over (partition by deptno) as min\_sal,

max(ename) keep(dense\_rank LAST order by sal) over (partition by deptno) as max\_sal\_man,

max(sal) keep(dense\_rank LAST order by sal) over (partition by deptno) as max\_sal,

wmsys.wm\_concat(ename) keep(dense\_rank LAST order by sal) over (partition by deptno) as 工资最高的人,

wmsys.wm\_concat(ename) keep(dense\_rank FIRST order by sal) over (partition by deptno) as 工资最低的人

from emp

where deptno=30

order by 1, 2 ;

deptno=30时的结果如下：

可以看到，deptno=30时，WARD和MARTIN两人的工资最小且均为1250，如果用MAX()的方式，就只能取出其中一个人的名称。

这就是因为keep()取出来的数据集是包含多个数据结果的，所以，在语句中使用了wmsys.wm\_concat()函数，该函数的作用是以逗号分隔连接列的值。

注：wm\_concat()的功能有点儿类似分析函数listagg() within group() 。

(2)使用SQL子查询和聚合函数，查询出最大值和最小值

\-- 使用子查询查询出最大值和最小值

select \* from

(

select

deptno,

listagg(ename,‘,‘) within group (order by deptno) as dept\_max\_ename,

max(sal) as dept\_max\_sal

from emp

where (deptno,sal) in (select deptno, max(sal) as max\_sal from emp group by deptno)

group by deptno

) A

inner join

(

select

deptno,

listagg(ename,‘,‘) within group (order by deptno) as dept\_min\_ename,

min(sal) as dept\_min\_sal

from emp

where (deptno,sal) in (select deptno, min(sal) as min\_sal from emp group by deptno)

group by deptno

) B

on A.deptno = B.deptno

结果如下：

在这个方案里面，还使用了listagg()分析函数将最值有重复姓名的人合并在一起，用wm\_concat()函数替代listagg()也可以

wm\_concat(ename) as dept\_max\_ename,

wm\_concat(ename) as dept\_min\_ename,

\------------------------------------------------------------------------

本文由职坐标整理并发布，希望对同学们学习Oracle有所帮助，更多内容请关注职坐标数据库Oracle数据库频道！

Oracle取非零的最小值,Oracle数据库之【Oracle】oracle取最大值和最小值的几个方法汇总...

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle同时取最大值与最小值sql语句性能测试

相关最大值、最小值函数

相关 Math对象方法总结（向上取整、向下取整、四舍五入、取随机数，取最大值、取最小值）

相关 List筛选最大值和最小值的方法

相关 Oracle取非零的最小值,Oracle数据库之【Oracle】oracle取最大值和最小值的几个方法汇总...

相关 oracle数据库分组后取每组最大值

相关 float类型最大值和最小值

相关 Java之数列最大值和最小值

相关 IOS之取乱序数据最大值、最小值方法

相关最大值最小值算法