--keep的用法
--需要注意的是keep只能与dense_rank FIRST,dense_rank LAST搭配使用
SELECT
emp_id,emp_name,dept_id,hire_date,salary,
dense_rank() OVER(PARTITION BY dept_id ORDER BY salary) dense_rank,
MIN(hire_date) KEEP (dense_rank FIRST ORDER BY salary) OVER(PARTITION BY dept_id) min_first,
MIN(hire_date) KEEP (dense_rank LAST ORDER BY salary) OVER(PARTITION BY dept_id) min_last,
MAX(hire_date) KEEP (dense_rank FIRST ORDER BY salary) OVER(PARTITION BY dept_id) max_first,
MAX(hire_date) KEEP (dense_rank LAST ORDER BY salary) OVER(PARTITION BY dept_id) max_last
FROM employeeinfo
WHERE dept_id IN (10,30,50,90);
--需要注意的是keep只能与dense_rank FIRST,dense_rank LAST搭配使用