分组排序

本文总结一下row_number() over(partition by a order by b desc) rn 用法


1、rownum：伪列，记录结果集的行号，使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序


2、row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）


3、rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．


4、dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．


5、lag（arg1,arg2,arg3): arg1表示表中的任意字段或者表单式 
arg2表示一个往回检索以前的行的数目，或者可以理解为某一行行号
arg3表示0到arg2行所显示的值


insert into test(id,province,city,code) values(1,'河北省','唐山市','100');
insert into test(id,province,city,code) values(2,'河北省','石家庄市','101');
insert into test(id,province,city,code) values(3,'河北省','秦皇岛市','102');
insert into test(id,province,city,code) values(4,'河北省','秦皇岛市','102');
insert into test(id,province,city,code) values(5,'河北省','保定市','103');
insert into test(id,province,city,code) values(6,'河北省','张家口市','104');




insert into test(id,province,city,code) values(7,'山东省','青岛市','200');
insert into test(id,province,city,code) values(8,'山东省','大连市','201');
insert into test(id,province,city,code) values(9,'山东省','济南市','202');






语句一、select rownum rownumber,t.* from test t order by t.code;


1 1 河北省 唐山市 100
2 2 河北省 石家庄市 101
3 3 河北省 秦皇岛市 102
4 4 河北省 秦皇岛市 102
5 5 河北省 保定市 103
6 6 河北省 张家口市 104
7 7 山东省 青岛市 200
8 8 山东省 大连市 201
9 9 山东省 济南市 202


语句二、select t.province,t.city,t.code,row_number() over(order by t.code) as rownumber from test t;


河北省 唐山市 100 1
河北省 石家庄市 101 2
河北省 秦皇岛市 102 3
河北省 秦皇岛市 102 4
河北省 保定市 103 5
河北省 张家口市 104 6
山东省 青岛市 200 7
山东省 大连市 201 8
山东省 济南市 202 9


两者达到的效果是一样的


语句三、（分组排序、唯一）select t.province,t.city,t.code,row_number() over(partition by t.province order by t.code) as rownumber from test t;


山东省 青岛市 200 1
山东省 大连市 201 2
山东省 济南市 202 3
河北省 唐山市 100 1
河北省 石家庄市 101 2
河北省 秦皇岛市 102 3
河北省 秦皇岛市 102 4
河北省 保定市 103 5
河北省 张家口市 104 6


语句四、（分组排序、重复、跳跃）select t.province,t.city,t.code,rank() over(partition by t.province order by t.code) as rownumber from test t;


山东省 青岛市 200 1
山东省 大连市 201 2
山东省 济南市 202 3
河北省 唐山市 100 1
河北省 石家庄市 101 2
河北省 秦皇岛市 102 3
河北省 秦皇岛市 102 3
河北省 保定市 103 5
河北省 张家口市 104 6


语句五、（分组排序、重复、连续）select t.province,t.city,t.code,dense_rank() over(partition by t.province order by t.code) as rownumber from test t;


山东省 青岛市 200 1
山东省 大连市 201 2
山东省 济南市 202 3
河北省 唐山市 100 1
河北省 石家庄市 101 2
河北省 秦皇岛市 102 3
河北省 秦皇岛市 102 3
河北省 保定市 103 4
河北省 张家口市 104 5


语句六、（分组排序、替换当前行号）select t.province,t.city,t.code,lag(t.city,2,'城市被替换了==========') over(partition by t.province order by t.code) as rownumber from test t;


山东省 青岛市 200 城市被替换了==========
山东省 大连市 201 城市被替换了==========
山东省 济南市 202 青岛市
河北省 唐山市 100 城市被替换了==========
河北省 石家庄市 101 城市被替换了==========
河北省 秦皇岛市 102 唐山市
河北省 秦皇岛市 102 石家庄市
河北省 保定市 103 秦皇岛市
河北省 张家口市 104 秦皇岛市


本文转载自：http://www.blogjava.net/kxbin/articles/360195.html