Mysql
連接具有相同鍵的表,第二個表具有多個鍵值
我有兩個帶有共享鍵的表,我正在嘗試加入它們並將結果輸出到一個表中。我遇到的問題是第二個表有多個共享鍵值。
主機
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 |
你有兩種可能:
- 兩個連接。如果您進行有限搜尋並且不想要所有可用屬性(即:您忽略大寫) ,這可能是一個不錯的選擇
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' ;
- 最大(案例)和 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)
. 否則,您可能有多個或type
perlocation
,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)
它不僅有益於性能,還可以讓您更好地控制域的類型、位置等。