题目:第二高的薪水销售额
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-2allfirst_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
题目:换座位
在这里插入代码片