1. 第二高的薪水：
解答一：最高的薪水，再取小于最高薪水中最高的就是第二高
select max(Salary) SecondHighestSalary from Employee
where Salary <
(select max(Salary) from Employee)
解答二：直接distinct 排序后limit 取第二高
select ( select DISTINCT Salary from Employee ORDER BY Salary DESC limit 1 offset 1 )
as SecondHighestSalary
注：limit 1 offset 1 从第一条数据读取（offset 1 ，但不包括第一条），往后读取一条数据（limit 1
），即第二条；
2. 分数排名
解答一： 窗口函数方法：-- dense_rank()：不占用下一名次的排序函数
SELECT *,dense_rank() over (order by Score desc) as R from score
-- 其他做法1（自连接）：
select s1.score,count(distinct(s2.score)) R from score s1
join score s2
on s1.score<=s2.score
group by s1.Id
order by R
-- 其他做法2：
select Score, (select count(distinct Score) from score where Score>=s.Score) R
from score s
order by R
3. 连续出现的数字
解答一：窗口函数 row_number()
select Num from
( select num,(id - row_number() over(partition by num order by id)) rank_ from Logs) tmp
group by rank_,num
having count(1)>=3
解答二（自连接）：
select distinct l1.num ConsecutiveNums from logs l1
join logs l2 on l1.id=l2.id-1
join logs l3 on l1.id=l3.id-2
where l1.num=l2.num=l3.num
解答三（自连接）：
-- 根据num列相等，以及id之差的范围在0到2之间，对logs表进行自连接
-- 根据l1.id进行聚合分组
-- 统计分组中l2.id的数目，若大于等于3即满足题意
select distinct l1.num consecutivenums
from logs l1 join logs l2
on l1.num = l2.num and l1.id - l2.id between 0 and 2
group by l1.id
having count(l2.id) >= 3
4. 部门前三高的员工
解答：select d.Name Department,e.Name Employee,Salary from employee e
join Department d on e.DepartmentId = d.Id
where e.Id in
-- 以下每部门员工薪资符合前三高的（类似第二题的做法1）
(select e1.Id from employee e1
join employee e2 on e1.DepartmentId = e2.DepartmentId and e1.Salary <= e2.Salary
group by e1.Id having count(distinct e2.Salary)<=3)
order by Department, Salary DESC
5. 最小的id（略改）
解答一：
select * from person p
where p.id not in
(select p1.id from person p1
join person p2 on p1.email = p2.email and p1.id > p2.id)
解答二：
select min(id),p.email from person p
group by p.email
6.超过5名学生的课
"student", "class"
["A", "Math"]
["B", "English"]
["C", "Math"]
["D", "Biology"]
["E", "Math"]
["F", "Math"]
["A", "Math"]
应输出：
class
Math
解答一：
select class from courses
group by class
having count(distinct student) >= 5 -- distinct用于having count，避免重复元素计数
解答二：
select class from
(select distinct * from courses) c -- 先把表去重再分组聚合
group by c.class
having count(1) >= 5
