mysql结果集转成_MySQL 将结果集方向转为一列

备注:测试数据库版本为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)