MySQL 行转列小结

备注:测试数据库版本为MySQL 8.0

需求:求emp表各个岗位的工资之和，如无，用0代替

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一.CASE语法

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;
复制

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)
复制

需求:部门编号为20的所有的员工信息，以行的形式显示

二.group_concat语法

group_concat语法:
GROUP_CONCAT([DISTINCT] expr [,expr …]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name …]]
[SEPARATOR str_val])

-- 默认分隔符是','
SELECT T.DEPTNO, group_concat(t.ename) names
  FROM EMP T
 WHERE T.DEPTNO = '20'
 GROUP BY T.DEPTNO;

-- 指定根据ename字段排序,分隔符为'-'
SELECT T.DEPTNO, group_concat(t.ename order by ename SEPARATOR '-') names
  FROM EMP T
 WHERE T.DEPTNO = '20'
 GROUP BY T.DEPTNO;

复制

mysql> -- 默认分隔符是','
mysql> SELECT T.DEPTNO, group_concat(t.ename) names
    ->   FROM EMP T
    ->  WHERE T.DEPTNO = '20'
    ->  GROUP BY T.DEPTNO;
+--------+------------------------------+
| DEPTNO | names                        |
+--------+------------------------------+
|     20 | SMITH,JONES,SCOTT,ADAMS,FORD |
+--------+------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 指定根据ename字段排序,分隔符为'-'
mysql> SELECT T.DEPTNO, group_concat(t.ename order by ename SEPARATOR '-') names
    ->   FROM EMP T
    ->  WHERE T.DEPTNO = '20'
    ->  GROUP BY T.DEPTNO;
+--------+------------------------------+
| DEPTNO | names                        |
+--------+------------------------------+
|     20 | ADAMS-FORD-JONES-SCOTT-SMITH |
+--------+------------------------------+
1 row in set (0.00 sec)
复制