SQL实现指定单据销售额计算：重复商品取第二最低价格

现有两张数据表：

TABLE checks #

TABLE articles #

需求：计算单据1check的销售总额，规则为：

• 若同一商品在articles表中有多条价格记录，取第二低的价格计算
• 若仅一条价格记录，直接使用该价格
• 最终计算所有商品的总数量 × 对应价格的总和

用户尝试的SQL代码：

SELECT
    a.check,
    Sump
FROM
    (
        SELECT
            price2,
            Case
                WHEN COUNT( a.art ) > 1 THEN
                    SUM( a.quantity * a.price ) 
                ELSE
                    SUM( a.quantity * price2 )
            END AS sump,
            a.art,
            a.check
        FROM
            checks AS a
            INNER JOIN
            (
                SELECT
                    art,
                    price,
                    LEAD( price, 1 ) OVER (
                        PARTITION BY art
                        ORDER BY price ASC
                    ) AS price2
            FROM
                prices
            ) AS b on a.art = b.art
        WHERE
            a.quantity > 0
        GROUP BY
            a.checks,
            a.art,
            price2
    )
WHERE
    a.checks = '1check' 

这段SQL存在几个明显问题：

1. 表名错误：子查询中引用了不存在的prices表，实际应为articles
2. 字段名混淆：checks表的字段是checks（单据），但代码中多次写成a.check，字段名不匹配
3. 逻辑错误：
   • 使用LEAD获取下一行价格，但会导致同一商品的多条价格记录与checks表关联后产生重复数据，计算结果失真
   • CASE语句的判断逻辑错误，无法正确区分商品价格记录的数量并选取对应价格
4. 分组错误：分组字段包含price2，会导致同一商品因不同的price2值被拆分计算，不符合需求

以下是修正后的SQL，通过分步处理实现需求：

WITH article_prices AS (
    -- 给每个商品的价格按升序编号
    SELECT 
        art,
        price,
        ROW_NUMBER() OVER (PARTITION BY art ORDER BY price ASC) AS price_rank
    FROM articles
),
target_prices AS (
    -- 确定每个商品用于计算的目标价格：多条记录取第二低，单条取唯一值
    SELECT 
        art,
        CASE 
            WHEN COUNT(*) >= 2 THEN MAX(CASE WHEN price_rank = 2 THEN price END)
            ELSE MAX(price)
        END AS calc_price
    FROM article_prices
    GROUP BY art
),
checks_summary AS (
    -- 汇总1check中每个商品的总数量
    SELECT 
        art,
        SUM(quantity) AS total_quantity
    FROM checks
    WHERE checks = '1check'
    GROUP BY art
)
-- 计算最终销售总额
SELECT 
    '1check' AS checks,
    SUM(total_quantity * calc_price) AS total_sales_amount
FROM checks_summary
JOIN target_prices ON checks_summary.art = target_prices.art;

代码说明 #

1. article_prices：使用ROW_NUMBER()给每个商品的价格按升序分配排名，排名1是最低价格，排名2是第二低价格
2. target_prices：对每个商品的价格记录数进行判断，>=2条则取排名2的价格，否则取唯一的价格
3. checks_summary：提前汇总1check中每个商品的总数量，避免重复计算
4. 最后将汇总的数量与目标价格关联，计算总销售额

计算结果 #

按示例数据计算，最终结果为40.5（8×2.5 + 1×2.5 + 3×6 = 20 + 2.5 + 18 = 40.5）

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