题解 | 商品价格排名

#1.先求出每个商品类型的价格的前2名的商品。
#2.在将上述的商品在进行个排序，取前三。
with tiaojian as (
select
product_id,
product_name,
type,
price,
dense_rank()over(partition by type order by price desc) as pf
from product_info
)

select 
t.product_id,
t.product_name,
t.type,
t.price
from(
select 
product_id,
product_name,
type,
price,
row_number()over(order by price desc,product_name asc) as pdiff
from tiaojian
where
pf<=2
) as t 
where
t.pdiff<=3