MySQL：计算指定日期范围内的零件销售利润

根据你的需求，我们需要按PartNo分组，计算日期范围内的总销量，并用最后一条记录的零售价与批发价差值来计算总利润。下面是两种可行的MySQL查询方案，附带详细解释：

方案1：使用子查询关联（适合MySQL 5.x及以上） #

假设你的表名为inventory，执行以下SQL：

SELECT
    p.PartNo,
    (first_inv.Inv - last_inv.Inv) AS Sold,
    CONCAT('$', (first_inv.Inv - last_inv.Inv) * (last_price.Retail - last_price.Wholesale)) AS Profit
FROM
    -- 获取所有唯一的零件编号
    (SELECT DISTINCT PartNo FROM inventory) p
-- 关联获取每个零件最早日期的库存（初始库存）
JOIN
    (SELECT PartNo, Inv FROM inventory 
     WHERE (PartNo, Date) IN (SELECT PartNo, MIN(Date) FROM inventory GROUP BY PartNo)) first_inv 
    ON p.PartNo = first_inv.PartNo
-- 关联获取每个零件最晚日期的库存（剩余库存）
JOIN
    (SELECT PartNo, Inv FROM inventory 
     WHERE (PartNo, Date) IN (SELECT PartNo, MAX(Date) FROM inventory GROUP BY PartNo)) last_inv 
    ON p.PartNo = last_inv.PartNo
-- 关联获取每个零件最晚日期的零售/批发价（需先去除$符号转成数值）
JOIN
    (SELECT PartNo, 
           CAST(REPLACE(Retail, '$', '') AS DECIMAL(10,2)) AS Retail, 
           CAST(REPLACE(Wholesale, '$', '') AS DECIMAL(10,2)) AS Wholesale 
     FROM inventory 
     WHERE (PartNo, Date) IN (SELECT PartNo, MAX(Date) FROM inventory GROUP BY PartNo)) last_price 
    ON p.PartNo = last_price.PartNo;

方案2：使用窗口函数（更简洁，适合MySQL 8.0+） #

利用窗口函数给每条记录标记排序序号，快速定位最早和最晚的记录：

WITH inventory_ranked AS (
    SELECT
        PartNo,
        Inv,
        -- 去除$符号并转成数值类型，方便计算
        CAST(REPLACE(Retail, '$', '') AS DECIMAL(10,2)) AS Retail,
        CAST(REPLACE(Wholesale, '$', '') AS DECIMAL(10,2)) AS Wholesale,
        -- 按日期升序编号，1为最早记录
        ROW_NUMBER() OVER (PARTITION BY PartNo ORDER BY Date ASC) AS rn_asc,
        -- 按日期降序编号，1为最晚记录
        ROW_NUMBER() OVER (PARTITION BY PartNo ORDER BY Date DESC) AS rn_desc
    FROM inventory
)
SELECT
    PartNo,
    -- 销量 = 初始库存 - 剩余库存
    (MAX(CASE WHEN rn_asc = 1 THEN Inv END) - MAX(CASE WHEN rn_desc = 1 THEN Inv END)) AS Sold,
    -- 总利润 = 销量 ×（最后一次的零售价 - 批发价）
    CONCAT('$', (MAX(CASE WHEN rn_asc = 1 THEN Inv END) - MAX(CASE WHEN rn_desc = 1 THEN Inv END)) * 
           (MAX(CASE WHEN rn_desc = 1 THEN Retail - Wholesale END))) AS Profit
FROM inventory_ranked
GROUP BY PartNo;

关键逻辑解释（解决你的困惑） #

1. 日期范围与销量计算：
   我们不需要关注中间日期的库存变化，因为销量本质是初始库存（最早日期）减去最终剩余库存（最晚日期），这是库存管理中默认的“无进货情况下的销量”计算逻辑（你的数据中每个零件的库存都是递减的，完全符合这个场景）。

2. 非起止日期记录的处理：
   中间日期的记录不需要直接参与计算——它们的库存变动已经体现在“初始→最终”的库存差值里，而利润计算只要求用最后一次记录的价格，因此这些中间记录可以直接忽略。

3. 价格格式化处理：
   因为你的Retail和Wholesale字段带$符号，属于字符串类型，所以必须先通过REPLACE去掉符号，再转成DECIMAL数值类型才能进行减法运算，最后用CONCAT把结果转回带$的格式。

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