排查查找当前年18-21点按销售额和数量排名的第二畅销产品SQL错误

表结构与需求 #

现有表结构 #

orders表

item_line表

需求 #

查找当前年份内，在18:00-21:00售出的、按销售额降序、销量降序排名的第二畅销产品。

你的SQL语句问题点 #

SELECT * FROM (
    SELECT i.product_name,
            SUM(o.total_amount)sales,
            SUM(i.quantity)total_qty,
            ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC,SUM(i.quantity)total_qty DESC) AS rn
    FROM item_line i
    WHERE o.created_at BETWEEN 18:00:00 AND 21:00:00
    JOIN orders o on o.order_id = i.order_id
    GROUP BY i.product_name ) temp
WHERE rn = 2;

1. JOIN与WHERE顺序错误 #

SQL执行顺序中，FROM后先处理JOIN关联表，再执行WHERE过滤。你在JOIN之前就引用了未关联的o.created_at，数据库无法识别表别名o，会直接报错。

2. 时间过滤逻辑错误 #

• 未限制当前年份：完全没匹配需求里的「当前年份内」的条件。
• 时间格式与提取错误：created_at是datetime类型，直接用18:00:00无引号格式比较会触发语法错误，需要用TIME()函数提取时间部分，同时给时间字符串加单引号，比如'18:00:00'。

3. 销售额计算错误 #

用SUM(o.total_amount)计算产品销售额是错的：total_amount是整个订单的总金额，一个订单可能包含多个产品，求和后会把订单总金额重复计算到每个关联产品上。正确的产品销售额应该是SUM(i.quantity * i.unit_price)，即单个产品的销量×单价的总和。

4. 窗口函数排序语法错误 #

在ROW_NUMBER()的排序规则里，你写了SUM(i.quantity)total_qty DESC，标准SQL中窗口函数无法引用同层级SELECT的别名，应该直接写SUM(i.quantity) DESC。

修正后的SQL语句 #

SELECT * FROM (
    SELECT 
        i.product_id,
        i.product_name,
        SUM(i.quantity * i.unit_price) AS sales,
        SUM(i.quantity) AS total_qty,
        ROW_NUMBER() OVER (
            ORDER BY SUM(i.quantity * i.unit_price) DESC, 
                     SUM(i.quantity) DESC
        ) AS rn
    FROM item_line i
    JOIN orders o ON o.order_id = i.order_id
    WHERE 
        YEAR(o.created_at) = YEAR(CURRENT_DATE())
        AND TIME(o.created_at) BETWEEN '18:00:00' AND '21:00:00'
    GROUP BY i.product_id, i.product_name
) temp
WHERE rn = 2;

修正说明 #

• 调整JOIN与WHERE顺序，先关联表再过滤条件。
• 添加YEAR(o.created_at) = YEAR(CURRENT_DATE())限制当前年份。
• 用TIME(o.created_at)提取时间部分，配合单引号包裹的时间字符串完成时段过滤。
• 改用SUM(i.quantity * i.unit_price)计算单个产品的真实销售额。
• 窗口函数排序直接使用聚合函数，避免引用别名；同时把product_id加入GROUP BY，避免同名称不同产品的分组错误。

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