MySQL 将结果集方向转为一列

﹏ヽ暗。殇╰゛Y 2023-01-11 15:30 194阅读 0赞

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

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

# 一.需求 #

把查询中返回的所有列转换为1列。  
例如，返回deptno 10中所有员工的ename、job和sal，而且要把这3个值放到一列中。  
为每个员工返回3行信息，而且在两个员工之间加一个空白行。

# 二.解决方案 #

关键是用笛卡尔积为每个员工返回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)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb