1 查找第N高的薪水/成绩
知识点:
- limit n:表示查询结果返回前n条数据
- offest m:表示跳过m条语句
- limit n,m:表示查询结果跳过m条语句,读取前y条。也就是如果n为3 m为2,那么表示跳过2条语句读取前3条,也就是读取原来顺序的第3,4,5条数据
- ifnull(a,b):表示如果value1不是空,返回a,若value1是空,返回b
select ifNull(
(select distinct salary
from Employee
order by Salary Desc
limit 1,1),null
) as SecondHighestSalary
from Employee;
2 树节点
知识点:
- case when(条件1)then(结果1)
when(条件2)then(结果2)
else(结果3)
end as(列名)
select id ,
Case
When p_id is null Then "Root"
When id in (select distinct p_id from tree) Then "Inner"
Else "Leaf"
End as Type
from tree;
3 分数排名(连续排名)
知识点:
窗口函数的使用格式:
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> )
- RANK():排序时存在相同位次则跳过
- DENSE_RANK():排序时存在相同位次不跳过,连续排序号
- ROW_NUMBER():赋予唯一的连续序号
select score, dense_rank() over(order by score desc) as 'rank'
from Scores;
4 连续出现的数字
知识点:
- 窗口函数
- 列相减
- group by 与 having(having作用于组,从中选择出满足条件的组)
SELECT num
FROM(
SELECT *,t.id - t.rn as diff
from(
select id,num, row_number() over(partition by Num order by Id ASC) as rn
from Logs) as t)as t2
group by num,diff
having Count(*) >= 3;
5 求每个班在年级中的人数占比
知识点:
- 与子表的左连接left join
SELECT class, stu_num/人数 as 占比
from grade_class left join (SELECT grade,sum(stu_num) as 人数
from grade_class
group by grade)t
on grade_class.grade = t.grade;