【SQL练习题】排序

原表如下：

建表脚本：

CREATE TABLE districtproducts (
  district varchar(255),
  name varchar(255),
  price int(255)
);

INSERT INTO districtproducts VALUES ('东北', '橘子', 100);
INSERT INTO districtproducts VALUES ('东北', '苹果', 50);
INSERT INTO districtproducts VALUES ('东北', '葡萄', 50);
INSERT INTO districtproducts VALUES ('东北', '柠檬', 30);
INSERT INTO districtproducts VALUES ('关东', '柠檬', 100);
INSERT INTO districtproducts VALUES ('关东', '菠萝', 100);
INSERT INTO districtproducts VALUES ('关东', '苹果', 100);
INSERT INTO districtproducts VALUES ('关东', '葡萄', 70);
INSERT INTO districtproducts VALUES ('关西', '柠檬', 70);
INSERT INTO districtproducts VALUES ('关西', '西瓜', 30);
INSERT INTO districtproducts VALUES ('关西', '苹果', 20);

效果如下：

每个district中的水果按照价格降序排序，rank_1跳过排名，rank_2不跳过排名

三种方式实现：

select p.district,p.name,p.price,
rank() over (partition by p.district order by p.price desc) as rank_1,
dense_rank() over (partition by p.district order by p.price desc) as rank_2
from districtproducts p;

select 
p1.district,p1.name,p1.price,
(select count(p2.price) from districtproducts p2 where p1.district = p2.district and p2.price > p1.price) + 1 as rank_1,
(select count(distinct p2.price) from districtproducts p2 where p1.district = p2.district and p2.price > p1.price) + 1 as rank_2
from districtproducts p1;

select 
p1.district,p1.name,max(p1.price) as price,count(p2.price)+1 as rank_1,count(distinct p2.price)+1 as rank_2
from districtproducts p1
left outer join districtproducts p2 on p1.district = p2.district and p1.price < p2.price 
group by p1.district,p1.name
order by p1.district,price desc;