28 分析函数优化自连接，减少表访问次数 --优化主题系列

有如下SQL(基于HROracle11gR2)：

selectemployee_id, first_name, last_name, salary

  from employees a

 where salary = (select min(salary)

                   from employees b

                  where b.department_id =a.department_id);

根据department_id分组 查找每个department_id组中薪水最少的员工信息

上面的SQL会扫描employees表两次可以利用如下分析函数只让Oracle扫描employees表一次

SQL改写:

withx as

 (select e.*, min(salary) over(partition bye.department_id) as min_salary

    from employees e)

selectemployee_id, first_name, last_name, salary

  from x

 where x.salary = x.min_salary;