【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_salfrom empwhere 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 empwhere deptno=20order 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 empwhere deptno=30order 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 ) Ainner 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 ) Bon A.deptno = B.deptno

結果如下：

在這個方案裡面，還使用了listagg()分析函數將最值有重複姓名的人合并在一起，用wm_concat()函數替代listagg()也可以

wm_concat(ename) as dept_max_ename,wm_concat(ename) as dept_min_ename,

------------------------------------------------------------------------

 

【Oracle】oracle取最大值和最小值的幾個方法匯總