SQL 合併查詢語法 列出部屬的數量和平均年齡 Leetcode #1731

Campaign Creators on Unsplash

題目敘述

題目會給我們一張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:

[1] MySQL by INNER JOIN + GROUP BY [w/ SQL Diagram] - The Number of Employees Which Report to Each Employee - LeetCode