MySQL 行转列

mysql> SELECT deptno,
-> ifnull(sum(case when job = 'MANAGER' then sal else 0 end),0) s_MANAGER,
-> ifnull(sum(case when job = 'ANALYST' then sal else 0 end),0) s_ANALYST,
-> ifnull(sum(case when job = 'CLERK' then sal else 0 end),0) s_CLERK,
-> ifnull(sum(case when job = 'PRESIDENT' then sal else 0 end),0) s_PRESIDENT,
-> ifnull(sum(case when job = 'SALESMAN' then sal else 0 end),0) s_SALESMAN
-> FROM emp
-> GROUP BY deptno;
+--------+-----------+-----------+---------+-------------+------------+
| deptno | s_MANAGER | s_ANALYST | s_CLERK | s_PRESIDENT | s_SALESMAN |
+--------+-----------+-----------+---------+-------------+------------+
| 10 | 2450.00 | 0.00 | 1300.00 | 5000.00 | 0.00 |
| 20 | 2975.00 | 6000.00 | 1900.00 | 0.00 | 0.00 |
| 30 | 2850.00 | 0.00 | 950.00 | 0.00 | 5600.00 |
+--------+-----------+-----------+---------+-------------+------------+
3 rows in set (0.00 sec)