MAX()KEEP(DENSE_RANK FIRST/LAST) 获取最大值相对应的某个字段,非常非常实用的一个函数
场景
在业务数据中可能遇到这样的需求。希望获取部门内年龄最小的人中，工资最高的记录。此时就需要使用KEEP(DENSE_RANK FIRST/LAST)来处理数据了。
使用
首先构造一下临时数据。
- WITH workers AS(
- SELECT 'DOM1' dept, 'zhangsan' names , 23 age, 4000 salaries FROM dual UNION ALL
- SELECT 'DOM1' dept, 'lisi' names , 35 age, 9000 salaries FROM dual UNION ALL
- SELECT 'DOM2' dept, 'wangwu' names , 26 age, 6500 salaries FROM dual UNION ALL
- SELECT 'DOM2' dept, 'maliu' names , 28 age, 6000 salaries FROM dual UNION ALL
- SELECT 'DOM2' dept, 'zhaoqi' names , 26 age, 5000 salaries FROM dual UNION ALL
- SELECT 'DOM1' dept, 'liba' names , 23 age, 3000 salaries FROM dual
- )
在这六条数据中，我们期望的数据是：(DOM1,4000)和(DOM2,6500)。
我们的sql如下：
- SELECT w.dept, MAX(w.salaries) KEEP(DENSE_RANK FIRST ORDER BY w.age) max_salary FROM workers w WHERE 1=1 GROUP BY dept;
此图就是我们的查询结果。
解释
KEEP
keep的意思就是“保持”，会保持满足括号内条件的记录，用ORDER BY 后自然会有FIRST和LAST了。
DENSE_RANK
DENSE_RANK是排序策略。例如，它会将两个第二名排在第一名之后，之后还是第三名排在第二名之后。
FIRST/LAST
FIRST/LAST就是对数据进行筛选了。这里我们筛选age最小的记录。当然不止一条了。然后使用MAX()选取最大salaries了。
延伸
同样的，年龄最大人中的最低工资查询如下：
- SELECT w.dept, MIN(w.salaries) KEEP(DENSE_RANK LAST ORDER BY w.age) min_salary FROM workers w WHERE 1=1 GROUP BY dept;