SQL重要题型总结

类型一：按照某个组分类计算该组最高/最低的记录

184. 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

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