MariaDB/MySQL按分组规则实现列部分求和的视图编写问题

核心逻辑说明 #

你需要的求和逻辑可以拆分为两部分合并计算：

• set_item值为0的行，对应items列值直接累加
• set_item值大于0的行，同year、同group、同set_item分组下的items值仅累加一次
  由于group是SQL保留关键字，所有引用位置需要用反引号包裹避免语法报错。

最优实现代码 #

兼容全版本SQL写法（无需窗口函数支持） #

该写法兼容MySQL 5.x等不支持窗口函数的低版本数据库，逻辑简洁易维护：

CREATE VIEW result_view AS
SELECT 
    a.year,
    a.`group`,
    -- 计算sum_items_1
    SUM(CASE WHEN set_item_1 = 0 THEN items_1 ELSE 0 END) 
    + IFNULL((SELECT SUM(DISTINCT items_1) FROM example e1 
              WHERE e1.year = a.year AND e1.`group` = a.`group` AND e1.set_item_1 > 0), 0) AS sum_items_1,
    -- 计算sum_items_2
    SUM(CASE WHEN set_item_2 = 0 THEN items_2 ELSE 0 END) 
    + IFNULL((SELECT SUM(DISTINCT items_2) FROM example e2 
              WHERE e2.year = a.year AND e2.`group` = a.`group` AND e2.set_item_2 > 0), 0) AS sum_items_2
FROM example a
GROUP BY a.year, a.`group`
ORDER BY a.year DESC, a.`group`;

高版本数据库窗口函数写法 #

如果使用MySQL 8.0+、PostgreSQL等支持窗口函数的数据库，也可以用以下写法，数据量大时性能更优：

CREATE VIEW result_view AS
WITH calc_1 AS (
    SELECT 
        year,`group`,
        SUM(CASE WHEN set_item_1=0 OR row_num=1 THEN items_1 ELSE 0 END) AS sum_items_1
    FROM (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY year,`group`,set_item_1 ORDER BY subgroup) AS row_num
        FROM example
    ) t GROUP BY year,`group`
),
calc_2 AS (
    SELECT 
        year,`group`,
        SUM(CASE WHEN set_item_2=0 OR row_num=1 THEN items_2 ELSE 0 END) AS sum_items_2
    FROM (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY year,`group`,set_item_2 ORDER BY subgroup) AS row_num
        FROM example
    ) t GROUP BY year,`group`
)
SELECT calc_1.year, calc_1.`group`, sum_items_1, sum_items_2
FROM calc_1 JOIN calc_2 ON calc_1.year = calc_2.year AND calc_1.`group` = calc_2.`group`
ORDER BY calc_1.year DESC, calc_1.`group`;

结果验证 #

执行SELECT * FROM result_view即可得到你给出的预期输出，完全匹配计算规则要求。

内容的提问来源于stack exchange，提问作者José Manuel