将Excel SUMIFS函数转换为MySQL语句的技术问询

我来帮你搞定这两个Excel公式转MySQL的需求，先拆解下每个公式的逻辑，再修正你的SQL代码：

1. 拆解Excel公式的核心逻辑 #

（1）ReplenQty（G2单元格） #

Excel的SUMIFS(D:D,A:A,A2,B:B,B2,C:C,">="&E2,C:C,"<="&F2)意思是：

针对当前行的Part（A列）和Customer（B列），统计所有满足OrdDt（C列）在StartDate（E列）到ReplenDate（F列）之间的OrdQty（D列）总和。

（2）RpInVar（H2单元格） #

Excel的IF($A2<>$A1,ROUND(VAR(IF($A:$A=$A2,$G:$G)),2),0)意思是：

当当前行的Part和上一行的Part不一样时，计算该Part对应的所有ReplenQty的方差（保留两位小数）；如果和上一行Part相同，就显示0。注意Excel的VAR()是样本方差，不是总体方差。

2. 修正后的完整MySQL语句 #

这里提供两种实现方式，按需选择：

方式一：直接关联子查询（直观对应Excel逐行逻辑） #

SELECT
    t.Part,
    t.Customer,
    t.OrdDt,
    t.OrdQty,
    t.StartDate,
    t.ReplenDate,
    -- 对应Excel的SUMIFS计算ReplenQty
    (SELECT SUM(s.OrdQty)
     FROM your_table_name s
     WHERE s.Part = t.Part
       AND s.Customer = t.Customer
       AND s.OrdDt >= t.StartDate
       AND s.OrdDt <= t.ReplenDate) AS ReplenQty,
    -- 对应Excel的IF+VAR逻辑计算RpInVar
    CASE
        -- 判断是否是当前Part的第一行（上一行Part不同或没有上一行）
        WHEN LAG(t.Part) OVER (ORDER BY t.Part, t.Customer, t.OrdDt) <> t.Part
             OR LAG(t.Part) OVER (ORDER BY t.Part, t.Customer, t.OrdDt) IS NULL
        THEN ROUND(
            -- 计算当前Part所有ReplenQty的样本方差，保留两位小数
            VAR_SAMP(
                (SELECT SUM(s.OrdQty)
                 FROM your_table_name s
                 WHERE s.Part = t.Part
                   AND s.Customer = t.Customer
                   AND s.OrdDt >= t.StartDate
                   AND s.OrdDt <= t.ReplenDate)
            ) OVER (PARTITION BY t.Part),
            2
        )
        ELSE 0
    END AS RpInVar
FROM your_table_name t
-- 排序要和Excel的行顺序一致，保证LAG()取到正确的上一行
ORDER BY t.Part, t.Customer, t.OrdDt;

方式二：用CTE预计算ReplenQty（更高效，适合大数据量） #

如果你的表数据量较大，重复计算ReplenQty会影响效率，先用CTE预先算出所有行的ReplenQty，再计算方差：

WITH replen_qty_cte AS (
    SELECT
        Part,
        Customer,
        OrdDt,
        OrdQty,
        StartDate,
        ReplenDate,
        (SELECT SUM(s.OrdQty)
         FROM your_table_name s
         WHERE s.Part = t.Part
           AND s.Customer = t.Customer
           AND s.OrdDt >= t.StartDate
           AND s.OrdDt <= t.ReplenDate) AS ReplenQty
    FROM your_table_name t
)
SELECT
    Part,
    Customer,
    OrdDt,
    OrdQty,
    StartDate,
    ReplenDate,
    ReplenQty,
    CASE
        WHEN LAG(Part) OVER (ORDER BY Part, Customer, OrdDt) <> Part
             OR LAG(Part) OVER (ORDER BY Part, Customer, OrdDt) IS NULL
        THEN ROUND(VAR_SAMP(ReplenQty) OVER (PARTITION BY Part), 2)
        ELSE 0
    END AS RpInVar
FROM replen_qty_cte
ORDER BY Part, Customer, OrdDt;

3. 关键注意事项 #

• 把代码里的your_table_name替换成你实际的表名；
• Excel的VAR()对应MySQL的VAR_SAMP()（样本方差），如果需要总体方差，换成VAR_POP()；
• ORDER BY t.Part, t.Customer, t.OrdDt必须和Excel里的行排序逻辑一致，否则LAG()函数获取的上一行Part会出错；
• 如果你的MySQL版本低于8.0，不支持窗口函数（LAG()、OVER()），可以告诉我，我再给你调整用变量实现的版本。

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