sql - 如何在 JOIN 中引用外部字段?
问题描述
我需要选择具有最优先电话和地址的所有用户。这是为特定用户(id=1)选择最优先电话的查询:
select ph.id, ph.number from VisitingCard vc
left join Phone ph on ph.card_id = vc.id
where vc.person_id = 1
order by priority desc
limit 1
以下是我尝试为所有用户获得优先级最高的手机:
select * from Person p
left join (
select ph.id, ph.number from VisitingCard vc
left join Phone ph on ph.card_id = vc.id
where vc.person_id = p.id -- no such column: p.id
order by priority desc
limit 1
)
链接- SqlFiddle 与用户的架构和最优先的地址和电话(id = 1)
我如何为每个用户使用这样的查询,而不仅仅是对于 id=1 的用户?
解决方案
一种方法使用相关子查询:
select vc.person_id,
(select ph.number
from Phone ph
where ph.card_id = vc.id
order by ph.priority desc
limit 1
) as number
from VisitingCard vc;
推荐阅读
- jekyll - kramdown(Jekyll):如何缩进整个段落而不将其转换为代码块
- visual-c++ - CreateDXGIFactory 函数引发链接器错误
- javascript - 带有 D3 v3 的散点图中的折线图
- angular - 随机加载图像数组
- angular - 同时调用的 Observable
- python - 如何在熊猫的浮点数中转换负字符串?
- postgresql - Debezium Postgres Kafka Connector heartbeat is not committing LSN
- mongodb - Mongo-Express 由 Docker-Compose 启动导致错误 [MongoError]: failed to connect to server [mongo:27017] on first connect
- angular - Angular 8 自定义验证器从不返回错误
- ruby-on-rails - 设计注册电子邮件未到达“已发送邮件”很奇怪并且包含 Macbook 名称