gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 15:15:30 25 4
gpt4 key购买 nike

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>

最佳答案

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

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

 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'

关于sql - 具有由联接表的不同行组成的列的 select 语句,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/40920866/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com