题解 | 商品价格排名

SELECT
    product_id,
    product_name,
    type,
    price
FROM
    (
        SELECT
            product_id,
            product_name,
            type,
            price,
            DENSE_RANK() OVER (
                PARTITION BY
                    type
                ORDER BY
                    price DESC
            ) AS rk
        FROM
            product_info
    ) AS a
WHERE
    rk <= 2
ORDER BY
    price DESC,
    product_name ASC
LIMIT
    3

此题ez，先通过ROW_NUMBER窗口函数进行分组排序选出每个类别中排名前二的，再通过ORDER BY排序后使用LIMIT限制输出前三位就好了