SQL 分地区排序：使用窗口函数、自连接和关联子查询三种方法解题

题目来源于《SQL进阶教程》一书，觉得有点意思，特来Mark一下。
源表：

建表代码：

CREATE TABLE DistrictProducts
(district  VARCHAR(16) NOT NULL,
 name      VARCHAR(16) NOT NULL,
 price     INTEGER NOT NULL,
 PRIMARY KEY(district, name, price));

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);

显示结果：

三种方法如下：
第一种，是最简单的方法，窗口函数。
注意区分rank, dense_rank和row_number三种排序函数。

--方法1 窗口函数
select district,name,price,
       rank() over(partition by district order by price desc) as rank_1
from DistrictProducts;

第二种，自连接

--方法2 自连接
SELECT P1.district, P1.name,
       P1.price AS price, 
       COUNT(P2.name) +1 AS rank_1
FROM DistrictProducts P1 LEFT OUTER JOIN DistrictProducts P2
ON  P1.district = P2.district
AND P1.price < P2.price
GROUP BY P1.district, P1.name,P1.price;

第三种，关联子查询

--方法3 关联子查询
select d1.district,d1.name,d1.price, 
       (select count(d2.price) 
        from DistrictProducts d2 
        where d1.district = d2.district 
        and d1.price < d2.price)+1 as rank_1
from DistrictProducts d1;

有任何问题欢迎留言交流，互相学习，共同进步。