如何引用连接内的外场？

Question

这是我的模式：

​

我需要选择所有的用户与最优先的电话和地址。这是一个用于为特定用户(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的用户？

Answer 1

使用相关子查询查询具有优先级地址和电话的每个用户(感谢这个答案的想法)：

select p.*,
(
    select ph.number
    from VisitingCard vc
    left join Phone ph on ph.card_id = vc.id
    where vc.person_id = p.id
    order by ph.priority desc, ph.id asc
) as most_prioritized_number,
(
    select ad.address_text
    from VisitingCard vc
    left join Address ad on ad.card_id = vc.id
    where vc.person_id = p.id
    order by ad.priority desc, ad.id asc
) as most_prioritized_address
from Person p

SqlFiddle

Answer 2

一种方法使用关联子查询：

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;