记录一些SQL题目

SQL 刷题笔记

    1. 给定每一个成绩等级的人数，求中位数落在哪个等级区间
select grade
from(
select grade,(select sum(number)as total from class_grade)as total,
sum(number)over(order by grade)as a,
sum(number)over(order by grade desc)as b
from class_grade)t
where a>=total/2 and b>=total/2
order by grade

做一次正向sum，做一次反向sum，中位数所在等级的sums都应该超过一半

    1. 注意Datediff里的日期要用引号括起来
    1. 寻找某日期首次登陆的用户名，注意要不要加distinct，有可能第一天登录了两次
    1. 窗口函数里的order by可以放多个条件，不过要记得用,隔开
    1. left join的时候要注意过滤条件是放在on还是放在where，放在on中会先过滤再连接，反之先连接再过滤可能会丢失掉想要的数据，比如查询用户购买数量，where会把没有买东西的过滤掉，而我们想要的是输出0
#leetcode 1158
select u.user_id as buyer_id,join_date,ifnull(count(order_id),0)orders_in_2019
from Users u left join Orders o on o.buyer_id=u.user_id and order_date like '2019%'
group by u.user_id
    1. SUBSTR的用法，SUBSTR(string,pos)取pos后面的所有，SUBSTR(string,pos,length)取pos后一定长度的string
    1. 自连接的妙用
    • leetcode 1204
select q1.person_name
from Queue q1 join Queue q2
on q1.turn>=q2.turn
group by q1.person_id
having sum(q2.weight)<=1000
order by q1.turn desc limit 1
    1. 求连续区间的方法，用窗口函数先排序，再将index减去rank值，得到同一个值的就是属于同一个连续区间的了。例子 leetcode 1285/1454
select min(log_id)as start_id,max(log_id)as end_id
from(
select log_id,rank()over(order by log_id)as rk
from Logs
)t
group by log_id-rk
order by start_id
    1. 调整窗口函数的汇总范围
<窗口函数> OVER (ORDER BY <排序用列名> ROWS n PRECEDING )
<窗口函数> OVER (ORDER BY <排序用列名> ROWS BETWEEN n PRECEDING AND n FOLLOWING)

PRECEDING取该行之前的行，FOLLOWING取改行之后的行，可以结合在一起使用。参考leetcode 1321

    1. 求中位数的方法
select distinct Id,Company,Salary from
(select Id,Company,Salary,rank()over(partition by Company order by Salary,Id)rk,
count(Salary)over(partition by Company)as total
from Employee)t
where rk in (total/2,total/2+1,total/2+0.5)

使用集合巧妙地把组内总数为奇数和偶数的情况都纳入考虑

    1. ANY 和 ALL的用法
select *
from(
select u.id,u.name,sum(if(type='add',grade_num,-1*grade_num))as grade_sum
from user u join grade_info g on u.id = g.user_id
group by u.name
)t
where t.grade_sum>=all(select sum(if(type='add',grade_num,-1*grade_num))from grade_info group by user_id)

当涉及要取最大值，最小值且可能存在多个的时候，可以用在判断里面用ALL或ANY看是否是某集合中的最大值或最小值。(当然也可以用窗口函数打标)
参考牛客SQL91

    1. 行转列/列转行
    • 行转列：把行根据属性字段，转为列，在这里使用case when的时候要注意加上聚合函数以保证取到我们要取到的值。
    • 列转行: 使用union进行查询结果的堆叠
    • 例子可以参考 (https://www.cnblogs.com/xiaoxi/p/7151433.html)
posted @ 2021-09-12 19:18  FeliXCVI  阅读(37)  评论(0)    收藏  举报