• 欢迎光临~

窗口函数专题

开发技术 开发技术 2022-10-01 次浏览

窗口函数之 “最大/最小” 问题,可和 group by 互相改写。

力扣1070:

select distinct product_id,year as first_year,quantity,price 
from 
    (select *,rank() over (partition by product_id order by year) as rnk
    from Sales) tmp
where rnk = 1

select distinct product_id,year first_year,quantity,price
from sales
where (product_id,year) in
    (select product_id,min(year)
    from sales 
    group by product_id)

力扣1112:

select distinct student_id,course_id,grade
from
    (select *,rank() over(partition by student_id order by grade desc,course_id) as rnk
    from Enrollments) as tmp
where rnk = 1

# 如删去条件对 course_id 取小
select distinct student_id,course_id,grade 
from Enrollments
where (student_id,grade) in
    (select distinct student_id,max(grade)
    from Enrollments
    group by student_id)
group by student_id
order by student_id

-END

程序员灯塔
转载请注明原文链接:窗口函数专题
喜欢 (0)