如何在MySQL中实现跨列数值均分？基于列B的Quarterly规则分配方案

当然可以用SQL实现这个需求！咱们先把需求拆解清楚，再一步步给出实现方案：

需求回顾 #

• 当列B的值为Quarterly时，需要把对应季度列的数值平均分配到连续的3个月份列中
  • 例1：第1行2020_1列的120，除以3得到40，填充到2020_1、2020_2、2020_3
  • 例2：第3行2020_1列的240（除以3得80）填充到2020_1-2020_3；2020_4列的90（除以3得30）填充到2020_4-2020_6

实现思路 #

1. 先筛选出列B为Quarterly的行，只有这些行需要做分配处理
2. 对每个季度起始列（比如2020_1、2020_4）计算平均值（原值÷3）
3. 用条件赋值的方式，把平均值填充到对应的3个连续月份列，非目标列保持原有数值

具体SQL实现 #

方案1：查询生成结果（不修改原表）

假设你的表名为your_table，包含唯一标识列id、列B，以及月份列2020_1到2020_12，可以用以下查询语句：

SELECT
    id,
    B,
    -- 处理2020年第一季度对应的三个月份
    CASE
        WHEN B = 'Quarterly' AND 2020_1 IS NOT NULL THEN 2020_1 / 3.0
        ELSE 2020_1
    END AS 2020_1,
    CASE
        WHEN B = 'Quarterly' AND 2020_1 IS NOT NULL THEN 2020_1 / 3.0
        ELSE 2020_2
    END AS 2020_2,
    CASE
        WHEN B = 'Quarterly' AND 2020_1 IS NOT NULL THEN 2020_1 / 3.0
        ELSE 2020_3
    END AS 2020_3,
    -- 处理2020年第二季度对应的三个月份
    CASE
        WHEN B = 'Quarterly' AND 2020_4 IS NOT NULL THEN 2020_4 / 3.0
        ELSE 2020_4
    END AS 2020_4,
    CASE
        WHEN B = 'Quarterly' AND 2020_4 IS NOT NULL THEN 2020_4 / 3.0
        ELSE 2020_5
    END AS 2020_5,
    CASE
        WHEN B = 'Quarterly' AND 2020_4 IS NOT NULL THEN 2020_4 / 3.0
        ELSE 2020_6
    END AS 2020_6,
    -- 剩余月份列无季度数据时保持原值
    2020_7, 2020_8, 2020_9, 2020_10, 2020_11, 2020_12
FROM your_table;

方案2：直接更新原表

如果需要直接修改原表中的数据，可以用UPDATE语句：

UPDATE your_table
SET
    2020_1 = CASE WHEN B = 'Quarterly' AND 2020_1 IS NOT NULL THEN 2020_1 / 3.0 ELSE 2020_1 END,
    2020_2 = CASE WHEN B = 'Quarterly' AND 2020_1 IS NOT NULL THEN 2020_1 / 3.0 ELSE 2020_2 END,
    2020_3 = CASE WHEN B = 'Quarterly' AND 2020_1 IS NOT NULL THEN 2020_1 / 3.0 ELSE 2020_3 END,
    2020_4 = CASE WHEN B = 'Quarterly' AND 2020_4 IS NOT NULL THEN 2020_4 / 3.0 ELSE 2020_4 END,
    2020_5 = CASE WHEN B = 'Quarterly' AND 2020_4 IS NOT NULL THEN 2020_4 / 3.0 ELSE 2020_5 END,
    2020_6 = CASE WHEN B = 'Quarterly' AND 2020_4 IS NOT NULL THEN 2020_4 / 3.0 ELSE 2020_6 END
WHERE B = 'Quarterly'; -- 只更新符合条件的行，提升效率

注意事项 #

• 用3.0而非3是为了确保结果为浮点数，避免整数除法导致的精度丢失（比如某些SQL方言中120/3会返回整数40，但如果是121/3，用3会得到40，用3.0会得到40.333...）
• 如果你的表中有更多年份的季度列（比如2021_1、2021_4），可以按照相同逻辑扩展CASE语句
• 若需要处理NULL值，CASE中的AND 2020_1 IS NOT NULL可以避免对空值进行无效计算

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