MySQL MySQL删除左连接上的重复列，3个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13908249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Removing duplicate columns on Left Join, 3 tables
提问by Mohammed Ahmed
I have three tables, each have a foreign key. When I perform a join, I get duplicate columns.
我有三个表，每个表都有一个外键。当我执行连接时，我得到重复的列。
Given
给定的
mysql> describe Family;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| HEAD_name | varchar(45) | NO | PRI | | |
| Family_Size | int(11) | NO | | | |
| Gender | char(1) | NO | | | |
| ID_Number | int(11) | NO | | | |
| DOB | date | NO | | | |
| Supervisor_ID | int(11) | NO | MUL | | |
+---------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> describe SUPERVISOR;
+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| Supervisor_ID | int(11) | NO | PRI | | |
| Supervisor_Name | varchar(45) | NO | | | |
| Supervisor_Number | decimal(10,0) | NO | | | |
| Center_ID | int(11) | NO | MUL | | |
+-------------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe CENTER;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Center_ID | int(11) | NO | PRI | | |
| Location | varchar(45) | NO | | | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
My query statement:
我的查询语句：
SELECT * from Family
JOIN SUPERVISOR on ( Family.Supervisor_ID = SUPERVISOR.Supervisor_ID)
JOIN CENTER on (SUPERVISOR.Center_ID = CENTER.Center_ID);
My objective is to get one row of all the columns from the join without duplicate columns. So what is the SQL statement syntax that I should use?
我的目标是从连接中获取所有列中的一行而没有重复的列。那么我应该使用的 SQL 语句语法是什么？
回答by Tanzeel Kazi
By default MySQL will return all columns for all tables if you use *. You will need to explicitly enter column names in your query to retrieve them the way you want. Use the query as follows:
默认情况下，如果您使用*. 您需要在查询中明确输入列名，以按照您想要的方式检索它们。使用查询如下：
SELECT A.HEAD_name, A.Family_Size, A.Gender, A.ID_Number, A.DOB,
B.Supervisor_ID, B.Supervisor_Name, B.Supervisor_Number,
C.Center_ID, C.Location
FROM Family A
JOIN SUPERVISOR B on ( A.Supervisor_ID = B.Supervisor_ID)
JOIN CENTER C on (B.Center_ID = C.Center_ID);
回答by bharatj
The problem can be solved by "USING" keyword.
这个问题可以通过“USING”关键字解决。
SELECT * from Family
JOIN SUPERVISOR on ( Family.Supervisor_ID = SUPERVISOR.Supervisor_ID)
JOIN CENTER on (SUPERVISOR.Center_ID = CENTER.Center_ID);
In your case the query will become
在您的情况下，查询将变为
SELECT * FROM FAMILY
JOIN (SUPERVISOR JOIN CENTER USING(Center_ID)) USING(Supervisor_ID);
The point is Simple, If you have two Tables A(a,b) and B(b,c) then after joining to produce the result in the form of (a,b,c)
重点很简单，如果你有两个表 A(a,b) 和 B(b,c) 那么在加入后以 (a,b,c) 的形式产生结果
Select *
from A JOIN B USING(b);
will give the Result-Set with three columns(a,b,c)
将给出三列（a，b，c）的结果集
NOTE : Since I don't know whether we can use multiple params in Using, therefore I made it as subquery.
注意：由于我不知道我们是否可以在 Using 中使用多个参数，因此我将其设为子查询。
回答by Mickle Foretic
You are not getting duplicate columns, what you are really getting is the Supervisor_ID column from table Family (that is Family.Supervisor_ID) and Supervisor_ID from table Supervisor (that is Supervisor.Supervisor_ID) but to your result set, you will see both as Supervisor_ID, and that is why you think they are duplicated. The same will happen with Center_iD.
您没有得到重复的列，您真正得到的是来自表 Family（即 Family.Supervisor_ID）的 Supervisor_ID 列和来自表 Supervisor（即 Supervisor.Supervisor_ID）的 Supervisor_ID 列，但对于您的结果集，您将看到两者均为 Supervisor_ID ，这就是为什么您认为它们是重复的。Center_iD 也会发生同样的情况。
The solution is to specify the fields that you need from each table, and decide if you need to get the Supervisor_ID and Center_ID and which table to get it from.
解决方案是从每个表中指定您需要的字段，并决定是否需要获取 Supervisor_ID 和 Center_ID 以及从哪个表中获取。
回答by ArrowInTree
According to oreilly,
根据奥雷利的说法，
Look for an explain plan.
You can do a _select x from y inner join.... where w=z ... this is just an example
寻找解释计划。
你可以做一个 _select x from y inner join.... where w=z ... 这只是一个例子
