sql - 防止 LEFT JOIN 中出现重复值

Question

我遇到了从LEFT JOIN获得重复值的情况。我认为这可能是一种理想的行为，但与我想要的不同。

我有三个表:person、department 和 contact。

人:

id bigint,
person_name character varying(255)

部门:

person_id bigint,
department_name character varying(255)

联系方式:

person_id bigint,
phone_number character varying(255)

Sql 查询:

SELECT p.id, p.person_name, d.department_name, c.phone_number 
FROM person p
  LEFT JOIN department d 
    ON p.id = d.person_id
  LEFT JOIN contact c 
    ON p.id = c.person_id;

结果:

id|person_name|department_name|phone_number
--+-----------+---------------+------------
1 |"John"     |"Finance"      |"023451"
1 |"John"     |"Finance"      |"99478"
1 |"John"     |"Finance"      |"67890"
1 |"John"     |"Marketing"    |"023451"
1 |"John"     |"Marketing"    |"99478"
1 |"John"     |"Marketing"    |"67890"
2 |"Barbara"  |"Finance"      |""
3 |"Michelle" |""             |"005634"

我知道这就是连接的作用，与选定的行保持相乘。但它给人的感觉就像电话号码 023451、99478、67890 是两个部门的，而它们只与 john 相关，并且具有不必要的重复值这将使更大的数据集的问题升级。
所以，这就是我想要的:

id|person_name|department_name|phone_number
--+-----------+---------------+------------
1 |"John"     |"Finance"      |"023451"
1 |"John"     |"Marketing"    |"99478"
1 |"John"     |""             |"67890"
2 |"Barbara"  |"Finance"      |""
3 |"Michelle" |""             |"005634"

这是我的情况的示例，我正在使用大量的表和查询。因此，需要一个通用的解决方案。

Answer 1

我喜欢将此问题称为“通过代理交叉连接”。由于没有信息(WHERE 或 JOIN 条件)，表 department 和 contact 应该如何匹配，它们通过代理表 person 交叉连接 - 为您提供 Cartesian product 。与此非常相似:

• Two SQL LEFT JOINS produce incorrect result

那里有更多解释。

您的查询的解决方案:

SELECT p.id, p.person_name, d.department_name, c.phone_number
FROM   person p
LEFT   JOIN (
   SELECT person_id, min(department_name) AS department_name
   FROM   department
   GROUP  BY person_id
   ) d ON d.person_id = p.id
LEFT   JOIN (
   SELECT person_id, min(phone_number) AS phone_number
   FROM   contact
   GROUP  BY person_id
   ) c ON c.person_id = p.id;

您没有定义要选择的部门或电话号码，所以我随意选择了最小值。您可以通过任何其他方式获得它...