如果我有两个表,第一个表,tbl_names
是:
+----+-----------+
| id | name |
+----+-----------+
| 1 | Name1 |
+----+-----------+
| 2 | Name2 |
+----+-----------+
| 3 | Name3 |
+----+-----------+
第二个,tbl_material
是:
+----+--------+--------+--------+
| id | field1 | field2 | field3 |
+----+--------+--------+--------+
| 1 | 1 | 2 | 3 |
+----+--------+--------+--------+
| 2 | 3 | 2 | 1 |
+----+--------+--------+--------+
其中field1
、field2
和field3
都是引用tbl_names
的外键列。如何在MySQL中制作连接/选择语句以生成以下输出表:
+----+--------+--------+--------+
| id | field1 | field2 | field3 |
+----+--------+--------+--------+
| 1 | Name1 | Name2 | Name3 |
+----+--------+--------+--------+
| 2 | Name3 | Name2 | Name1 |
+----+--------+--------+--------+
我可以执行以下操作:
SELECT m.id as id, n.name as field1
FROM tbl_names n JOIN tbl_material m
ON m.field1 = n.id
并一次获取一列的输出,但是如果我尝试尝试做更多的事情,我觉得我最终会看到一些东西:
+----+--------+--------+--------+
| id | field1 | field2 | field3 |
+----+--------+--------+--------+
| 1 | Name1 | 2 | 3 |
+----+--------+--------+--------+
| 1 | 1 | Name2 | 3 |
+----+--------+--------+--------+
...
我是否需要在此处使用类似group by
的东西来减少输出以获得正确的输出行数?
您需要JOIN
tbl_names
表3次,每次一次才能获得每个field
的名称:
SELECT m.id,
n1.name AS field1,
n2.name AS field2,
n3.name AS field3
FROM tbl_material m
JOIN tbl_names n1 ON n1.id = m.field1
JOIN tbl_names n2 ON n2.id = m.field2
JOIN tbl_names n3 ON n3.id = m.field3
ORDER BY m.id
输出:
id field1 field2 field3
1 Name1 Name2 Name3
2 Name3 Name2 Name1
在 dbfiddle 上演示