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

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_created_byuser_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

你使用自连接(两次连接同一个表(，使用别名作为不同的数据集将 fere 添加到表中

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 

Answer 2

使用自加入

select u1.user_id, u1.name as user_name,
u2.user_created_by        
,u2.user_name as createdby  from users u1
join users u2 on u1.user_id=u2.user_created_by   
where u1.user_id=3    

Answer 3

您可以使用 JOIN 解决此问题。

$sql = "SELECT users.user_id, users.user_name,  user_created_by_name.user_name,
FROM users JOIN users AS user_created_by_name ON users.user_id = user_created_by_name.user_id WHERE users.user_id = 3";
$query=$this->db->query($sql);

如果您有不是由其他用户创建的用户，请改用左联接：

$sql = "SELECT users.user_id, users.user_name, user_created_by_name.user_name,
FROM users LEFT JOIN users AS user_created_by_name ON users.user_id = users.user_id WHERE user_created_by_name.user_id = 3";
$query=$this->db->query($sql);

Answer 4

这将起作用：

SELECT a.user_id as User_id, 
a.user_name as Name, 
b.user_id as Created_by_user_id, 
b.user_name as Created_by_name
FROM users AS a
INNER JOIN users AS b
ON a.user_id = b.user_created_by
WHERE a.user_id = 3

它称为自连接，在合并同一表的两个记录时使用。