emp表中有如下数据
SQL> select job,ename from emp;
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
14 rows selected.
现在要达到如下效果
SQL> select
2 max(case when job='CLERK' then ename else null end) as clerks,
3 max(case when job='ANALYST' then ename else null end) as analyst,
4 max(case when job='MANAGER' then ename else null end) as manager,
5 max(case when job='PRESIDENT' then ename else null end) as president,
6 max(case when job='SALESMAN' then ename else null end) as salesman
7 from
8 (
9 select job,ename,row_number() over(partition by job order by ename) rn
10 from emp
11 )
12 group by rn;
CLERKS ANALYST MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ----------
ADAMS FORD BLAKE KING ALLEN
JAMES SCOTT CLARK MARTIN
SMITH WARD
MILLER JONES TURNER
可能有人会产生疑问,这里为什么要使用row_number() over()呢?下面我们把内层查询的这一步取掉看看结果
SQL> select
2 max(case when job='CLERK' then ename else null end) as clerks,
3 max(case when job='ANALYST' then ename else null end) as analyst,
4 max(case when job='MANAGER' then ename else null end) as manager,
5 max(case when job='PRESIDENT' then ename else null end) as president,
6 max(case when job='SALESMAN' then ename else null end) as salesman
7 from emp;
CLERKS ANALYST MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ----------
SMITH SCOTT JONES KING WARD
对于一个给定职位,每个ENAME都有唯一的“行号”,这就可以避免可能有两个员工名字和职位都相同而带来的问题。这样做的目的是能够按行号(RN)分组,在使用了MAX时不会遗漏结果集中的任何员工。这是解决本问题最重要的步骤,如果没有这一步,外层查询的聚集运算会丢失掉一些有用的行,如上面的例子所示。
SQL> select rn,
2 case when job='CLERK' then ename else null end as clerks,
3 case when job='ANALYST' then ename else null end as analyst,
4 case when job='MANAGER' then ename else null end as manager,
5 case when job='PRESIDENT' then ename else null end as president,
6 case when job='SALESMAN' then ename else null end as salesman
7 from
8 (
9 select job,ename,row_number() over(partition by job order by ename) rn
10 from emp
11 );
RN CLERKS ANALYST MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
1 FORD
2 SCOTT
1 ADAMS
2 JAMES
3 MILLER
4 SMITH
1 BLAKE
2 CLARK
3 JONES
1 KING
1 ALLEN
RN CLERKS ANALYST MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
2 MARTIN
3 TURNER
4 WARD
14 rows selected.
上面的结果已经很接近了,再剔除掉NULL值就可以了。这由MAX和GROUP BY来完成。