Mysql：創建一個具有多個自連接的視圖，結果中沒有重複

只是為了澄清我無法更改表格結構，所以請省略“您應該將表格更改為這個和那個”的答案，謝謝。

所以我有一個表entities_attributes_values，其中一個實體有很多屬性和該屬性的值，基本上想像3個欄位：

• entity_id
• entity_attributes_id
• 價值

因為每個實體屬性及其值都在行上，所以獲取更多值並不是那麼容易，我在考慮多個自連接，並且因為這個查詢很常見，所以我創建了一個視圖，它是用這個查詢建構的：

SELECT `L1`.`entity_id`,
      `L1`.`value` as 'company_id',
      `L2`.`value` as 'entity_name',
      `P`.`value` as 'person_name',
      `L4`.`value` as 'establishment_id',
      `L5`.`value` as 'department_id'
FROM `entities_attributes_values` `L1`
LEFT JOIN `entities_attributes_values` `L2` ON `L1`.`entity_id` = `L2`.`entity_id` AND `L2`.`entity_attributes_id` = 1
LEFT JOIN `entities_attributes_values` `L3` ON `L1`.`entity_id` = `L3`.`entity_id` AND `L3`.`entity_attributes_id` = 3
LEFT JOIN `persons_attributes_values` `P` ON `L3`.`value` = `P`.`core_persons_id` AND `P`.`core_persons_attributes_id` = 4
LEFT JOIN `entities_attributes_values` `L4` ON `L1`.`entity_id` = `L4`.`entity_id` AND `L4`.`entity_attributes_id` = 12
LEFT JOIN `entities_attributes_values` `L5` ON `L1`.`entity_id` = `L5`.`entity_id` AND `L5`.`entity_attributes_id` = 13
WHERE `L1`.`entity_attributes_id` = 2

所以這行得通，但我有一個問題，我得到“重複”的值，它並不是真正重複的，但關鍵是在我看來，我希望每個實體只有一行具有所有屬性值，但我得到了這個：

所以你可以看到前三個結果對我不利，我只需要第四個，我有關於一個實體的所有數據。

預先感謝您的任何幫助！

內部聯接：

內部連接會生成一個結果集，該結果集僅限於兩個表中與我們要查找的內容匹配的行。如果您不知道需要哪種加入，這通常是您最好的選擇。

左外連接：

添加到哪裡

... and establishment_id is not null and department_id is not null   

有時只是為了讓它完整我也這樣做

... and establishment_id<>'' and department_id<>''

由於您有一個鍵值儲存而不是模式，因此對結果進行任何假設變得很棘手（您真的應該……:-）。也就是說，您可以嘗試將聚合函式應用於列以消除空值：

SELECT `L1`.`entity_id`,
      MAX(`L1`.`value`) as 'company_id',
      MAX(`L2`.`value`) as 'entity_name',
      MAX(`P`.`value`) as 'person_name',
      MAX(`L4`.`value`) as 'establishment_id',
      MAX(`L5`.`value`) as 'department_id'
FROM `entities_attributes_values` `L1`
LEFT JOIN `entities_attributes_values` `L2` 
   ON `L1`.`entity_id` = `L2`.`entity_id` 
  AND `L2`.`entity_attributes_id` = 1
LEFT JOIN `entities_attributes_values` `L3` 
   ON `L1`.`entity_id` = `L3`.`entity_id` 
  AND `L3`.`entity_attributes_id` = 3
LEFT JOIN `persons_attributes_values` `P` 
   ON `L3`.`value` = `P`.`core_persons_id` -- is this correct?
  AND `P`.`core_persons_attributes_id` = 4
LEFT JOIN `entities_attributes_values` `L4` 
   ON `L1`.`entity_id` = `L4`.`entity_id` 
  AND `L4`.`entity_attributes_id` = 12
LEFT JOIN `entities_attributes_values` `L5` 
   ON `L1`.`entity_id` = `L5`.`entity_id` 
  AND `L5`.`entity_attributes_id` = 13
WHERE `L1`.`entity_attributes_id` = 2
GROUP BY `L1`.`entity_id`

我假設 entity_id, entity_attributes_id 是唯一的

引用自：https://dba.stackexchange.com/questions/104983