关于Oracle MAX()KEEP(DENSE_RANK LAST/FIRST ORDER BY ) 函数的使用分析

MAX()/MIN() KEEP(DENSE_RANK LAST/FIRST) 函数

解释：

1. max()  获取最大值；

2.min() 获取最小值;

3. keep 保持满足括号内条件的内容;

4.dense_rank  排序策略，连续排序，如果有两个同一级别时，接下来是第二级别 ，例如1,2,2,3

select names,dept,dense_rank() over(partition by dept order by age desc) rank from workers;

结果如下图

 

5.rank 排序策略， 跳跃排序，如果有两个同一级别时，接下来是第三级别,例如1,2,2,4

select names,dept,rank() over(partition by dept order by age desc) rank from workers;

结果如下图

6.first order by  按照要求对数据进行筛选，正序排

7.last order by 按照要求对数据进行筛选，倒叙排

下面我们将进行不同的数据获取展示：

只获取所需要的信息，便于统计：

WITH workers AS (
SELECT 'DOMA' dept ,'zhangsan' names,23 age,4000 salaries FROM dual
UNION ALL
SELECT 'DOMA' dept ,'lisi' names,35 age,9000 salaries FROM dual
union all
SELECT 'DOMB' dept ,'wangwu' names,26 age,6500 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'zhaoliu' names,28 age,7000 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'maqi' names,26 age,6000 salaries FROM dual
UNION ALL
SELECT 'DOMA' dept ,'fengba' names,25 age,6500 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'sujiu' names,25 age,7000 salaries FROM dual
)
--获取部门中年龄最小但工资最高的工资信息
SELECT A.dept,MAX(A.salaries) KEEP(DENSE_RANK FIRST ORDER BY A.age) AS max_sal FROM workers A GROUP BY A.dept;

结果如下图

 

 下面是获取人员匹配信息，将所有的信息都展示，便于直观统计

WITH workers AS (
SELECT 'DOMA' dept ,'zhangsan' names,23 age,4000 salaries FROM dual
UNION ALL
SELECT 'DOMA' dept ,'lisi' names,35 age,9000 salaries FROM dual
union all
SELECT 'DOMB' dept ,'wangwu' names,26 age,6500 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'zhaoliu' names,28 age,7000 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'maqi' names,26 age,6000 salaries FROM dual
UNION ALL
SELECT 'DOMA' dept ,'fengba' names,25 age,6500 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'sujiu' names,25 age,7000 salaries FROM dual
)
--获取部门中年龄最小但工资最高的人员信息
SELECT A.*, MAX(A.salaries) KEEP(DENSE_RANK FIRST ORDER BY A.age ) OVER(PARTITION BY A.dept) AS max_sal FROM workers A ;

 结果如下图

转载于:https://www.cnblogs.com/ityemu/p/10910545.html