2021-12-08 sql 的窗口函数和非等值自连接——多TM拗口的名字，确是如此优雅的实现

有如下表

SELECT
    *
FROM
    DistrictProducts;
    
 district | name | price 
----------+------+-------
 东北     | 橘子 |   100
 东北     | 苹果 |    50
 东北     | 葡萄 |    50
 东北     | 柠檬 |    30
 关东     | 柠檬 |   100
 关东     | 菠萝 |   100
 关东     | 苹果 |   100
 关东     | 葡萄 |    70
 关西     | 柠檬 |    70
 关西     | 西瓜 |    30
 关西     | 苹果 |    20
(11 rows)

求如何得出按地区区分，按价格排序表：

 district | name | max | rank1 
----------+------+-----+-------
 东北     | 橘子 | 100 |     1
 东北     | 苹果 |  50 |     2
 东北     | 葡萄 |  50 |     2
 东北     | 柠檬 |  30 |     4
 关东     | 苹果 | 100 |     1
 关东     | 柠檬 | 100 |     1
 关东     | 菠萝 | 100 |     1
 关东     | 葡萄 |  70 |     4
 关西     | 柠檬 |  70 |     1
 关西     | 西瓜 |  30 |     2
 关西     | 苹果 |  20 |     3
(11 rows)

最先想到的一定是窗口函数，如果没学过，请速去学，否则简洁和优雅将与你无缘。

SELECT
    district,
    name,
    price,
    RANK() OVER(
        PARTITION BY district
        ORDER BY
            price DESC
    )
FROM
    DistrictProducts;

如果不用窗口函数，如何实现？问题是现在这个节点，没窗口函数的sql db可以考虑更新了：

非等值自连接查询，请收下：

SELECT
    district,
    name,
    price,
    (
        SELECT
            count(price)
        FROM
            DistrictProducts AS dp
        WHERE
            dp.price > dp1.price
            AND dp.district = dp1.district
    ) + 1 AS rank1
FROM
    DistrictProducts AS dp1;

什么？不够复杂，不能体验智商。

非等值查询+聚合函数+外连接查询，请收下：

SELECT
    dp.district,
    dp.name,
    max(dp.price),
    count(dp2.name) + 1 AS rank1
FROM
    DistrictProducts AS dp
    LEFT OUTER JOIN DistrictProducts AS dp2 ON dp.price < dp2.price
    AND dp.district = dp2.district
GROUP BY
    dp.district,
    dp.name
ORDER BY
    dp.district,
    rank1;

所以，还不快去学窗口函数。