简单复习了一下 SQL,记录一下
速查表
例子
示例表:
/* Employee */
+----+-------+--------+--------------+
| 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 |
+----+----------+
/* 最直接的思路就是一层一层的嵌套子查询了,但是可读性一般,待更新... */
SELECT
DName AS Department,
Name AS Employee,
Salary
FROM (
SELECT
Name, DName, Salary,
RANK() OVER (PARTITION BY DName ORDER BY Salary DESC) as Ranking
FROM (
SELECT * FROM Employee
INNER JOIN
(SELECT Name AS Dname, Id AS DId FROM Department)
AS d
ON d.DId = Employee.departmentId
) as joined
)
AS ranked
WHERE ranked.Ranking = 1;
/* result */
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+