SQL 排序后按组显示排名编号

Table1表结构如下

id  group1  group2  money

2232  gp1  aaa     234.3
2233  gp2  bbb     90.3
2234  gp1  aaa     804.39
2235  gp2  bbb     0
2236  gp2  bbb     88
2237  gp1  aaa     7

2238  gp1  aaa     234.3             

2239  gp2  bbb     88             

 

需要获得如下查询结果:

 id  group1  group2  money   num

2234  gp1  aaa     804.39      1
2232  gp1  aaa     234.3        2

2238  gp1  aaa     234.3        2
2237  gp1  aaa     7              3

2233  gp2  bbb     90.3         1

2236  gp2  bbb     88            2
2239  gp2  bbb     88            2

2235  gp2  bbb     0              3

 

其中Num是按group1 ，group2  排序后增加的编号列

SQL语句如下：

select id,group1,group2,money
,(select count(money) from Table1 where group1=a.group1 and group2=a.group2 and money>=a.money) as num
from Table a
order by group1,group2,money desc

 

另一种排序的

需要获得如下查询结果:

 id  group1  group2  money   num

2234  gp1  aaa     804.39      1
2232  gp1  aaa     234.3        2

2238  gp1  aaa     234.3        3
2237  gp1  aaa     7              4

2233  gp2  bbb     90.3         1

2236  gp2  bbb     88            2
2239  gp2  bbb     88            3

2235  gp2  bbb     0              4

 

SQL语句如下：

select id,group1,group2,money
,(select count(id) from Table1 where group1=a.group1 and group2=a.group2 and money>=a.money) as num
from Table a
order by group1,group2,money desc