项目需求:分组排序
1、要求取出按field1分组后,并在每组中按照field2排序;
2、根据要求取出1中已经分组排序好的前多少行的数据;
1.row_number() over()
row_number()over(partition by col1 order by col2)表示根据col1分组,再分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 (1,2,3,4,5.....)
2.rank() over()
rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)(1,2,2,4,5...)
3.dense_rank() over()
dense_rank()也是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。(1,2,2,3,4,5....)
示例SQL代码:
CREATEDATE ACCNO MONEY
2014/6/5 111 200
2014/6/4 111 600
2014/6/5 111 400
2014/6/6 111 300
2014/6/6 222 200
2014/6/5 222 800
2014/6/6 222 500
2014/6/7 222 100
2014/6/6 333 800
2014/6/7 333 500
2014/6/8 333 200
2014/6/9 333 0
1、select t.*,row_number() over(partition by accno order by createDate) row_number from Test t
查询结果如下:
(即使某排序的字段的值相同,各组的row_number唯一)
![](https://i-blog.csdnimg.cn/blog_migrate/5925dba1f34b1ec0f462a97ef1442dee.png)
2、 select t.*,rank() over(partition by accno order by createDate) rank from Test t
查询结果
(根据 createDate排序,在1、2组中,日期都相同,因此row_number一致。接下来的是4)
![](https://i-blog.csdnimg.cn/blog_migrate/f232b0c97a87a2aa49ddd85ab5dc01b7.png)
3、select t.*,dense_rank() over(partition by accno order by createDate) dense_rank from Test t
查询结果:
(重复的row_number之后依然是row_number+1)
![](https://i-blog.csdnimg.cn/blog_migrate/572694b0ba272d3c25d4757070f024d6.png)
第二个需求: 分组排序后分别取出各组内前多少的数据记录,如班级里有多个小组,求每组的前三名。
select createDate,accno,money,row_number from (select t.*,row_number() over(partition by accno order by createDate) row_number from Test t) t1 where row_number<4
查询结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/2ea4490f33b9e94b437930d780d6f9fe.png)
如果是按照多个字段分组,那么也是一样的。多条件分组,多条件排序。
row_number()over(partition by a,b,c order by a,b,c)
本文借鉴于:https://www.cnblogs.com/java-class/p/4726175.html