mysql - MySQL 中使用多个外键连接表

Question

我有 2 张 table

骑行(从一处到另一处)

+------+-------+-------+-------+-------+
| id   | start | end   | pointA| pointB|
+------+-------+-------+-------+-------+
| 1    | xxx   |    xx |    1  |    2  |
| 2    | xxx   |    xx |    2  |    1  |
+------+-------+-------+-------+-------+

积分

+------+-------------+
| id   | desc        |
+------+-------------+
| 1    | "Chicago"   |    
| 2    | "NYC"       |    
+------+-------------+

例如:

当一个人从 A 点骑行到 B 点时，它会被记录在 rides 表中。 pointA 和 pointB 都是来自表 points

的 rides 表中的 FK

如何在MySQL中获得像这样的SQL输出，例如id = 1:

+------+-------+-------+-------+-------+
| id   | start | end   | pointA| pointB|
+------+-------+-------+-------+-------+
| 1    | xxx   |    xx |Chicago|  NYC  |

Answer 1

您可以通过为每个表指定不同的别名来连接同一个表两次:

SELECT r.id, r.start, r.end, pA.desc as pointA, pB.desc as pointB
FROM rides r
JOIN points pA ON pA.id = r.pointA
JOIN points pB ON pB.id = r.pointB
WHERE r.id = 1;