建表语句
create table grouptest(
city varchar(10),
town varchar(10),
persons number(10)
)
插入数据
insert into grouptest values ('江苏','苏州',10);
insert into grouptest values ('江苏','南京',20);
insert into grouptest values ('浙江','杭州',19);
insert into grouptest values ('浙江','义乌',13);
insert into grouptest values ('浙江','金华',11);
insert into grouptest values ('广东','广州',18);
1、对所有地级市的人数排序
select city,town,persons,row_number() over(order by persons desc) from grouptest
2、对各省地级市人数排序
select city,town,persons,row_number() over(partition by city order by persons desc) from grouptest
3、按照省名称排序,名称相同的,次序一样
select city,town,persons,rank() over(order by city desc) from grouptest
4、按照省名称排序,名称相同的,次序一样且连续
select city,town,persons,DENSE_RANK() over(order by city desc) from grouptest
5、按照省份降序分为三组
select city,town,persons,ntile(3) over(order by city desc) from grouptest