sql练习题

题目：第二高的薪水销售额

select salary , row_number() over( order by salary ) as ranking
from salary  
having ranking=2

select [销售额],row_number() over(order by [销售额] ) as ranking
from (select [销售额] from [deposit_order] group by [销售额] ) a  
having ranking=2

题目：删除重复的销售额

delete from deposit_order
where [user_id] not in (select [user_id] from (
select [user_id], [销售额],row_number() over(partition by [销售额] order by [user_id]) as  ranking from  deposit_order 
where ranking=1) 

delete from deposit_order
where [user_id] not in (select [user_id] from (
select min([user_id]) [user_id]  from  deposit_order 
group by [销售额]) 

题目：

with t1 as (
select
    id,
    visit_date,
    people,
    id-row_number() over(order by id) rk
from stadium
where people >= 100
)

select
    id,
    visit_date,
    people
from t1
#where条件过滤出条数大于3的
where rk in (
select rk from t1 group by rk having count(1) >= 3);

作者：bryce-28
链接：https://leetcode-cn.com/problems/human-traffic-of-stadium/solution/da-shu-ju-fang-xiang-xia-ci-ti-jie-ti-si-lu-by-bry/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

窗口排序函数
https://blog.csdn.net/u011726005/article/details/94592866?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522161113808516780269845979%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=161113808516780269845979&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_v2~rank_v29-1-94592866.pc_search_result_cache&utm_term=rank%E5%87%BD%E6%95%B0%E6%8E%92%E5%90%8D%E6%80%8E%E4%B9%88%E7%94%A8&spm=1018.2226.3001.4187

题目：换座位

在这里插入代码片