mysql - 选择具有附加列的特定行，其值来自一个表中的另一列

Question

假设我有一个名为 users 的表，包含以下列:user_id、user_name、user_created_by。

+------------------+----------------------+-------------------+
|    user_id       +      user_name       +  user_created_by  +
+------------------+----------------------+-------------------+
|        1         |        John          |         1         |
|        2         |        Ann           |         1         |
|        3         |        Paul          |         2         |
|        4         |        King          |         2         |
|        5         |        Dirk          |         3         |
+------------------+----------------------+-------------------+

user_created_by 的值是创建该记录的 user_id。现在，我想做一个查询，结果是一个特定的行和添加的列让我们说 user_created_by_name 这是 user_id 的 user_name 来自 用户创建者。假设我们想要获取 “Paul” 的记录以及创建者(姓名)(临时新列)。为了便于理解，这是我的预期结果:

+----------+--------------+-------------------+------------------------+
| user_id  |   user_name  |  user_created_by  |  user_created_by_name  |
+----------+--------------+-------------------+------------------------+
|    3     |     Paul     |        2          |          Ann           |
+----------+--------------+-------------------+------------------------+

这是我使用 codeigniter 的查询:

$query=$this->db->query("SELECT *, 
                           (SELECT user_name FROM users WHERE user_id = user_created_by) 
                              AS "user_created_by_name" FROM users WHERE user_id=3);

但我的结果是:

+----------+--------------+-------------------+------------------------+
| user_id  |   user_name  |  user_created_by  |  user_created_by_name  |
+----------+--------------+-------------------+------------------------+
|    3     |     Paul     |        2          |         NULL           |
+----------+--------------+-------------------+------------------------+

Answer 1

您可以使用自连接(连接同一个表两次)使用别名将表作为不同的数据集

SELECT a.user_id, a.user_name, a.user_created_by, b.user_name as user_created_by_name
from users a 
inner join user b on a.user_created_by = b.user_id 
where a.user_id  = 3