我遇到了从LEFT JOIN获得重复值的情况.我认为这可能是一种理想的行为,但与我想要的不同.
我有三张桌子：人,部门和联系人.
人：
id bigint,person_name character varying(255)
部 ：
person_id bigint,department_name character varying(255)
联系 ：
person_id bigint,phone_number character varying(255)
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的意义,而它们只与人约翰有关,不必要的重复值会使更大的数据集升级问题.
所以,这就是我想要的：
id|person_name|department_name|phone_number --+-----------+---------------+------------ 1 |"John" |"Finance" |"023451" 1 |"John" |"Marketing" |"99478" 1 |"John" |"" |"67890" 2 |"Barbara" |"Finance" |"" 3 |"Michelle" |"" |"005634"
解决方法
我喜欢称这个问题为“通过代理交叉加入”.由于没有信息(WHERE或JOIN条件)表部门和联系人应该如何匹配,他们通过代理表人交叉加入 – 给你
Cartesian product.非常类似于这个：
> Two SQL LEFT JOINS produce incorrect result
那里有更多解释.
SELECT p.id,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;
您没有定义要选择的部门或电话号码,因此我随意选择了第一个部门或电话号码.你可以用任何其他方式……
