如何用MySQL查询替换列值

Question

我正在使用MySQL工作台来获取我要查找的表。我快到了。以下是查询结果：

----------------------------------------------------------------------------
employee.manager_id | employee.id | employee.first_name | employee.last_name
----------------------------------------------------------------------------
null                |     1       | Petra               | Wallace
null                |     3       | Peter               | Willis
null                |     5       | Michael             | Best
1                   |     2       | David               | Lone
3                   |     4       | Barbara             | Grinder
5                   |     6       | Anthony             | Krone

现在，我想用以下内容替换列employee.manager_id的值：

• 当值为null时，将其保留为null或用字符串"none"替换
• 当该值有一个数字时，它将引用employee.id的数字。例如，employee.manager_id中的值1表示employee.id编号1，即Petra Wallace

我想在employee.manager_id列中显示employee.first_name和employee.last_name，而不是数字。有人知道怎么做吗？

Answer 1

左联接根据join标准检索管理器。如果没有管理器，那么所有的manager.*字段都是NULL。

SELECT 
COALESCE(manager.first_name,"none") as manager_first_name,
manager.last_name as manager_last_name,
employee.id,
employee.first_name,
employee.last_name
FROM employee
LEFT JOIN employee manager
ON employee.manager_id = manager.id 
ORDER BY employee.manager_id

Answer 2

试试这个：

SELECT 
CASE WHEN employee.manager_id IS NULL THEN "NONE"
ELSE (SELECT CONCAT(e.first_name, ' ', e.last_name), e.id FROM employee e 
WHERE e.id = employee.manager_id)
END AS case_generated_column,
employee.id, employee.first_name, employee.last_name FROM employee ORDER BY employee.manager_id;