1 选出第n名
1.1 第一种情况:n is given,比如找出第3名
- 思路:limit n offset m
Select distinct score from Student
order by score desc
Limit 1 offset 2;
用法解释:limit 1 表示选择一行,offset 2表示skip两行
1.2 第二种情况:n is a variable,让找出第n名,此时需要去定义n这个变量
Create Function GetNthHighest(N int)
Returns Int
Begin
Declare M int;
Set M = N - 1;
Return
(
Select distinct score from Student
order by score desc
Limit 1 offset M
);
End
# 之后便可以像sum(),avg()一样方便地使用了
2 保存一张新的table,供以后反复调用
- 思路: with table as
with t1 as (select ...),
t2 as (select .