我试着搜索了很多例子，但我的有点复杂，因为我有很多列。下面是一个简化的版本:
用户+--------+----------+
| userid | username |
+--------+----------+
| 1 | Tom |
+--------+----------+
| 2 | Dick |
+--------+----------+
| 3 | Harry |
+--------+----------+
+--------+----------+
| typeid | typename |
+--------+----------+
| 1 | Desktop |
+--------+----------+
| 2 | Laptop |
+--------+----------+
+------+--------+-------------------+------------------+--------------------+--------------------+
| pcid | typeid | bought_by_user_id | assigned_user_id | created_by_user_id | updated_by_user_id |
+------+--------+-------------------+------------------+--------------------+--------------------+
| 1 | 2 | 1 | 3 | 1 | 3 |
+------+--------+-------------------+------------------+--------------------+--------------------+
| 2 | 1 | 2 | 2 | 2 | 2 |
+------+--------+-------------------+------------------+--------------------+--------------------+
| 3 | 1 | 3 | 2 | 3 | 1 |
+------+--------+-------------------+------------------+--------------------+--------------------+
我想达到的结果是
+------+---------+----------------+---------------+-----------------+-----------------+
| pcid | type | bought_by_user | assigned_user | created_by_user | updated_by_user |
+------+---------+----------------+---------------+-----------------+-----------------+
| 1 | Desktop | Tom | Harry | Tom | Harry |
+------+---------+----------------+---------------+-----------------+-----------------+
| 2 | Laptop | Dick | Dick | Dick | Dick |
+------+---------+----------------+---------------+-----------------+-----------------+
| 3 | Desktop | Harry | Dick | Harry | Tom |
+------+---------+----------------+---------------+-----------------+-----------------+
我试过使用多个on，像这样:
SELECT * FROM computers
LEFT JOIN types ON computers.typeid = types.typeid
LEFT JOIN users
ON users.userid = computers.bought_by_user_id
ON users.userid = computers.assigned_user_id
ON users.userid = computers.created_by_user_id
ON users.userid = computers.updated_by_user_id
但这不起作用…想买吗?
您需要在users表上进行多个连接:
SELECT
computers.pcid,
types.typename,
bought.username as bought_by_user,
assigned.username as assigned_by_user,
created.username as created_by_user,
updated.username as updated_by_user
FROM computers
LEFT JOIN types ON computers.typeid = types.typeid
LEFT JOIN users bought ON computers.bought_by_user_id = bought.userid
LEFT JOIN users assigned ON computers.assigned_by_user_id = assigned.userid
LEFT JOIN users created ON computers.created_by_user_id = created.userid
LEFT JOIN users updated ON computers.updated_by_user_id = updated.userid
对于computers表中的4个用户列，分别执行一个连接到users表的操作。每个已连接的表都有不同的别名，因此在select语句中可以访问已连接表的任何列值。