LeetCode(184):部門給与が最も高い従業員Reformat Department Table(SQL)
2292 ワード
2020.7.8 LeetCode 0ブラシ個人ノートから整理(更新継続)
github:https://github.com/ChopinXBP/LeetCode-Babel
転送ゲート:部門給与が最も高い従業員
Employeeテーブルには、対応するId、salary、department Idを持つすべての従業員情報が含まれています.
Departmentテーブルには、会社のすべての部門の情報が含まれています.
SQLクエリーを作成し、各部門で最も給料の高い従業員を見つけます.例えば、上記の表によれば、MaxはIT部門で最高賃金、HenryはSales部門で最高賃金を有する.
#Coding 1時間、Copying 1秒.いいねを残してくれてありがとう
github:https://github.com/ChopinXBP/LeetCode-Babel
転送ゲート:部門給与が最も高い従業員
Employeeテーブルには、対応するId、salary、department Idを持つすべての従業員情報が含まれています.
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Departmentテーブルには、会社のすべての部門の情報が含まれています.
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
SQLクエリーを作成し、各部門で最も給料の高い従業員を見つけます.例えば、上記の表によれば、MaxはIT部門で最高賃金、HenryはSales部門で最高賃金を有する.
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
# :1. ;2.
# NULL
SELECT d.Name AS 'Department', t.Name AS 'Employee', t.Salary AS 'Salary'
FROM (
SELECT e.Name, e.Salary, e.DepartmentId
FROM (SELECT DepartmentId, MAX(Salary) AS 'maxSalary'
FROM Employee
GROUP BY DepartmentId) m, Employee e
WHERE e.Salary = m.maxSalary AND e.DepartmentId = m.DepartmentId
) t
LEFT JOIN Department d
ON t.DepartmentId = d.Id
WHERE d.Name IS NOT NULL;
# NULL
SELECT d.Name AS 'Department', t.Name AS 'Employee', t.Salary AS 'Salary'
FROM (
SELECT e.Name, e.Salary, e.DepartmentId
FROM (SELECT DepartmentId, MAX(Salary) AS 'maxSalary'
FROM Employee
GROUP BY DepartmentId) m, Employee e
WHERE e.Salary = m.maxSalary AND e.DepartmentId = m.DepartmentId
) t
INNER JOIN Department d
ON t.DepartmentId = d.Id;
#IN
SELECT d.Name AS 'Department', e.Name AS 'Employee', e.Salary AS 'Salary'
FROM Employee e
INNER JOIN Department d
ON e.DepartmentId = d.Id
WHERE (e.DepartmentId, e.Salary) IN (
SELECT DepartmentId, MAX(Salary)
FROM Employee
GROUP BY DepartmentId
);
#Coding 1時間、Copying 1秒.いいねを残してくれてありがとう