备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句,可参考:
scott建表及录入数据sql脚本
一.需求
把查询中返回的所有列转换为1列。
例如,返回deptno 10中所有员工的ename、job和sal,而且要把这3个值放到一列中。
为每个员工返回3行信息,而且在两个员工之间加一个空白行。
希望返回的结果集如下:
+-----------+
| emps |
+-----------+
| CLARK |
| MANAGER |
| 2450 |
| NULL |
| KING |
| PRESIDENT |
| 5000 |
| NULL |
| MILLER |
| CLERK |
| 1300 |
| NULL |
+-----------+
二.解决方案
关键是用笛卡尔积为每个员工返回4行。这样就可以为每列产生一行,而且在两个员工之间加一个空白行。
select case rn
when 1 then ename
when 2 then job
when 3 then cast(sal as char(4))
end emps
from (
select e.ename, e.job,e.sal,
row_number() over w as 'rn'
from emp e,
( select *
from emp where job = 'CLERK') four_rows
where e.deptno = 10
window w as (partition by e.empno order by e.empno )
) x;
测试记录:
mysql> select case rn
-> when 1 then ename
-> when 2 then job
-> when 3 then cast(sal as char(4))
-> end emps
-> from (
-> select e.ename, e.job,e.sal,
-> row_number() over w as 'rn'
-> from emp e,
-> ( select *
-> from emp where job = 'CLERK') four_rows
->
-> where e.deptno = 10
-> window w as (partition by e.empno order by e.empno )
-> ) x;
+-----------+
| emps |
+-----------+
| CLARK |
| MANAGER |
| 2450 |
| NULL |
| KING |
| PRESIDENT |
| 5000 |
| NULL |
| MILLER |
| CLERK |
| 1300 |
| NULL |
+-----------+
12 rows in set, 3 warnings (0.00 sec)