【SQL】一些常见的使用场景

SQL 专栏收录该内容
7 篇文章 1 订阅

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 ...from t1)

select ... from t2 ...

3 Case When

Task:把下面一张table转换为下面一个tree结构的table

idp_id
1null
21
31
42
52
idType
1Root
2Inner
3Leaf
4Leaf
5Leaf

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

4 找出连续n个相同的数

4.1 第一种情况:找出连续n个相同的数,n is given

  • 思路:lag(column,n) over ()

例题:
Write a SQL query to find all numbers that appear at least three times consecutively.

IdNum
11
21
31
42
51
62
72

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

ConsecutiveNums
1

解法:

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;

4.2 第二种情况:找出至少连续n个相同的数,n is given

  • 思路: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?

seat_idfree
11
20
31
41
51

Your query should return the following result for the sample case above.

seat_id
3
4
5

解法:

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 
    );

4.3 第三种情况:找出至少连续n个相同的数,n未知,是一个variable

  • 思路:新生成一个index,然后与原index相减
    例题:同上

解法:

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

5 用 0 或 null 填充空值

用0填充空值

select ifnull(column,0) from T
  • 0
    点赞
  • 2
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页

打赏

Xionglingchu

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值