Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as test@192.168.1.118:1521/orcl
SQL>
SQL> /*查看有多少个JOB*/
2 SELECT DISTINCT JOB FROM EMP;
JOB
----------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
SQL> /*将各个JOB用CASE WHEN转换为列显示工资在部门及工作间的分布*/
2 SELECT DEPTNO,
3 CASE WHEN JOB = ' CLERK ' THEN SAL END AS CLERK,
4 CASE WHEN JOB = ' SALESMAN ' THEN SAL END AS SALESMAN,
5 CASE WHEN JOB = ' PRESIDENT ' THEN SAL END AS PRESIDENT,
6 CASE WHEN JOB = ' MANAGER ' THEN SAL END AS MANAGER
7 FROM EMP
8 ORDER BY 1;
DEPTNO CLERK SALESMAN PRESIDENT MANAGER
---------- ---------- ---------- ---------- ----------
10 2450
10 5000
10 1300
20 2975
20
20 1100
20 800
20
30 1250
30 1500
30 1600
30 950
30 2850
30 1250
14 rows selected
SQL> /* 根据需求可保持上述各列显示明细数据,或按部门编码汇总,如下所示 */
2 SELECT DEPTNO,
3 SUM(CASE WHEN JOB = 'CLERK' THEN SAL END) AS CLERK,
4 SUM(CASE WHEN JOB = 'SALESMAN' THEN SAL END) AS SALESMAN,
5 SUM(CASE WHEN JOB = 'PRESIDENT' THEN SAL END) AS PRESIDENT,
6 SUM(CASE WHEN JOB = 'MANAGER' THEN SAL END) AS MANAGER
7 FROM EMP
8 GROUP BY DEPTNO
9 ORDER BY 1;
DEPTNO CLERK SALESMAN PRESIDENT MANAGER
---------- ---------- ---------- ---------- ----------
10 1300 5000 2450
20 1900 2975
30 950 5600 2850
SQL>
Connected as test@192.168.1.118:1521/orcl
SQL>
SQL> /*查看有多少个JOB*/
2 SELECT DISTINCT JOB FROM EMP;
JOB
----------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
SQL> /*将各个JOB用CASE WHEN转换为列显示工资在部门及工作间的分布*/
2 SELECT DEPTNO,
3 CASE WHEN JOB = ' CLERK ' THEN SAL END AS CLERK,
4 CASE WHEN JOB = ' SALESMAN ' THEN SAL END AS SALESMAN,
5 CASE WHEN JOB = ' PRESIDENT ' THEN SAL END AS PRESIDENT,
6 CASE WHEN JOB = ' MANAGER ' THEN SAL END AS MANAGER
7 FROM EMP
8 ORDER BY 1;
DEPTNO CLERK SALESMAN PRESIDENT MANAGER
---------- ---------- ---------- ---------- ----------
10 2450
10 5000
10 1300
20 2975
20
20 1100
20 800
20
30 1250
30 1500
30 1600
30 950
30 2850
30 1250
14 rows selected
SQL> /* 根据需求可保持上述各列显示明细数据,或按部门编码汇总,如下所示 */
2 SELECT DEPTNO,
3 SUM(CASE WHEN JOB = 'CLERK' THEN SAL END) AS CLERK,
4 SUM(CASE WHEN JOB = 'SALESMAN' THEN SAL END) AS SALESMAN,
5 SUM(CASE WHEN JOB = 'PRESIDENT' THEN SAL END) AS PRESIDENT,
6 SUM(CASE WHEN JOB = 'MANAGER' THEN SAL END) AS MANAGER
7 FROM EMP
8 GROUP BY DEPTNO
9 ORDER BY 1;
DEPTNO CLERK SALESMAN PRESIDENT MANAGER
---------- ---------- ---------- ---------- ----------
10 1300 5000 2450
20 1900 2975
30 950 5600 2850
SQL>