我有这个查询:

SELECT DISTINCT p1.rootid AS user_id, p1.rid AS friend_id
 FROM relations p1
WHERE rootid = 1246
   OR rootid IN (SELECT p2.rid
              FROM relations p2
              WHERE rootid = 1246);


结果是这样的:

mysql - 如何获得每个用户的ID?-LMLPHP

我也有一个包含名称的表。像这样:

// users
+------+_--------+
|  id  |  name   |
+------+---------+
| 1246 | Jack    |
| 1247 | Peter   |
| 1246 | Ali     |
| .    | .       |
| .    | .       |
| .    | .       |
+------+---------+


现在,我想在输出中获取名称而不是ID。注意,user_idfriend_id都引用users表。我怎样才能做到这一点?

最佳答案

试试这个

SELECT u1.name user_name, u2.name friend_name from relations r
INNER JOIN users u1 ON u1.id = r.user_id
INNER JOIN users u2 ON u2.id = r.friend_id
where r.user_id = 1246


希望这能解决您的问题。

08-05 06:52