题解 | 商品价格排名

#type diff
#top 2
#huizong top3

with t1 as(
    select product_id,product_name,type,price,rank() over (partition by type order by price desc) as rank_info
from product_info pi),

t2 as(select t1.*
from t1
where t1.rank_info = 2 or t1.rank_info = 1),

t3 as(select *,rank() over (order by price desc) as rank_info2
from t2)

select  product_id,product_name,type,price
from t3
where rank_info2 <=3

注意区分

1.where会在窗口函数前执行，所以有窗口函数的地方少用where （老生常谈）

2.rownumber，rank，denserank区别