mysql 问题：从另一个表填充表但引用 ID 而不是名称

Question

我现在尝试使用“摘要”表中的数据填充“testMatch”表（如下）：

TESTMATCH TABLE

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| match_id         | int(11)      | NO   | PRI | NULL    |       | 
| match_date       | date         | YES  |     | NULL    |       | 
| ground           | varchar(50)  | YES  | MUL | NULL    |       | 
| homeTeam         | varchar(100) | YES  | MUL | NULL    |       | 
| awayTeam         | varchar(100) | YES  | MUL | NULL    |       | 
| matchResult      | varchar(100) | YES  | MUL | NULL    |       | 
| manOfMatch       | varchar(30)  | YES  |     | NULL    |       | 
| homeTeam_captain | int(10)      | YES  | MUL | NULL    |       | 
| homeTeam_keeper  | int(10)      | YES  | MUL | NULL    |       | 
| awayTeam_captain | int(10)      | YES  | MUL | NULL    |       | 
| awayTeam_keeper  | int(10)      | YES  | MUL | NULL    |       | 
+------------------+--------------+------+-----+---------+-------+

填充match_id没有问题-----> manOfMatch - 我在引入“homeTeam_captain”、“homeTeam_keeper”、“awayTeam_captain”和“awayTeam_keeper”时遇到了问题。

汇总表

mysql> DESCRIBE SUMMARY;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| matchID         | int(11)      | NO   | PRI | NULL    |       | 
| Test            | int(11)      | YES  |     | NULL    |       | 
| matchDate       | date         | YES  |     | NULL    |       | 
| Ground          | varchar(50)  | YES  |     | NULL    |       | 
| HomeTeam        | varchar(100) | YES  |     | NULL    |       | 
| AwayTeam        | varchar(100) | YES  |     | NULL    |       | 
| matchResult     | varchar(50)  | YES  |     | NULL    |       | 
| MarginRuns      | int(11)      | YES  |     | NULL    |       | 
| MarginWickets   | int(11)      | YES  |     | NULL    |       | 
| ManOfMatch      | varchar(40)  | YES  |     | NULL    |       | 
| HomeTeamCaptain | varchar(30)  | YES  |     | NULL    |       | 
| HomeTeamKeeper  | varchar(30)  | YES  |     | NULL    |       | 
| AwayTeamCaptain | varchar(30)  | YES  |     | NULL    |       | 
| AwayTeamKeeper  | varchar(30)  | YES  |     | NULL    |       | 
+-----------------+--------------+------+-----+---------+-------+

I need to somehow select the data from summary, get the corresponding player_id and input the player_id into my 'testMatch'. Player table below:

球员表

mysql> describe players;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| player_id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| player_surname | varchar(30)  | YES  |     | NULL    |                | 
| team           | varchar(100) | YES  | MUL | NULL    |                | 
+----------------+--------------+------+-----+---------+----------------+

所以要澄清一下，例如。我想从汇总表中选择 homeTeam_captain 数据，但不是名称，我想要相应的player_id。

我假设我需要使用某种连接/子查询来完成此操作...我尝试首先找到正确的选择查询以确保我提取正确的数据，并且我一直在使用以下代码测试（感谢用户 Larry_Croft 帮助我完成此任务）：

select matchID, player_id, player_surname, team from players p, summary s
where
s.hometeamcaptain = p.player_surname AND s.HomeTeam = p.team ORDER BY matchID;

但这正确地返回了 65 行（65 个匹配项），但我随后尝试了：

select matchID, player_id, player_surname, team from players p, summary s
where
s.hometeamKEEPER = p.player_surname AND s.HomeTeam = p.team ORDER BY matchID;

但这只返回 61 行（应该是 65 行），所以我的逻辑一定有错误。

一旦我让这个选择工作，我就需要以某种方式将它包含到我的“INSERT INTO.....SELECT”语句中，以将所有数据放入“testMatch”表中。

我希望这是有道理的，感谢您的帮助！

Answer 1

对我来说，似乎有一个 hometeamKEEPER 具有空值，或者 hometeamKEEPER 的值不在玩家表中。

使用以下查询，您应该能够找到不在players表中的homeroomKEEPER：

SELECT matchID, player_id, player_surname, team 
FROM players p 
     RIGHT JOIN summary s ON p.hometeamKEEPER = p.player_surname AND 
                             s.HomeTeam = p.team
ORDER BY matchID;