MySQL 联合/完全外连接未返回预期结果

Question

我有 2 个表，一个列出人员及其与其属性的关系，另一个表列出属性(名字、姓氏等)。

人员表中的每个人可能不具有属性表中列出的所有属性。我想要的是每个人都为每个属性返回一行，无论他们是否有链接。

举个例子

Properties              Person
prop_id|prop_name       person_id|prop_id|prop_value
-----------------       -----------------------------------
1      |firstname        10      | 1            | john
2      |lastname         11      | 1            | sean
                         11      | 2            | connery

我想要的结果是这样的

10 | firstname | john
10 | lastname  | null
11 | firstname | sean
11 | lastname  | connery

请注意，john/10 有一个姓氏行，但为空。

我认为这个查询会给我结果，但它没有给我我想要的 john 的空行。

select person_id, prop_value, prop_name
from person
left outer join properties on person.prop_id = properties.prop_id
union
select person_id, prop_value, prop_name
from person
right outer join properties on person.prop_id = properties.prop_id

此查询省略了 10 个姓氏空行。这个查询可以吗？这是 MySQL/MariaDB。

Answer 1

我认为您需要将 person 表连接到 properties 表两次才能使其正常工作。可能是这样的:

SELECT DISTINCT p1.person_id, p2.prop_value, prop.prop_name
FROM person p1
  CROSS JOIN properties prop
  LEFT JOIN person p2 ON p1.person_id = p2.person_id AND prop.prop_id = p2.prop_id

这在 @RomainBar 的 sqlfiddle.com/#!9/3dadb90/7 中得到了证明。