现有一个表
a b c
A a1 100
C a1 70
Z a1 150
J a2 100
G a2 200
F a2 170
N a2 140a列是名称,b列是分配名称属于哪个组,c是d数量,现在希望生成一列 d按照每个小组的数量显示排名
a b c d
A a1 100 2
C a1 70 3
Z a1 150 1
J a2 100 4
G a2 200 1
F a2 170 2
N a2 140 3
a b c
A a1 100
C a1 70
Z a1 150
J a2 100
G a2 200
F a2 170
N a2 140a列是名称,b列是分配名称属于哪个组,c是d数量,现在希望生成一列 d按照每个小组的数量显示排名
a b c d
A a1 100 2
C a1 70 3
Z a1 150 1
J a2 100 4
G a2 200 1
F a2 170 2
N a2 140 3
rank() over (partition by b order by c desc)
dense() () over (partition by b order by c desc)
from table
顺序排名,没有并列
rank() over (partition by b order by c desc)
有并列,并列的跳过下一排名
dense() () over (partition by b order by c desc)
有并列,不跳过