postgresql PostgreSQL中如何连接两个子查询的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2076685/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to join the results of two subqueries in PostgreSQL?
提问by Falcon
hello i'm newbie in sql (postgresql)
i have 2 tables as resultof 2 differen selects
你好，我是 sql (postgresql) 的新手，由于2 个不同的选择，
我有 2 个表
all calls our customer contacts
number contact_id and contact_id name
3213 12 12 jonh
3213 34 16 michael
3213 43 65 hewlet
5432 16 32 steward
5432 51
6543 65
2322 54
2322 32
1 number can belong to different contacts... (contacts belong to different customers) i need to select distinct numbers from 1st result table. and names of this contacts from 2nd table..
1 个号码可以属于不同的联系人...（联系人属于不同的客户）我需要从第一个结果表中选择不同的号码。以及第二个表中此联系人的姓名..
and how i must unite my 2 selects
以及我必须如何统一我的 2 个选择
thanks.
谢谢。
回答by J?rn Schou-Rode
You will not be able to use the distinctkeyword, as you actually want to select also the contact_idfrom the all_callstable. Instead, you will need to use one of the aggregate functions to select a single contact_idfor each distinct phone number.
您将无法使用distinct关键字，因为您实际上还想contact_id从all_calls表中选择。相反，您需要使用聚合函数之一contact_id为每个不同的电话号码选择一个。
In this example I use the min()function, which will give me the contact with the numerically lowest contact_idfor each phone number:
在这个例子中，我使用了这个min()函数，它将为我contact_id提供每个电话号码数字最低的联系人：
select tmp.number, contacts.name
from (
select number, min(contact_id) as min_id
from all_calls
group by number
) as tmp
join contacts on tmp.min_id = contacts.contact_id
