上图中reportsTo 列的数据是manager id,找出manager的查询方法如下:
SELECT CONCAT(m.lastname,', ',m.firstname) AS 'Manager',
CONCAT(e.lastname,', ',e.firstname) AS 'Direct report'
FROM employees e
INNER JOIN employees m ON m.employeeNumber = e.reportsto
ORDER BY manager
可是以上查询没有 top manager ,当reportsto为NULL时,他就是top manager。
等价转化为 当e.reportsto =NULL,m.employeeNumber=NULL,e就是top manager。
SELECT IFNULL(CONCAT(m.lastname,', ',m.firstname),'Top Manager')AS 'Manager',
CONCAT(e.lastname,', ',e.firstname)AS 'Direct report'
FROM employees e
LEFT JOIN employees m ON m.employeeNumber=e.reportsto
ORDER BY manager DESC
此时用left join 是因为employees m可能为空,这是被左连接所允许的。
转载于:https://blog.51cto.com/6793248/1325437