防止 LEFT JOIN 中的重复值 [英] Prevent duplicate values in LEFT JOIN

问题描述

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

I faced a situation where I got duplicate values from LEFT JOIN. I think this might be a desired behavior but unlike from what I want.

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

I have three tables: person, department and 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 是两个部门的，而它们只与人约翰有关，并具有不必要的重复值这将在更大的数据集上升级问题.
所以，这就是我想要的:

I know it's what joins do, keeping multiplied with selected rows. But It gives a sense like phone numbers 023451,99478,67890 are for both departments while they are only related to person john with unnecessary repeated values which will escalate the problem with larger data set.
So, here is what I want:

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

这是我的情况示例，我使用了大量表和查询.所以，有点需要一个通用的解决方案.

This is a sample of my situation and I am using a large set of tables and queries. So, kind of need a generic solution.

推荐答案

我喜欢称这个问题为通过代理交叉连接".由于没有信息(WHERE 或 JOIN 条件)表 department 和 contact 应该如何匹配，它们通过代理表 person 交叉连接 - 为您提供 笛卡尔积.非常类似于这个:

I like to call this problem "cross join by proxy". Since there is no information (WHERE or JOIN condition) how the tables department and contact are supposed to match up, they are cross-joined via the proxy table person - giving you the Cartesian product. Very similar to this one:

• 两个 SQL LEFT JOINS 产生错误的结果

那里有更多解释.

您的查询的解决方案:

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;

你没有定义选择哪个部门或电话号码，所以我随意选择了最低.你可以用任何其他方式......

You did not define which department or phone number to pick, so I arbitrarily chose the minimum. You can have it any other way ...

这篇关于防止 LEFT JOIN 中的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！