- 思路：limit n offset m
Select distinct score from Student order by score desc Limit 1 offset 2;
用法解释：limit 1 表示选择一行，offset 2表示skip两行
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()一样方便地使用了
- 思路： with table as
with t1 as (select ...), t2 as (select ...from t1) select ... from t2 ...
Select id, Case when t.id in (select id from tree where p_id is null) Then 'Root' when t.id in (select distinct p_id from tree) Then 'Inner' else 'Leaf' End as Type from tree t
- 思路：lag(column,n) over ()
Write a SQL query to find all numbers that appear at least three times consecutively.
For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
select distinct t1.Num as ConsecutiveNums from (select Num, lag(Num,1) over() as num_l1, lag(Num,2) over() as num_l2 from Logs) t1 where t1.Num = t1.num_l1 and t1.Num = t1.num_l2;
- 思路：lag(column,n) over ()，先找出不相邻的数，然后找补集
- 边界：lag和lead导致的首尾行的null值需要用 ifnull()函数来处理
Several friends at a cinema ticket office would like to reserve consecutive available seats.
Can you help to query all the consecutive available seats order by the seat_id using the following cinema table?
Your query should return the following result for the sample case above.
select cinema.seat_id from cinema where cinema.free = 1 and cinema.seat_id not in ( select t1.seat_id from (select *, ifnull(lag(free,1) over(),0) as free_lag, ifnull(lead(free,1) over(),0) as free_lead from cinema )t1 where t1.free <> t1.free_lag and t1.free <> t1.free_lead );
with table1 as ( select seat_id, rank() over(order by seat_id asc) as rk from cinema where free = 1 ), table2 as ( select seat_id, seat_id - rk as rnk from table1 ), table3 as ( select seat_id, count(*) over(partition by rnk) as cnt from table2 ) select seat_id from table3 where cnt >= 2
select ifnull(column,0) from T