使用UNION的更好方式

Question

我有一个有5个字段的表product

code supplier_1 supplier_2 supplier_3
000001 3 87 25
000002 12 5 48

我必须带产品代码和所有有关系的供应商为此，我创建了每个供应商的查询和关联然后像

select code supplier1
from products
union
select code supplier2
from products
... and so on

还有别的方法吗?

Answer 1

从MySQL 8.0.14开始，可以使用横向连接:

select t.code, s.supplier
from t cross join lateral
(select supplier_1 as supplier union all
select supplier_2 as supplier union all
select supplier_3 as supplier union all
select supplier_4 as supplier 
) s;