MySql 从同一行的不同列返回多行

Question

给定两个表:

'people' 表包含以下列:

name
favorite_walking_shoe
favorite_running_shoe
favorite_dress_shoe
favorite_house_shoe
favorite_other_shoe

'shoes' 表包含以下列:

shoe
name
description

我想创建一个包含以下内容的结果集:

people.name, people.favorite_shoe_type, shoes.name, shoes.description

我知道我可以使用类似的方法获得所需的结果:

select p.name, p.favorite_shoe_type, s.name, s.description
  from (select name, favorite_walking_shoe as shoe, 'walking' as favorite_shoe_type
          from people where favorite_walking_shoe is not null
        union all
        select name, favorite_running_shoe, 'running'
          from people where favorite_running_shoe is not null
        union all
        select name, favorite_dress_shoe, 'dress'
          from people where favorite_dress_shoe not is null
        union all
        select name, favorite_house_shoe, 'house'
          from people where favorite_house_shoe not is null
        union all
        select name, favorite_other_shoe, 'other'
          from people where favorite_other_shoe not is null
        ) p
     join shoes s on s.shoe = p.shoe
    order by 1,2

但这需要“人员”表的 5 次传递。有没有一种方法可以在不需要多次传递的情况下完成 UNION ALL？

我应该指出，这些结构是我无法修改的供应商产品的一部分。 :(

Answer 1

您可以通过cross join 绕过五次扫描:

select p.name, p.favorite_shoe_type, s.name, s.description
from (select p.*,
             (case when favorite_shoetype = 'walking' then p.favore_walking_shoe
                   when favorite_shoetype = 'running' then p.favorite_running_shoe
                   when favorite_shoetype = 'dress' then p.favorite_dress_shoe
                   when favorite_shoetype = 'house' then p.favorite_house_shoe
                   when favorite_shoetype = 'other' then p.favorite_other_shoe
              end) as shoe
      from people p cross join
           (select 'walking' as favorite_shoe_type union all
            select 'running' union all
            select 'dress' union all
            select 'house' union all
            select 'other'
           ) shoetypes join
           shoes s
     ) p
     on s.shoe = p.shoe

我不确定这样会更有效率。如果你在鞋子上有索引，这个更复杂的版本可能会更有效:

select p.name, p.favorite_shoe_type, s.name, s.description
from (select p.name, favorite_shoe_types,
             (case when favorite_shoetype = 'walking' then ws.name
                   when favorite_shoetype = 'running' then rs.name
                   when favorite_shoetype = 'dress' then ds.name
                   when favorite_shoetype = 'house' then hs.name
                   when favorite_shoetype = 'other' then os.name
              end) as name,
             (case when favorite_shoetype = 'walking' then ws.description
                   when favorite_shoetype = 'running' then rs.description
                   when favorite_shoetype = 'dress' then ds.description
                   when favorite_shoetype = 'house' then hs.description
                   when favorite_shoetype = 'other' then os.name
              end) as description
      from people p left outer join
           shoes ws
           on ws.shoe = favorite_walking_shoe left outer join
           shoes rs
           on rs.shoe = favorite_running_shoe left outer join
           shoes ds
           on ds.shoe = favorite_dress_shoe left outer join
           shoes hs
           on hs.shoe = favorite_house_shoe left outer join
           shoes os
           on os.shoe = favorite_other_shoe cross join
           (select 'walking' as favorite_shoe_type union all
            select 'running' union all
            select 'dress' union all
            select 'house' union all
            select 'other'
           ) shoetypes 
     ) p
     on s.shoe = p.shoe
where s.name is not null     

这应该使用索引进行五次连接——非常快，一次扫描 people 表，并将其提供给交叉连接。然后该逻辑返回您想要的值。

注意:这两个都未经测试，因此它们可能存在语法错误。