类型一：按照某个组分类计算该组最高/最低的记录
- Department Highest Salary
184
解法一：
直接计算每个组的最高/最低记录
使用IN做匹配
select
d.Name AS 'Department',
e.Name AS 'Employee',
e.Salary
from
Employee e inner join Department d
on e.DepartmentId = d.Id
where
(e.Salary, e.DepartmentId) IN
(select
max(Salary), DepartmentId
From
Employee e2
group by DepartmentId )
解法二：
使用ALL()函数
需要将e和e2连接起来，限制于同一个部门进行比较
不需要group by
select
d.Name AS 'Department',
e.Name AS 'Employee',
e.Salary
from
Employee e inner join Department d
on e.DepartmentId = d.Id
where
e.Salary >= ALL(
select
Salary
from
Employee e2
where
e.DepartmentId = e2.DepartmentId
)
类型二：按照某个组分类计算该组最高/最低的前三名记录
解法一：
利用两个employee表连接， count(e2.Salary) < 3
select
de.Name AS 'Department',
e.Name AS 'Employee',
e.Salary
From
Employee e
inner join
Department de
on e.DepartmentId = de.Id
where
(select
count(distinct e2.Salary)
from
Employee e2
where
e.Salary < e2.Salary
and e.DepartmentId = e2.DepartmentId) <= 3 # 前3个,
;
解法二：
利用Dense_Rank建立一张有排序的表
再利用排序筛选
select Department, Employee, Salary from
(select
de.Name AS Department,
e.Name AS Employee,
e.Salary
DENSE_RANK() over(partition by de.Name order by e.Salary Desc) AS Rank
from
Employee e inner join Department de
on e.DepartmentId = de.Id) a
where Rank <= 3
SQLZOO
https://sqlzoo.net/wiki/Nested_SELECT_Quiz
- Select the code that shows the countries belonging to regions with all populations over 50000
