sql - 如何从 Postgresql 子查询中的同一个表中获取用户名而不是 id？

Question

我有一个用户表:具有以下字段:名字，姓氏...，manager_id...，

manager_id 只是另一个用户的 id。

当我运行查询时:

   SELECT DISTINCT ON(users.id) user.id, CONCAT(users.first_name, ' ', users.last_name) AS \
                'employee', users.email, \
                (SELECT manager_id), \
                (SELECT users.first_name from users where users.id = manager_id) as Manager,\
                (SELECT DISTINCT to_char(auth_user.last_login, 'Mon dd, yyyy')) as Last_Login FROM users INNER JOIN auth_user ON (users.user_id = auth_user.id)

此查询 (SELECT manager_id) 返回经理的 ID，但是下一行

(SELECT users.first_name from users where users.id = manager_id) as
Manager

返回空白。我还尝试加入表本身，它只返回 1 位经理的名字:

(SELECT e.first_name FROM employees_employee e INNER JOIN
employees_employee s ON s.id = e.coach_id limit 1)

Answer 1

这是因为没有用户是他们自己的管理者。 始终限定您的列名!

SELECT DISTINCT ON (u.id) u.id,
       CONCAT(u.first_name, ' ', u.last_name) AS employee
       u.email,
       u.manager_id,
       (SELECT u2.first_name FROM users u2 where u2.id = u.manager_id) as Manager,
       to_char(au.last_login, 'Mon dd, yyyy') as Last_Login
FROM users u INNER JOIN
     auth_user au
     ON u.user_id = au.id
ORDER BY u.user_id;