我有两张 table 。类似于下面给出的 table1 和 table2:
table1 有 ID(主键)和列 Aid、Bid 和 Cid，它们是表 2 的主键。
table1
ID Aid Bid Cid
-----------------
1 X Y Z
2 X Z Z
3 Y X X
-----------------
table2
ID NAME
------------------
X Abc
Y Bcd
Z Cde
------------------
我想要一个查询，它将以这种方式从 table1 中获取所有列(在用 table2 中给出的相应名称替换 Aid 、 Bid 和 Cid 之后):
ID A B C
1 Abc Bcd Cde
2 Abc Cde Cde
3 Bcd Abc Abc
你能告诉我 mysql 查询来做到这一点吗？
非常感谢您的回答。但是当我执行这些查询时我开始了这个:
+------+------+------+------+
| ID | A | B | C |
+------+------+------+------+
| 3 | bcd | abc | abc |
| 1 | abc | bcd | cde |
| 2 | abc | cde | cde |
+------+------+------+------+
此查询:
SELECT * FROM table1 JOIN table2 aa ON table1.Aid = aa.ID JOIN table2 bb ON table1.Bid = bb.ID JOIN table2 cc ON table1.Cid = cc.ID;给出了这个结果:
+------+------+------+------+------+------+------+------+------+------+
| ID | Aid | Bid | Cid | ID | NAME | ID | NAME | ID | NAME |
+------+------+------+------+------+------+------+------+------+------+
| 3 | Y | X | X | Y | bcd | X | abc | X | abc |
| 1 | X | Y | Z | X | abc | Y | bcd | Z | cde |
| 2 | X | Z | Z | X | abc | Z | cde | Z | cde |
+------+------+------+------+------+------+------+------+------+------+
我认为查询需要稍微改变一下..
最佳答案
这应该有效:
select table1.ID, a.NAME AS A, b.NAME AS B, c.NAME AS C
from table1
join table2 a on table1.Aid = a.ID
join table2 b on table1.Bid = b.ID
join table2 c on table1.Cid = c.ID
除此以外:
select table1.ID, a.NAME, b.NAME, c.NAME from table1 join (select * from table2) a on table1.Aid = a.ID join (select * from table2) b on table1.Bid = b.ID join (select * from table2) c on table1.Cid = c.ID
关于带有内连接的 Mysql 查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35053958/