select employee_id,department_id,salary from employees e where salary =(select max(salary) from employees m where m.department_id=e.department_id)
可查处每部门的最高工资,不知道他的执行顺序是什么样的
SELECT employee_id, department_id, salary
FROM employees e
WHERE salary = ( SELECT MAX( salary )
FROM employees m
WHERE m.department_id = e.department_id )
用类似PL/SQL的写法给你看看
代码:
declare
max_salary number;
begin
for e in
(SELECT employee_id, department_id, salary
FROM employees ) loop
select MAX( salary ) into max_salary
FROM employees m
WHERE m.department_id = e.department_id ;
if (e.salary = max_salary) then
dbms_output.put_line(e.employee_id||' ' ||e.department_id ||' '||e.salary);
end if;
end loop;
end;
/
对于employees e里的每行
检查 算出 和它的 department_id 一样的 employees m中最高Salary的数据。
如果 employees e 中的 Salary 和 相同department_id 一样的 employees m中最高Salary一致,则返回这一行。
整个语句等于是,找出所有在本部门中工资最高的个人,也可以写成:
select employee_id, department_id, salary from(
select employee_id, department_id, salary,
rank() over(partition by department_id order by salary desc ) as r
from employees
) where r= 1
可以避免多次访问 employees