题解 | 查询出不同类别商品中，销售金额排名前三且利润率超过 20%的商品信息

容易出问题的地方是，利润率的筛选条件是和销售额排名放在一起的。

如果子查询先进行利润率筛选，再进行排名，就会把利润达标但是销售额靠后的算进来

题目意思应该是先选前三名，同时看利润达不达标，不达标的话就不要了。

select

product_id,

product_name,

category_id,

sales_amount,

profit_rate

from

(select

p.product_id,

product_name,

category_id,

sales_amount,

round((sales_amount-cost_amount)/sales_amount,2) as profit_rate,

row_number() over(partition by category_id order by sales_amount desc,p.product_id) as rn

from product_category p

inner join sales_and_profit s on p.product_id=s.product_id) w1

where rn<=3 and profit_rate>=0.2

order by category_id,sales_amount desc,product_id;

select

product_id,

product_name,

category_id,

sales_amount,

profit_rate

from

(select

p.product_id,

product_name,

category_id,

sales_amount,

round((sales_amount-cost_amount)/sales_amount,2) as profit_rate,

row_number() over(partition by category_id order by sales_amount desc,p.product_id) as rn

from product_category p

inner join sales_and_profit s on p.product_id=s.product_id

where (sales_amount-cost_amount)/sales_amount>=0.2) w1

where rn<=3

order by category_id,sales_amount desc,product_id;