題目敘述
題目會給我們一張Employees 資料表。裡面分別有employee_id、name、reports_to 、age 等欄位。其中employee_id 是這張資料表的主鍵Ｐrimary key。
輸出答案時以 employee_id 員工ＩＤ作升序排列。
Table: Employees
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| employee_id | int |
| name | varchar |
| reports_to | int |
| age | int |
+-------------+----------+
employee_id is the column with unique values for this table.
This table contains information about the employees and the id of the manager they report to. Some employees do not report to anyone (reports_to is null).
約束條件
要求我們列出每一位經理人下轄部屬的數量和部屬的平均年齡（四捨五入到最接近的整數）。
輸出答案時以 employee_id 員工ＩＤ作升序排列。
演算法- 合併查詢 JOIN
當需要使用到兩張資料表，或者一張資料表使用兩次時，想到 JOIN 合併查詢。
本題的情況是一張資料表使用兩次，並且用經理的ID 和 員工向上報告對象的ID去做JOIN 合併查詢，建立出經理對部屬的關係。
這題主要的考點落在合併查詢JOIN，可以使用經典的SQL 合併查詢語法去解開
SELECT ...欄位
FROM ...表格1
JOIN ...表格2 (可能是另一張表格，也可以是同一張表格)
ON ...合併對齊的欄位
GROUP BY ...分群的依據欄位
ORDER BY ...輸出排序的依據
主角是 經理人 和 下轄的部屬，經理人 和 部屬 都是員工表Employees裡面的成員，這題使用INNER JOIN 同一張表使用兩次，做合併查詢。
經理人 對 部屬的關係可以用 經理的員工ID 和 員工的向上回報的ID 串聯起來。
image.png
SELECT mgr.employee_id, mgr.name, COUNT(emp.employee_id) AS "reports_count", ROUND(AVG(emp.age), 0) as "average_age"
FROM Employees mgr
# Connect two tables on "report_to" field.
INNER JOIN Employees emp
ON mgr.employee_id = emp.reports_to
接著 在對經理人做分群 和 升序排列即可。
GROUP BY mgr.employee_id
# Sort by ID in ascending order
ORDER BY mgr.employee_id ASC;
程式碼 合併查詢 JOIN
# Round employees' age to nearest integer
SELECT mgr.employee_id, mgr.name, COUNT(emp.employee_id) AS "reports_count", ROUND(AVG(emp.age), 0) as "average_age"
FROM Employees mgr
# Connect two tables on "report_to" field.
INNER JOIN Employees emp
ON mgr.employee_id = emp.reports_to
GROUP BY mgr.employee_id
# Sort by ID in ascending order
ORDER BY mgr.employee_id ASC;
關鍵知識點
當需要使用到兩張資料表，或者一張資料表使用兩次時，想到 JOIN 合併查詢。
本題的情況是一張資料表使用兩次，並且用經理的ID和員工向上報告對象的ID去做JOIN 合併查詢，建立出經理對部屬的關係。
Reference:
