mysql - 如何在 Mysql 中取消透视下表？ [请求更好的方法]

Question

初始表:

客户编号 |订单_A |订单_B |订单_C | order_D

       1 |       1 |       0 |       0 |       1 |
       2 |       0 |       1 |       0 |       1 |
       3 |       1 |       1 |       0 |       1 |
       4 |       0 |       0 |       0 |       1 |

要求的输出:

客户编号 |订单类型 |

       1 | A          |
       1 | D          |
       2 | B          |
       2 | D          |
       3 | A          |
       3 | B          |
       3 | D          |
       4 | D          |

最初，我的问题被标记为重复，并要求我引用以下问题: MySQL pivot table

我引用了它，也得到了 http://archive.oreilly.com/oreillyschool/courses/dba1/ 的帮助提出以下代码:

select customer_id,
 case when order_A=1 then 'A' end as order_type
 from tb1 having order_type is not null
Union all
 select customer_id,
 case when order_B=1 then 'B' end as order_type
 from tb1 having order_type is not null
Union all
 select customer_id,
 case when order_C=1 then 'C' end as order_type
 from tb1 having order_type is not null
Union all
 select customer_id,
 case when order_D=1 then 'D' end as order_type
 from tb1 having order_type is not null order by customer_id,order_type;

这段代码确实给了我所需的输出，但我想知道是否有更好的方法/方法来解决这个问题。

此外，如果有人可以帮助推荐我可以在面试中练习此类问题的网站/书籍，那将是非常有帮助的。

Answer 1

我会这样写:

select customerId, 'A' as order_type
from tb1
where order_A = 1
union all
select customerId, 'B' as order_type
from tb1
where order_B = 1
union all
select customerId, 'C' as order_type
from tb1
where order_C = 1
union all
select customerId, 'D' as order_type
from tb1
where order_D = 1;

条件逻辑都在 where 中，而不是在 case 表达式和 having 子句之间拆分。这也使用标准 SQL，它将在任何数据库中工作(您使用 having 而没有 GROUP BY 是 MySQL 扩展)。

如果您想从性能 的角度简化查询，那么您想消除四次扫描。你可以这样做:

select tb1.customerId, o.order_type
from tb1 join
     (select 'A' as order_type union all
      select 'B' as order_type union all
      select 'C' as order_type union all
      select 'D' as order_type 
     ) o
     on (tb1.order_A = 1 and o.order_type = 'A') or
        (tb1.order_B = 1 and o.order_type = 'B') or
        (tb1.order_C = 1 and o.order_type = 'C') or
        (tb1.order_D = 1 and o.order_type = 'D') ;

这应该从 tb1 中读取一行，然后通过 join 操作进行四次比较。 union all 方法读取表四次。