窗口函数之keep的用法（读书笔记四）

--keep的用法

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_lastFROM employeeinfoWHERE dept_id IN (10,30,50,90);

--需要注意的是keep只能与dense_rank FIRST,dense_rank LAST搭配使用