MySQL LEFT JOIN 作为新列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10591952/
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
LEFT JOIN AS new column?
提问by supercoolville
Im trying to do multiple LEFT JOIN's on the same column of a table.I need to LEFT JOIN "table2.words" with "table1.color" and "table2.words" with "table1.food". How do I do this? and can I do it by making the left joined "table2.words" a new column?
我试图在表的同一列上执行多个左连接。我需要将“table2.words”与“table1.color”和“table2.words”与“table1.food”进行左连接。我该怎么做呢？我可以通过将左侧加入的“table2.words”作为新列来实现吗？
My SQL code:
我的 SQL 代码：
SELECT table1.id, table1.color, table2.words
FROM table1
LEFT JOIN table2 ON table1.color=table2.id
LEFT JOIN table2 ON table1.food=table2.id
table1:
表格1：
--------------------------------
| id | color | food |
--------------------------------
| 1 | 1 | 3 |
| 2 | 1 | 4 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 2 | 3 |
| 6 | 2 | 4 |
| 7 | 2 | 3 |
| 8 | 2 | 4 |
--------------------------------
table2:
表2：
---------------------------
| id | words |
---------------------------
| 1 | yellow |
| 2 | blue |
| 3 | cookies |
| 4 | milk |
---------------------------
What Im trying to output:
我想输出什么：
----------------------------------------
| id | colorname | foodname |
----------------------------------------
| 1 | yellow | cookies |
| 2 | yellow | milk |
| 3 | yellow | cookies |
| 4 | yellow | milk |
| 5 | blue | cookies |
| 6 | blue | milk |
| 7 | blue | cookies |
| 8 | blue | milk |
----------------------------------------
Note: I cant change the table structures.
注意：我无法更改表结构。
回答by RolandoMySQLDBA
SELECT
table1.id,
table2_A.words colorname,
table2_B.words foodname
FROM table1
LEFT JOIN table2 table2_A ON table1.color=table2_A.id
LEFT JOIN table2 table2_B ON table1.food=table2_B.id;
Your Sample Data
您的样本数据
mysql> drop database if exists supercoolville;
Query OK, 2 rows affected (0.06 sec)
mysql> create database supercoolville;
Query OK, 1 row affected (0.00 sec)
mysql> use supercoolville;
Database changed
mysql> create table table1
-> (
-> id int not null auto_increment,
-> color int,
-> food int,
-> primary key (id)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into table1 (color,food) values
-> (1,3),(1,4),(1,3),(1,4),
-> (2,3),(2,4),(2,3),(2,4);
Query OK, 8 rows affected (0.06 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> create table table2
-> (
-> id int not null auto_increment,
-> words varchar(20),
-> primary key (id)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into table2 (words) values
-> ('yellow'),('blue'),('cookies'),('milk');
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from table1;
+----+-------+------+
| id | color | food |
+----+-------+------+
| 1 | 1 | 3 |
| 2 | 1 | 4 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 2 | 3 |
| 6 | 2 | 4 |
| 7 | 2 | 3 |
| 8 | 2 | 4 |
+----+-------+------+
8 rows in set (0.01 sec)
mysql> select * from table2;
+----+---------+
| id | words |
+----+---------+
| 1 | yellow |
| 2 | blue |
| 3 | cookies |
| 4 | milk |
+----+---------+
4 rows in set (0.00 sec)
Results of My Query
我的查询结果
mysql> SELECT
-> table1.id,
-> table2_A.words colorname,
-> table2_B.words foodname
-> FROM table1
-> LEFT JOIN table2 table2_A ON table1.color=table2_A.id
-> LEFT JOIN table2 table2_B ON table1.food=table2_B.id
-> ;
+----+-----------+----------+
| id | colorname | foodname |
+----+-----------+----------+
| 1 | yellow | cookies |
| 2 | yellow | milk |
| 3 | yellow | cookies |
| 4 | yellow | milk |
| 5 | blue | cookies |
| 6 | blue | milk |
| 7 | blue | cookies |
| 8 | blue | milk |
+----+-----------+----------+
8 rows in set (0.00 sec)
mysql>
UPDATE 2012-05-14 19:10 EDT
更新 2012-05-14 19:10 EDT
In the event there are values for food or color that do not exist, here is the adjusted query:
如果食物或颜色的值不存在，这里是调整后的查询：
SELECT
table1.id,
IFNULL(table2_A.words,'Unknown Color') colorname,
IFNULL(table2_B.words,'Unknown Food') foodname
FROM table1
LEFT JOIN table2 table2_A ON table1.color=table2_A.id
LEFT JOIN table2 table2_B ON table1.food=table2_B.id;
I will add rows to table1 and run this new query
我将向 table1 添加行并运行这个新查询
mysql> drop database if exists supercoolville;
Query OK, 2 rows affected (0.13 sec)
mysql> create database supercoolville;
Query OK, 1 row affected (0.00 sec)
mysql> use supercoolville;
Database changed
mysql> create table table1
-> (
-> id int not null auto_increment,
-> color int,
-> food int,
-> primary key (id)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into table1 (color,food) values
-> (1,3),(1,4),(1,3),(1,4),
-> (2,3),(2,4),(2,3),(2,4),
-> (5,3),(5,4),(2,6),(2,8);
Query OK, 12 rows affected (0.07 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> create table table2
-> (
-> id int not null auto_increment,
-> words varchar(20),
-> primary key (id)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into table2 (words) values
-> ('yellow'),('blue'),('cookies'),('milk');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from table1;
+----+-------+------+
| id | color | food |
+----+-------+------+
| 1 | 1 | 3 |
| 2 | 1 | 4 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 2 | 3 |
| 6 | 2 | 4 |
| 7 | 2 | 3 |
| 8 | 2 | 4 |
| 9 | 5 | 3 |
| 10 | 5 | 4 |
| 11 | 2 | 6 |
| 12 | 2 | 8 |
+----+-------+------+
12 rows in set (0.00 sec)
mysql> select * from table2;
+----+---------+
| id | words |
+----+---------+
| 1 | yellow |
| 2 | blue |
| 3 | cookies |
| 4 | milk |
+----+---------+
4 rows in set (0.00 sec)
mysql> SELECT
-> table1.id,
-> IFNULL(table2_A.words,'Unknown Color') colorname,
-> IFNULL(table2_B.words,'Unknown Food') foodname
-> FROM table1
-> LEFT JOIN table2 table2_A ON table1.color=table2_A.id
-> LEFT JOIN table2 table2_B ON table1.food=table2_B.id;
+----+---------------+--------------+
| id | colorname | foodname |
+----+---------------+--------------+
| 1 | yellow | cookies |
| 2 | yellow | milk |
| 3 | yellow | cookies |
| 4 | yellow | milk |
| 5 | blue | cookies |
| 6 | blue | milk |
| 7 | blue | cookies |
| 8 | blue | milk |
| 9 | Unknown Color | cookies |
| 10 | Unknown Color | milk |
| 11 | blue | Unknown Food |
| 12 | blue | Unknown Food |
+----+---------------+--------------+
12 rows in set (0.00 sec)
mysql>
Given any invalid data, LEFT JOINin still needed.
鉴于任何无效数据，LEFT JOIN仍然需要。
回答by ericosg
try:
尝试：
SELECT table1.id, colorcodes.words, foodcodes.words
FROM table1
LEFT JOIN table2 as colorcodes
ON colorcodes.id = table1.color
LEFT JOIN table2 as foodcodes
ON foodcodes.id= table1.food
回答by Kevin Bedell
Here is the query:
这是查询：
SELECT a.id as id, b.words as colorname, c.words as foodname
FROM table1 a
LEFT JOIN table2 b ON b.id = a.color
LEFT JOIN table2 c ON c.id = a.food
Note: It looks from your data that a LEFT JOINis not needed. If there are no rows in table1 where either color or food are null, then you can leave off the LEFT.
注意：从您的数据看来，LEFT JOIN不需要 a。如果 table1 中没有 color 或 food 为空的行，那么您可以不使用LEFT.
