有这么一个表：
create table emp3( id number(8), name nvarchar2(20), deptname nvarchar2(20), salary number(6), primary key(id) );
可以这样充值：
declare d integer; salary integer; begin for i in 1..1000000 loop d:=dbms_random.value(1,5); salary:=dbms_random.value(3000,99999); insert into emp3 values(i, dbms_random.string('*',dbms_random.value(6,20))， decode(d,1,'销售',2,'售后',3,'研发',4,'市场',5,'管理'), salary); end loop; commit; end; /
好了，需求出来了：求每个部门薪水最高的员工。
实现方式一：自连接（自联结）
select * from emp3 a where a.salary=(select max(salary) from emp3 b where b.deptname=a.deptname) order by deptname
执行效果：
SQL> select * from emp3 a 2 where a.salary=(select max(salary) from emp3 b where b.deptname=a.deptname) 3 order by deptname; ID NAME DEPTNAME SALARY ---------- ---------------------------------------- ---------------------------------------- ---------- 90705 OIYXDGPGFLBCOE 售后 99999 382001 RSTFZVMJ 市场 99998 891150 YAEBSBDXQTMV 研发 99999 182816 GBPKFQXGZLEHODVPR 研发 99999 214175 MEPMYZ 研发 99999 42985 ABDXGRG 管理 99999 953733 MITUNVMEIRUDZ 销售 99999 已选择 7 行。
解释计划：
explain plan for select * from emp3 a where a.salary=(select max(salary) from emp3 b where b.deptname=a.deptname) order by deptname; select * from table(dbms_xplan.display) SQL> explain plan for select * from emp3 a 2 where a.salary=(select max(salary) from emp3 b where b.deptname=a.deptname) 3 order by deptname; 已解释。 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 956464727 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1651K| 165M| | 25606 (1)| 00:00:02 | | 1 | MERGE JOIN | | 1651K| 165M| | 25606 (1)| 00:00:02 | | 2 | SORT JOIN | | 849K| 28M| 71M| 9777 (2)| 00:00:01 | | 3 | VIEW | VW_SQ_1 | 849K| 28M| | 1849 (5)| 00:00:01 | | 4 | HASH GROUP BY | | 849K| 28M| | 1849 (5)| 00:00:01 | | 5 | TABLE ACCESS FULL| EMP3 | 849K| 28M| | 1791 (1)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ |* 6 | SORT JOIN | | 849K| 56M| 136M| 15822 (1)| 00:00:01 | | 7 | TABLE ACCESS FULL | EMP3 | 849K| 56M| | 1791 (1)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("ITEM_1"="A"."DEPTNAME" AND "A"."SALARY"="MAX(SALARY)") filter("ITEM_1"="A"."DEPTNAME" AND "A"."SALARY"="MAX(SALARY)") Note PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ ----- - dynamic statistics used: dynamic sampling (level=2) 已选择 24 行。
实现方式二：使用max分析函数
代码：
select b.id,b.name,b.deptname,b.salary from (select a.*,max(salary) over (partition by deptname) as max_salary from emp3 a ) b where b.salary=b.max_salary order by b.deptname
效果：
SQL> select b.id,b.name,b.deptname,b.salary from 2 (select a.*,max(salary) over (partition by deptname) as max_salary from emp3 a ) b 3 where b.salary=b.max_salary 4 order by b.deptname; ID NAME DEPTNAME SALARY ---------- ---------------------------------------- ---------------------------------------- ---------- 90705 OIYXDGPGFLBCOE 售后 99999 382001 RSTFZVMJ 市场 99998 182816 GBPKFQXGZLEHODVPR 研发 99999 214175 MEPMYZ 研发 99999 891150 YAEBSBDXQTMV 研发 99999 42985 ABDXGRG 管理 99999 953733 MITUNVMEIRUDZ 销售 99999 已选择 7 行。
解释计划：
SQL> explain plan for select b.id,b.name,b.deptname,b.salary from 2 (select a.*,max(salary) over (partition by deptname) as max_salary from emp3 a ) b 3 where b.salary=b.max_salary 4 order by b.deptname; 已解释。 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2505443670 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 849K| 67M| | 15822 (1)| 00:00:01 | |* 1 | VIEW | | 849K| 67M| | 15822 (1)| 00:00:01 | | 2 | WINDOW SORT | | 849K| 56M| 68M| 15822 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP3 | 849K| 56M| | 1791 (1)| 00:00:01 | ------------------------------------------------------------------------------------ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("B"."SALARY"="B"."MAX_SALARY") Note ----- - dynamic statistics used: dynamic sampling (level=2) 已选择 19 行。 SQL>
比较：
从解释计划可以看出，max分析函数方案cost只是自连接方案的五分之三，前者全表访问一次，后者两次，前者明显胜出。
END