select
t.prod_id,
t.profit_total,
rank() over(partition by t.prod_id order by t.profit_total) rank
from rep_prod_index t
where 1=1
按照prod_id分组,并且在分组内按照profit_total升序排列,rank是分组内排序的序号(如果profit_total相同,则是并列排名)。
查询结果:
prod_id | profit_total | rank |
HBCP004 | 234 | 1 |
HBCP004 | 234 | 1 |
HBCP004 | 234 | 1 |
HBCP004 | 234 | 1 |
HBCP004 | 5479.45 | 5 |
HBCP004 | 8219.17 | 6 |
HBCP004 | 10958.89 | 7 |
HBCP004 | 13327.31 | 8 |
HBCP004 | 15695.73 | 9 |
HBCP004 | 18064.15 | 10 |
HBCP004 | 20432.57 | 11 |
HBCP004 | 22801 | 12 |
HBCP004 | 25279.01 | 13 |
HBCP004 | 27702.23 | 14 |
HBCP004 | 29296.62 | 15 |
HBCP004 | 29879.16 | 16 |
HBCP005 | 5479.45 | 1 |
HBCP005 | 5479.45 | 1 |
HBCP005 | 5479.45 | 1 |
HBCP005 | 5479.45 | 1 |
HBCP005 | 5479.45 | 1 |
HBCP005 | 5479.45 | 1 |
HBCP005 | 8219.17 | 7 |
HBCP005 | 8219.17 | 7 |
HBCP005 | 8219.17 | 7 |
HBCP005 | 8219.17 | 7 |
HBCP005 | 8219.17 | 7 |
HBCP005 | 8219.17 | 7 |