mysql - 将 MySQL 列值从查询转换为行

Question

我有以下查询:

mysql> select id_sucursal from dw_sucursallookup where sucursal = "Centro" 
           UNION ALL 
       select id_sexo from dw_sexolookup where sexo = "Hombre" 
           UNION ALL 
       select id_tiempo from dw_tiempolookup where fecha = "2018-06-27" and hora = "noche" 
           UNION ALL 
       select id_edad from dw_edadlookup where edad = "41-55" 
           UNION ALL 
       select id_tipo_comida from dw_tipo_comidalookup where tipo_de_comida = 'dulce';

其输出如下:

+-------------+
| id_sucursal |
+-------------+
|  2014820869 |
|  2127812561 |
|  2020742459 |
|    49527792 |
|    95944605 |
+-------------+

我想要实现的是将结果转置为:

+-------------+-------------+-------------+-------------+----------------+
| id_sucursal |   id_sexo   |  id_tiempo  |   id_edad   | id_tipo_comida |
+-------------+-------------+-------------+-------------+----------------+
|  2014820869 |  2127812561 |  2020742459 |    49527792 |       95944605 |
+-------------+-------------+-------------+-------------+----------------+

如何在 mysql 中做到这一点？我尝试搜索并找到了一些解决方案，但没有一个像我想要的那样工作。

提前致谢

Answer 1

您可以将 select 与标量子查询结合使用:

select (select id_sucursal from dw_sucursallookup where sucursal = 'Centro') as id_sucursal,
       (select id_sexo from dw_sexolookup where sexo = 'Hombre') as id_sexo, 
       (select id_tiempo from dw_tiempolookup where fecha = '2018-06-27' and hora = 'noche') as id_tiempo
       (select id_edad from dw_edadlookup where edad = '41-55') as id_edad,
       (select id_tipo_comida from dw_tipo_comidalookup where tipo_de_comida = 'dulce') as tipo_de_comida