每组排名前N位(MSSQL)

I have 10k - 1m goods wich are discribed by fields product_id, name, category, price. Which is the fastest way to fetched 10 most expensive goods from each category? Previously I checked this answer https://stackoverflow.com/a/176985/9513268. My table:

-------------------------------------
|product_id| name | category| price |
-------------------------------------
|    1     |Phone | Gadgets | 599.99|
------------------------------------
|    2     |Jacket| Clothes | 399.00|
------------------------------------- 
|   ...    | ...  |   ...   | ...   |
------------------------------------- 
评论
MS女人
MS女人

您可以使用窗口功能,如链接的答案所示。

select *
from (
    select t.*, rank() over(partition by category order by price desc) rn
    from mytable t
) t
where rn <= 10
order by category, rn

The key is to properly define the over() clause of the window function. You want the top 10 by category, so this column goes to the partition by; you want the top most expensive goods, so the order by criteria is on descending price.

You can run the subquery separately and stare and the rn column to better understand the logic.

点赞
评论