SQL如何查询指定时间范围内每个月按总金额排序的前3名商品

直接在你现有聚合逻辑的外层加窗口函数排序过滤即可，修改后完整SQL如下：

SELECT Yr, Mnth, Product, Sales
FROM (
    SELECT 
        Yr, Mnth, Product, 
        SUM(Price * Amount) AS Sales,
        -- 按年月分组，销售额降序排名
        ROW_NUMBER() OVER (PARTITION BY Yr, Mnth ORDER BY SUM(Price * Amount) DESC) AS sales_rank
    FROM
        (SELECT 
             Random_product_name AS Product, Random_price AS Price, 
             Random_quantity AS Amount,
             YEAR(Random_order_dt) AS Yr, MONTH(Random_order_dt) AS Mnth
         FROM 
             Random_table_1 a
         JOIN 
             Random_table_2 b ON a.Random_ID = b.Random_ID
         WHERE 
             a.Random_product_name LIKE 'XX_%' 
             AND a.Random_country = 'XX'
             AND a.Random_price != 0 
             AND b.Random_order_dt BETWEEN 'Jan 01 2019 12:00AM' AND 'Jul 01 2021 12:00AM') x
    GROUP BY Yr, Mnth, Product
) t
WHERE sales_rank <= 3 -- 仅保留每个月前3的商品
ORDER BY Yr, CONVERT(INT, Mnth), sales_rank

补充说明 #

• 如果需要支持销售额相同的商品并列进入前3，可以把ROW_NUMBER()替换为RANK()（排名跳过并列位）或DENSE_RANK()（排名不跳过并列位），按需选择即可
• 该写法兼容所有支持标准SQL窗口函数的数据库，包括SQL Server、MySQL 8.0+、PostgreSQL、Oracle等

内容的提问来源于stack exchange，提问作者Amberjack