類型一：按照某個(gè)組分類計(jì)算該組最高/最低的記錄

184. Department Highest Salary

184

解法一：

? 直接計(jì)算每個(gè)組的最高/最低記錄

? 使用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()函數(shù)

? 需要將e和e2連接起來，限制于同一個(gè)部門進(jìn)行比較

? 不需要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
    )

類型二：按照某個(gè)組分類計(jì)算該組最高/最低的前三名記錄

解法一：

? 利用兩個(gè)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個(gè), 
;

解法二：

? 利用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

2. Select the code that shows the countries belonging to regions with all populations over 50000