面试题：MongoDB和SQL复杂聚合查询的实现

Question

假设有一个电商数据库，在SQL中有products表存储商品信息（包含price、category等字段），在MongoDB中有products集合。要求分别使用SQL和MongoDB实现查询每个类别中价格最高的商品信息，并按类别名称排序。

Answer 1

SQL 实现

WITH RankedProducts AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
    FROM
        products
)
SELECT
    category,
    price,
    -- 假设还有其他字段，按实际添加
    other_field1,
    other_field2
FROM
    RankedProducts
WHERE
    price_rank = 1
ORDER BY
    category;

MongoDB 实现

db.products.aggregate([
    {
        $sort: {
            category: 1,
            price: -1
        }
    },
    {
        $group: {
            _id: "$category",
            highestPriceProduct: {
                $first: "$$ROOT"
            }
        }
    },
    {
        $project: {
            _id: 0,
            category: "$_id",
            price: "$highestPriceProduct.price",
            // 假设还有其他字段，按实际添加
            other_field1: "$highestPriceProduct.other_field1",
            other_field2: "$highestPriceProduct.other_field2"
        }
    },
    {
        $sort: {
            category: 1
        }
    }
]);