連接具有相同鍵的表，第二個表具有多個鍵值

我有兩個帶有共享鍵的表，我正在嘗試加入它們並將結果輸出到一個表中。我遇到的問題是第二個表有多個共享鍵值。

主機

host_object_id | hostname  | 
1              | America1  |
2              | Another1  |

誰的

object_id      | varname   |  varvalue |
1              | type      |  Country  |
1              | location  |  N.A.     |
1              | capital   |  D.C.     |
2              | type      |  Another  |
2              | location  |  Location |
2              | capital   |  Example  |

我想要得到的是

Hostname       | Type      |  Location |
America        | Country   |  N.A.     |
Another1       | Another   |  Location |

你有兩種可能：

1. 兩個連接。如果您進行有限搜尋並且不想要所有可用屬性（即：您忽略大寫） ，這可能是一個不錯的選擇

SELECT
   hostname, 
   type_var.varvalue AS `type`, 
   location_var.varvalue AS location
FROM
   hosts
   JOIN vars AS type_var  
       ON (type_var.object_id = hosts.host_object_id) AND  type_var.varname = 'type'
   JOIN vars AS location_var 
       ON (location_var.object_id = hosts.host_object_id) AND location_var.varname = 'location' ;

2. 最大（案例）和 GROUP BY。這是標準 SQL 中的典型方法unpivot。type如果您想要所有屬性（ , location, capital），或者您不使用顯著限制結果的 a 來限制查詢，這通常會更快WHERE。（在你的情況下，沒有WHERE，我會選擇這種方法：

SELECT
   hostname, 
   max(case when varname = 'type'     then varvalue end) AS `type`, 
   max(case when varname = 'location' then varvalue end) AS location
FROM
   hosts
   JOIN vars 
       ON (vars.object_id = hosts.host_object_id) 
GROUP BY
   object_id ;

在這兩種情況下，您都會得到：

主機名 | 類型 | 地點
:------- | :------ | :-------
美國1 | 國家 | 不適用 
另一個1 | 另一個 | 地點

---

注 1：這假設vars有PRIMARY KEY (object_id, varname). 否則，您可能有多個或typeper location，object_id這將在第一種情況下為您提供超過一行object_id，而在第二種情況下只有一個，選擇其中的最大值。

注意 2：這些查詢是標準 SQL，不僅適用於 MySQL，而且適用於大多數 SQL RDBMS。

---

你可以在*這裡*查看dbfiddle的所有內容

不提供 ascii 表，而是考慮create table和insert語句。這就是我的假設：

create table hosts 
( host_object_id int not null primary key
, hostname varchar(20) not null);

create table vars 
( object_id int not null
, varname varchar(20) not null
, varvalue varchar(20) not null
,    primary key (object_id, varname));

insert into hosts (...) values (1,'America')
                      , (2, 'Another1');
insert into vars (...) values (1,'type','Country')
                     , (1, 'location', 'N.A')
                     , (1,'capital', 'D.C');

這似乎是某種 EAV 結構，由於結構靈活，這可能很誘人，但也意味著您的查詢很快就會變得非常複雜並且性能會受到影響。

您可以使用 case 語句來獲取正確的varname值，然後通過 - 例如 - max（任何值大於空值）選擇一個非空值：

select hostname, max(type), max(location) 
from (
   select h.hostname
        , case when v.varname = 'type' then v.varvalue end as type
        , case when v.varname = 'location' then v.varvalue end as location 
   from hosts h 
   join vars v 
       on h.host_object_id = v.object_id
) as t 
group by hostname;

您可以通過在沒有外部選擇的情況下應用 max 來縮短這一點：

select h.hostname
    , max(case when v.varname = 'type' then v.varvalue end) as type
    , max(case when v.varname = 'location' then v.varvalue end) as location 
from hosts h 
join vars v 
   on h.host_object_id = v.object_id 
group by h.hostname;

如果可能的話，請考慮將 vars 表更改為：

create table vars 
( object_id int not null primary key
, type varchar(20) not null
, location char(4) not null
, capital char(...) not null)

它不僅有益於性能，還可以讓您更好地控制域的類型、位置等。

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