sql - Postgresql 多个左连接
问题描述
我正在尝试从表中获取列表并在两列上进行连接。我认为如果我向您展示我正在尝试做的事情会更好,并希望它会有意义。
用户表:
username | emailaddress | socialkey | avatarpic
----------+----------------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------
Bobert | bob@none.com | 9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a | https://craftedin.co/static/images/user/avatars/thumbs/59159be40a8c76.26091804.jpg
Mikey | mike@none.com | 3bcd6c5f811ba06aa49c2df9504fe5416f35702b78bcdc15ecbd5137fabdca59 | http://cdn1-www.cattime.com/assets/uploads/gallery/siamese-cats-and-kittens-pictures/siamese-ca
Mary | mary@none.com | f96197d19984b6f4a457ff0aed1c5bd222ffc2db51f30e4e8170ae02cb007ded | http://foopic.com
Katya | katya@none.com | 11ba72d57246c08ccbb9e201fb242a3f578d698e7d787d4a1aa08c8897b6cd80 | http://foopic.com
好友表:
usera | userb
---------------+----------------
mike@none.com | bob@none.com
mary@none.com | bob@none.com
bob@none.com | katya@none.com
我的查询需要用户的socialkey并进行查找。不幸的是,这给了我不好的结果。我想使用用户的socialkey
with Sfriends as (
select *
from friends
where usera = (select emailaddress
from users
where socialkey = '9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a'))
or (userb = (select emailaddress
from users
where socialkey = ('9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a')))
), socialkeys as (
select socialkey,username,emailaddress,avatarpic
from users
)
select Sfriends.usera, socialkey , avatarpic, Sfriends.userb,socialkey
from Sfriends as foo
left join Socialkeys on foo.usera = socialkeys.emailaddress
left join Sfriends on foo.userb = socialkeys.emailaddress;
这是我想要的:
user-namea | usera | userasocialkey | usera-avatar-pic | user-nameb | userb | userbsocialkey | userb-avatar-pic
----------+---------------+------------------------------------------------------------------+------------------+----------+----------------+------------------------------------------------------------------+------------------
Mikey | mike@none.com | 3bcd6c5f811ba06aa49c2df9504fe5416f35702b78bcdc15ecbd5137fabdca59 | usera-avatar-pic | Bobert | bob@none.com | 9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a | userb-avatar-pic
Mary | mary@none.com | f96197d19984b6f4a457ff0aed1c5bd222ffc2db51f30e4e8170ae02cb007ded | usera-avatar-pic | bobert | bob@none.com | 9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a | userb-avatar-pic
Bobert | bob@none.com | 9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a | usera-avatar-pic | Katya | katya@none.com | 11ba72d57246c08ccbb9e201fb242a3f578d698e7d787d4a1aa08c8897b6cd80 | userb-avatar-pic
如果这会有所帮助,我愿意将社交密钥插入朋友表中。
解决方案
尝试加入users
两次,一次为usera
一次,一次为userb
from friends
。
SELECT ua.username,
ua.emailaddress,
ua.socialkey,
ua.avatarpic,
ub.username,
ub.emailaddress,
ub.socialkey,
ub.avatarpic
FROM friends f
INNER JOIN users ua
ON ua.emailaddress = f.usera
INNER JOIN users ub
ON ub.emailaddress = f.userb
WHERE '9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a' IN (ua.socialkey,
ub.socialkey);
推荐阅读
- jquery - 将错误从 Nodejs express 发送到 Jquery
- r - 对数赔率公式
- r - 使用 pivot_longer 函数从多个原始列创建两列的建议
- php - 如何在 PHP 中从字符串中扩展对象属性和局部变量
- reactjs - 与 if / else 反应的问题
- webgl - 具有多个渲染目标的 WebGL readPixels
- docusignapi - 如何检索按模板 ID 过滤的已完成信封?
- c# - 带有泛型的观察者模式
- java - 在java中停止异步任务的onPostExecute
- java - Spring Security 5 - 如何从 id_token 进行 SSO(Google 一键式)