查询每个部门最高工资员工的基本信息

首先需要说明的是Oracle的sql里面的in函数和exists函数以及not in函数和not exists函数的用法都非常相近，互相是可以替换的。

员工表（EMP）：
[img]http://dl2.iteye.com/upload/attachment/0097/4348/4bcb898a-2432-32f5-8090-1350d0bd3308.jpg[/img]


产品信息表（PRODUCT_INFO）：
[img]http://dl2.iteye.com/upload/attachment/0097/4346/967ddeff-2dc0-3a94-88d4-b8f2ed1ddd74.jpg[/img]

--查询每个部门最高工资员工的基本信息
select t.*
from emp t
where not exists (select 1 from emp where deptno = t.deptno and sal > t.sal)

--查询每组pid里面价格price最高的基本信息
select t.* from product_info t where not exists (select 1 from product_info where pid = t.pid and price > t.price);

--查询每组pid里面价格price最低的基本信息
select t.* from product_info t where not exists (select 1 from product_info where pid = t.pid and price < t.price);

--查询每组pid里面价格price最高的基本信息
select t.* from product_info t where t.pid not in (select pid from product_info where pid = t.pid and price > t.price);

--查询每组pid里面价格price最低的基本信息
select t.* from product_info t where t.pid not in (select pid from product_info where pid = t.pid and price < t.price);