MySQL入门篇-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_SALESMANFROM empGROUP 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) namesFROM EMP TWHERE T.DEPTNO = '20'GROUP BY T.DEPTNO;-- 指定根据ename字段排序,分隔符为'-'
SELECT T.DEPTNO, group_concat(t.ename order by ename SEPARATOR '-') namesFROM EMP TWHERE 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)