满足条件时的自引用SQL查询

Question

我正在尝试创建一个SQL查询，以返回符合特定条件的表中的列值。

目前，我已经使用CONCAT函数将具有"程序员"角色的员工的名字和姓氏连接到查询结果中的一列中。

SELECT
    person.id, CONCAT(person.firstname,' ', person.lastname) AS FULLNAME 
FROM 
    person, role 
WHERE 
    person.role_id = role.id AND role.name = 'Programmer'

这成功地运行并返回了表中的所有程序员。请注意，在我的表结构中，我有一个actingas_id列。这是另一个人的钥匙。对于那些在休假期间代表另一个人工作的人来说。

因此，我们得出了我的问题：我如何修改SQL查询，以便当一个人执行时，该查询检索此人的名字和姓氏以及"正在填充鞋子"的人？

我的表格结构如下：

人员：id | firstname | lastname | role_id | actigas_id |

角色：id | name |

+----+-----------+----------+---------+-------------+
| id | firstname | lastname | role_id | actingas_id |
+----+-----------+----------+---------+-------------+
|  1 | John      | Smith    |       1 |           0 |
|  2 | Kevin     | Tull     |       2 |           1 |
|  3 | Michael   | Woods    |       1 |           0 |
+----+-----------+----------+---------+-------------+

Kevin是John的代理，Michael也是一名程序员，所以我的查询结果应该是：

+----+-------------------------+
| id |          NAME           |
+----+-------------------------+
|  1 | John Smith - Kevin Tull |
|  3 | Michael Woods           |
|  x | Other Programmers..     |
+----+-------------------------+

Answer 1

这个未经测试的查询应该会给你想要的结果：

SELECT person1.id, CASE WHEN person1.actingas_id =0 then CONCAT(person1.firstname,' ', person1.lastname) else CONCAT(person1.firstname,' ', person1.lastname,' - ',  person2.firstname,' ', person2.lastname)  AS FULLNAME 
FROM person person1 left join person person2 on person1.actingas_id=person2.id
join role on person1.role_id=role.id
WHERE role.name = 'Programmer'

Answer 2

使用UNION ALL添加这些附加记录：

SELECT person.id, 
       CONCAT(person.firstname,' ', person.lastname) AS FULLNAME 
FROM person INNER JOIN role 
  ON person.role_id = role.id 
WHERE role.name = 'Programmer' 
  AND NOT EXISTS(SELECT 1 FROM person p WHERE p.actingas_id = person.id) 
UNION ALL
SELECT a.id, 
       CONCAT(a.firstname,' ', a.lastname, ' - ', person.firstname,' ', person.lastname) AS FULLNAME 
FROM person INNER JOIN person a 
  ON a.acting_as = person.id
INNER JOIN role 
  ON person.role_id = role.id 
WHERE role.name = 'Programmer' AND a.actingas_id <> 0

此外，请避免使用旧式逗号分隔的JOIN。使用INNER JOIN