窗口函数专题

窗口函数之 “最大/最小” 问题，可和 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