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

xwmevbvl 于 2021-06-19 发布在 Mysql

关注(0)|答案(4)|浏览(276)

假设我有一张名为 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_name 的 user_id 从 user_created_by . 假设我们想得到“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           |
+----------+--------------+-------------------+------------------------+

mysql codeigniter

来源：https://stackoverflow.com/questions/52390773/select-a-specific-row-with-additional-column-that-its-value-is-from-another-colu

4条答案

按热度按时间

bqjvbblv1#

可以使用alias进行自连接（两次连接同一个表），将表作为不同的数据集

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

赞(0）回复(0）举报 2021-06-20

nnsrf1az2#

使用自联接

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

赞(0）回复(0）举报 2021-06-20

p1iqtdky3#

可以使用连接来解决此问题。

$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);

赞(0）回复(0）举报 2021-06-20

whitzsjs4#

这将起作用：

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

它被称为自联接，用于组合同一表的两条记录。

赞(0）回复(0）举报 2021-06-20

我来回答

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

4条答案

相关问题

热门标签

最新问答