MySQL UNION 问题 - 无法获取正确的数据

Question

我需要标准化包含板球运动员的表格：

 
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| matchID    | int(11)      | NO   | PRI | 0       |       | 
| innings    | int(11)      | NO   | PRI | 0       |       | 
| Bat1Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat1Score  | int(11)      | YES  |     | NULL    |       | 
| balls1     | int(11)      | YES  |     | NULL    |       | 
| sixes1     | int(11)      | YES  |     | NULL    |       | 
| fours1     | int(11)      | YES  |     | NULL    |       | 
| out1       | varchar(10)  | YES  |     | NULL    |       | 
| catcher1   | varchar(30)  | YES  |     | NULL    |       | 
| bowler1    | varchar(30)  | YES  |     | NULL    |       | 
| Bat2Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat2Score  | int(11)      | YES  |     | NULL    |       | 
| balls2     | int(11)      | YES  |     | NULL    |       | 
| sixes2     | int(11)      | YES  |     | NULL    |       | 
| fours2     | int(11)      | YES  |     | NULL    |       | 
| out2       | varchar(10)  | YES  |     | NULL    |       | 
| catcher2   | varchar(30)  | YES  |     | NULL    |       | 
| bowler2    | varchar(30)  | YES  |     | NULL    |       | 
| Bat3Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat3Score  | int(11)      | YES  |     | NULL    |       | 
| balls3     | int(11)      | YES  |     | NULL    |       | 
| sixes3     | int(11)      | YES  |     | NULL    |       | 
| fours3     | int(11)      | YES  |     | NULL    |       | 
| out3       | varchar(10)  | YES  |     | NULL    |       | 
| catcher3   | varchar(30)  | YES  |     | NULL    |       | 
| bowler3    | varchar(30)  | YES  |     | NULL    |       | 
| Bat4Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat4Score  | int(11)      | YES  |     | NULL    |       | 
| balls4     | int(11)      | YES  |     | NULL    |       | 
| sixes4     | int(11)      | YES  |     | NULL    |       | 
| fours4     | int(11)      | YES  |     | NULL    |       | 
| out4       | varchar(10)  | YES  |     | NULL    |       | 
| catcher4   | varchar(30)  | YES  |     | NULL    |       | 
| bowler4    | varchar(30)  | YES  |     | NULL    |       | 
| Bat5Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat5Score  | int(11)      | YES  |     | NULL    |       | 
| balls5     | int(11)      | YES  |     | NULL    |       | 
| sixes5     | int(11)      | YES  |     | NULL    |       | 
| fours5     | int(11)      | YES  |     | NULL    |       | 
| out5       | varchar(10)  | YES  |     | NULL    |       | 
| catcher5   | varchar(30)  | YES  |     | NULL    |       | 
| bowler5    | varchar(30)  | YES  |     | NULL    |       | 
| Bat6Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat6Score  | int(11)      | YES  |     | NULL    |       | 
| balls6     | int(11)      | YES  |     | NULL    |       | 
| sixes6     | int(11)      | YES  |     | NULL    |       | 
| fours6     | int(11)      | YES  |     | NULL    |       | 
| out6       | varchar(10)  | YES  |     | NULL    |       | 
| catcher6   | varchar(30)  | YES  |     | NULL    |       | 
| bowler6    | varchar(30)  | YES  |     | NULL    |       | 
| Bat7Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat7Score  | int(11)      | YES  |     | NULL    |       | 
| balls7     | int(11)      | YES  |     | NULL    |       | 
| sixes7     | int(11)      | YES  |     | NULL    |       | 
| fours7     | int(11)      | YES  |     | NULL    |       | 
| out7       | varchar(10)  | YES  |     | NULL    |       | 
| catcher7   | varchar(30)  | YES  |     | NULL    |       | 
| bowler7    | varchar(30)  | YES  |     | NULL    |       | 
| Bat8Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat8Score  | int(11)      | YES  |     | NULL    |       | 
| balls8     | int(11)      | YES  |     | NULL    |       | 
| sixes8     | int(11)      | YES  |     | NULL    |       | 
| fours8     | int(11)      | YES  |     | NULL    |       | 
| out8       | varchar(10)  | YES  |     | NULL    |       | 
| catcher8   | varchar(30)  | YES  |     | NULL    |       | 
| bowler8    | varchar(30)  | YES  |     | NULL    |       | 
| Bat9Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat9Score  | int(11)      | YES  |     | NULL    |       | 
| balls9     | int(11)      | YES  |     | NULL    |       | 
| sixes9     | int(11)      | YES  |     | NULL    |       | 
| fours9     | int(11)      | YES  |     | NULL    |       | 
| out9       | varchar(10)  | YES  |     | NULL    |       | 
| catcher9   | varchar(30)  | YES  |     | NULL    |       | 
| bowler9    | varchar(30)  | YES  |     | NULL    |       | 
| Bat10Name  | varchar(30)  | YES  |     | NULL    |       | 
| Bat10Score | int(11)      | YES  |     | NULL    |       | 
| balls10    | int(11)      | YES  |     | NULL    |       | 
| sixes10    | int(11)      | YES  |     | NULL    |       | 
| fours10    | int(11)      | YES  |     | NULL    |       | 
| out10      | varchar(10)  | YES  |     | NULL    |       | 
| catcher10  | varchar(30)  | YES  |     | NULL    |       | 
| bowler10   | varchar(30)  | YES  |     | NULL    |       | 
| Bat11Name  | varchar(30)  | YES  |     | NULL    |       | 
| Bat11Score | int(11)      | YES  |     | NULL    |       | 
| balls11    | int(11)      | YES  |     | NULL    |       | 
| sixes11    | int(11)      | YES  |     | NULL    |       | 
| fours11    | int(11)      | YES  |     | NULL    |       | 
| out11      | varchar(10)  | YES  |     | NULL    |       | 
| catcher11  | varchar(30)  | YES  |     | NULL    |       | 
| bowler11   | varchar(30)  | YES  |     | NULL    |       | 
| extras     | int(11)      | YES  |     | NULL    |       | 
| wides      | int(11)      | YES  |     | NULL    |       | 
| noBalls    | int(11)      | YES  |     | NULL    |       | 
| byes       | int(11)      | YES  |     | NULL    |       | 
| legByes    | int(11)      | YES  |     | NULL    |       | 
| score      | int(11)      | YES  |     | NULL    |       | 
| wickets    | int(11)      | YES  |     | NULL    |       | 
| overs      | float        | YES  |     | NULL    |       | 
| runrate    | float        | YES  |     | NULL    |       | 
| team       | varchar(100) | YES  |     | NULL    |       | 
+------------+--------------+------+-----+---------+-------+

我感兴趣的行是所有 Bat?Name 和 Team 字段，例如。 Bat1Name、Bat2Name...Bat11Name 以及他们所属的团队。

我创建了一个玩家表：

 
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    |                | 
    +----------------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

我使用以下 UNION 代码成功将玩家的姓名（没有重复）选择到玩家的表中：

INSERT INTO players (player_surname)
SELECT DISTINCT bat1Name from details
UNION
SELECT DISTINCT bat2Namen from details
UNION
SELECT DISTINCT bat3Name from details
(etc............................)
SELECT DISTINCT bat11Name from details; 

这给了我 300 条记录，这是正确的。

但后来意识到我需要包括团队，所以我添加了团队：

 
INSERT INTO players (player_surname, team)
SELECT DISTINCT bat1Name,team from details
UNION
SELECT DISTINCT bat2Namen,team from details
UNION
SELECT DISTINCT bat3Name,team from details
(etc.............................)
SELECT DISTINCT bat11Name,team from details;

但这为我提供了 310 条记录，并说（例如）史密斯为英国、新西兰和南非效力，但这是不正确的。它还错误地删除了其他一些内容。我不知所措了！

我认为我的 UNION 方法有缺陷，那么我怎样才能让所有击球手与他们相应的球队一起出局呢？

Answer 1

如果有两个同姓的球员在不同的球队踢球，他们将在第一个查询中合并，但不会在第二个查询中合并：

bat1   bat2   team
Smith  Jones  UK
Doe    Smith  Zealand

第一个查询将返回 Smith 一次，第二个查询返回两次。

您的第一个查询很可能是错误的，而不是第二个查询。尝试为您的第一个查询运行此命令：

SELECT  batname
FROM    (
        SELECT  bat1Name AS batname, team
        FROM   details
        UNION
        SELECT  bat2Name, team
        FROM   details
        UNION  …
        ) q

您不需要在此处执行 DISTINCT：UNION 将自动处理此操作。

Answer 2

正如夸斯诺伊所说，这可能是第一个查询是错误的，因为第二个查询会提取出正确的数据。

此外，球员表应该只存储与球员相关的数据以及与另一个存储球队的表的关系。