php - 连接相同表的多行

Question

我正在尝试从 1 个表中提取 2 组不同的数据。我不太确定如何处理这个问题。这是当前设置的表(相关)。

+----+-----------+----------+------+------------+--------+--------+
| id | recipient | given_by | time | expiration | points | reason |
+----+-----------+----------+------+------------+--------+--------+
|  1 |   72      |     1    | time |    time    |   2    |  test  |
|  3 |   15      |     4    | time |    time    |   5    |  test  |
+----+-----------+----------+------+------------+--------+--------+

+----+----------+
| id | username |
+----+----------+
| 1  |   admin  |
| 4  |   user4  |
...
| 15 |  user15  |
...
| 72 |  user72  |
+----+----------+

我能够通过使用此查询让收件人匹配姓名:

SELECT 
   usr.username, usr.id,  sl.recipient, sl.given_by, 
   sl.time, sl.experation, sl.points, sl.reason
FROM 
    DD_users AS usr
LEFT JOIN
    DD_schittlist AS sl
ON (sl.recipient = usr.id)       
GROUP BY
        usr.id
ORDER BY 
        sl.points DESC,
        usr.username

这会将收件人 72 与 user72 匹配，但我还想将 given by 1 显示为 admin，将 given_by 4 显示为 user4。

Answer 1

试试这个:(您需要做的就是为 DD_users 表的第二个引用添加别名并添加第二个连接语句，然后在您的选择语句中引用别名的用户名列。)

  SELECT 
    usr.username, usr.id,  sl.recipient, sl.given_by, usr2.username, sl.time, sl.experation, sl.points, sl.reason
  FROM 
    DD_users AS usr
  LEFT JOIN DD_schittlist AS sl  ON (sl.recipient = usr.id)    
  JOIN DD_users as usr2 ON (usr2.id = sl.given_by)   
  GROUP BY
    usr.id
  ORDER BY 
    sl.points DESC,
    usr.username