我有兩個名為“Player”和“Match”的表。在“Player”中，有兩列：Player.id 和 Player.name，在“Match”表中，我有許多列與大約 20000 場足球比賽相關，其中 22 列與參加特定比賽的球員有關。

在“比賽”表中，我在各自的列中有球員 ID，假設我想要一個顯示每場比賽中球員姓名的查詢。（我想用他們的名字替換他們的 ID）。為此，我必須加入這兩張牌桌 22 次，每個玩家一張。例如獲取 player1 的名字：

select Match.id, Match.date, p1.name
from Match, Player as p1
Where Match.home_player1 == p1.id

現在，如果我也想獲得例如 player2 的名字，我必須再次加入。這將是：

select Match.id, Match.date, p1.name, p2.name
from Match, Player as p1, Player as p2
Where Match.home_player1 == p1.id and Match.home_player2 == p2.id

如果我想獲得所有 22 名玩家，我必須進行 22 次加入。這既不方便，又在我看來效率很低，因為行數很大。

有什麼方便的方法可以做我想做的事嗎？

如果有幫助，則數據庫位於 sqlite 中，如果所有名稱最終都在一列中並在它們之間使用分隔符，則無關緊要。

更好的（設計）解決方案是使用另一個名為matchplayer. 這個簡單的表格將由兩列match_id和player_id.

你再JOIN上match_id再上player_id。

SELECT m.id, m.date, p.name from 
FROM Match as m 
   JOIN matchplayer as mp
       ON mp.match_id = m.id
   JOIN Player as p
       ON mp.player_id = p.id

那場比賽你是否只有 11、10 或 12 名球員都沒關係。您始終會獲得參加給定比賽的球員的完整列表。這甚至可以包括替換。

Unpivot 到新表的小例子

我在db<>fiddle上創建了一個小範例，向您展示如何將匹配表中的數據反透視到新表中，以便更輕鬆地檢索玩家的姓名。

球員表

create table Player (
id int,
name varchar(100)
)

…和一些數據：

insert into Player
(
   id, name
)
values 
(1, 'Bernd Leno (1)'), 
(2, 'Aaron Ramsdate (32)'), 
(3, 'Kieran Tierney (3)'), 
(4, 'Nick Pope (1)'), 
(5, 'Will Norris (25)'), 
(6, 'Wayne Hennessey (13)')

匹配表

create table Match
(
   id int, 
   matchdate date, 
   home_player1 int, 
   home_player2 int, 
   home_player3 int, 
   away_player1 int, 
   away_player2 int, 
   away_player3 int
)

…以及一些與之相關的數據：

insert into Match (id, matchdate, home_player1, home_player2, home_player3, away_player1, away_player2, away_player3)
values
(1, '2021-09-29', 1, 2, 3, 4, 5, 6),
(2, '2021-09-30', 4, 5, 6, 1, 2, 3)

比賽選手錶

這是我們之後要用來加入的表

create table MatchPlayer 
(
   match_id int, 
   player_id int
)

選定的未透視數據

select match_id, player_id from 
(select Match.id as match_id, Match.home_player1 as player_id from Match
UNION ALL
select Match.id as match_id, Match.home_player2 as player_id from Match
UNION ALL
select Match.id as match_id, Match.home_player3 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player1 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player2 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player3 as player_id from Match

) as unpivottable

結果是：

匹配ID | player_id
-------: | --------:
 1 | 1
 2 | 4
 1 | 2
 2 | 5
 1 | 3
 2 | 6
 1 | 4
 2 | 1
 1 | 5
 2 | 2
 1 | 6
 2 | 3

將選定的未透視數據插入 MatchPlayer

insert into matchplayer
select match_id, player_id from 
(select Match.id as match_id, Match.home_player1 as player_id from Match
UNION ALL
select Match.id as match_id, Match.home_player2 as player_id from Match
UNION ALL
select Match.id as match_id, Match.home_player3 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player1 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player2 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player3 as player_id from Match

) as unpivottable
where unpivottable.match_id 

檢索簡單匹配概述

SELECT m.id, m.matchdate, p.name  
FROM Match as m 
   JOIN MatchPlayer as mp
       ON mp.match_id = m.id
   JOIN Player as p
       ON mp.player_id = p.id

結果是：

編號 | 比賽日期 | 姓名 
-: | :--------- | :-------------------
 1 | 2021-09-29 | 伯恩德·雷諾 (1) 
 1 | 2021-09-29 | 亞倫拉姆斯戴特 (32)
 1 | 2021-09-29 | 基蘭蒂爾尼 (3) 
 1 | 2021-09-29 | 尼克·波普 (1) 
 1 | 2021-09-29 | 威爾·諾里斯 (25) 
 1 | 2021-09-29 | 韋恩·亨尼西 (13)
 2 | 2021-09-30 | 伯恩德·雷諾 (1) 
 2 | 2021-09-30 | 亞倫拉姆斯戴特 (32)
 2 | 2021-09-30 | 基蘭蒂爾尼 (3) 
 2 | 2021-09-30 | 尼克·波普 (1) 
 2 | 2021-09-30 | 威爾·諾里斯 (25) 
 2 | 2021-09-30 | 韋恩·亨尼西 (13)

這是一個基於一些假設的非常簡單的範例。

旁注

球員數據取自伯恩利足球俱樂部和 阿森納球隊截至今天（2021-10-04）的英超聯賽首頁。

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