sql - 具有由联接表的不同行组成的列的 select 语句

Question

2张 table

用户列 = 姓名,电子邮件

joe|joe@gmail.com
sara|sara@hotmail.com
alan|alan@gmail.com

表 2 是 extras cols = name,col,value

joe|favoritecolor|blue
joe|boss|sam
sara|favoritecolor|green

(真正扩展了第一个表的模式，但没有让 sql 引擎注意到它)

获得人们喜爱的颜色

Select  name
      , value As favoritecolor
From    user
      , extras
Where   extras.name = user.name
        And extras.col = 'favoritecolor'

但是我如何获得 favoritecolor 和 boss？

注意这个查询我想要的输出

select name,email,favoritecolor,boss from user

是

joe|joe@gmail|blue|sam
sara|sara@hotmail.com|green|<null>
alan|alan@gmail.com|<null>|<null>

Answer 1

您可以使用别名在同一个表上使用双连接

(对于连接，如果键始终存在则使用内部连接，否则使用左连接)

 select a.name, e1.value  as favoritecolor , e2.value as boss
 from user
 INNER JOIN extras as e1 on e1.name = user.name and e1.col = 'favoritecolor'
 INNER JOIN extras as e2 on e2.name = user.name and e2.col = 'boss'

如果你的列不总是匹配你应该使用左连接

 select a.name, e1.value  as favoritecolor , e2.value as boss
 from user
 left JOIN extras as e1 on e1.name = user.name and e1.col = 'favoritecolor'
 left JOIN extras as e2 on e2.name = user.name and e2.col = 'boss'

如果你一直有喜欢的颜色关系但不总是老板

 select a.name, e1.value  as favoritecolor , e2.value as boss
 from user
 Inner JOIN extras as e1 on e1.name = user.name and e1.col = 'favoritecolor'
 left JOIN extras as e2 on e2.name = user.name and e2.col = 'boss'