查询数据排名情况SQL

1/准备测试数据

---------------------------------------------------------------------------------
createtablet1(
c1integer,
c2integer,
c3integer
);

insertintot1values(1,2,3)

insertintot1values(1,8,4)
insertintot1values(1,4,4)

insertintot1values(1,4,5)

insertintot1values(1,5,5)

insertintot1values(2,2,3)

insertintot1values(2,8,4)
insertintot1values(2,4,4)

insertintot1values(2,4,5)

insertintot1values(2,5,5)

2/查看排名

---------------------------------------------------------------------------------

A/单记录排名

selectc1,c3,
(selectcount(c3)+1fromt1awherea.c3>b.c3
anda.c1=b.c1anda.c1=1
)order_num
fromt1b
where c1=1
orderbyc1,c3

c1         c3         order_num             
--------------------------------------------
1          3          5                     
1          4          3                     
1          4          3                     
1          5          1                     
1          5          1     
B/多记录排名

selectc1,c2,c3,
(selectcount(c3)+1fromt1awherea.c3>b.c3
anda.c1=b.c1
)order_num
fromt1b
orderbyc1,c3

c1         c2         c3         order_num             
-------------------------------------------------------
1          2          3          5                     
1          8          4          3                     
1          4          4          3                     
1          4          5          1                     
1          5          5          1                     
2          2          3          5                     
2          8          4          3                     
2          4          4          3                     
2          4          5          1                     
2          5          5          1