SQL如何对多列存储的产品按项目ID分组统计所有产品金额总和

解决思路 #

你当前的表是将多组产品+金额横向存储的宽表结构，核心解法是先通过UNION ALL将三组列转为统一的纵向长表结构，再分组聚合计算金额总和，若需要你给出的横向宽格式输出结果，可再通过行转列逻辑实现。

通用统计SQL（长格式结果，兼容所有SQL数据库） #

SELECT product, SUM(amount) AS total_amount
FROM (
    -- 提取第一组产品&金额
    SELECT 
        product1 AS product, 
        CAST(REPLACE(amount1, ',', '.') AS DECIMAL(10,2)) AS amount 
    FROM doe_table 
    WHERE project_id = 2 
      AND product1 IS NOT NULL 
      AND product1 != ''
    UNION ALL
    -- 提取第二组产品&金额
    SELECT 
        product2 AS product, 
        CAST(REPLACE(amount2, ',', '.') AS DECIMAL(10,2)) AS amount 
    FROM doe_table 
    WHERE project_id = 2 
      AND product2 IS NOT NULL 
      AND product2 != ''
    UNION ALL
    -- 提取第三组产品&金额
    SELECT 
        product3 AS product, 
        CAST(REPLACE(amount3, ',', '.') AS DECIMAL(10,2)) AS amount 
    FROM doe_table 
    WHERE project_id = 2 
      AND product3 IS NOT NULL 
      AND product3 != ''
) AS all_products
GROUP BY product
ORDER BY product

运行后会直接得到每个产品对应的总金额，和你预期的数值完全一致。

匹配你示例的宽格式输出SQL（MySQL 8.0+/PostgreSQL通用） #

如果需要和你示例完全一致的多列横向输出，可以用以下写法：

SELECT 
  2 AS project_id,
  MAX(CASE WHEN rn = 1 THEN product END) AS product1,
  MAX(CASE WHEN rn = 1 THEN total_amount END) AS product1_sum,
  MAX(CASE WHEN rn = 2 THEN product END) AS product2,
  MAX(CASE WHEN rn = 2 THEN total_amount END) AS product2_sum,
  MAX(CASE WHEN rn = 3 THEN product END) AS product3,
  MAX(CASE WHEN rn = 3 THEN total_amount END) AS product3_sum,
  MAX(CASE WHEN rn = 4 THEN product END) AS product4,
  MAX(CASE WHEN rn = 4 THEN total_amount END) AS product4_sum
FROM (
  SELECT 
    product, 
    SUM(amount) AS total_amount, 
    ROW_NUMBER() OVER(ORDER BY product) AS rn
  FROM (
      SELECT product1 AS product, CAST(REPLACE(amount1, ',', '.') AS DECIMAL(10,2)) AS amount FROM doe_table WHERE project_id = 2 AND product1 IS NOT NULL AND product1 != ''
      UNION ALL
      SELECT product2 AS product, CAST(REPLACE(amount2, ',', '.') AS DECIMAL(10,2)) AS amount FROM doe_table WHERE project_id = 2 AND product2 IS NOT NULL AND product2 != ''
      UNION ALL
      SELECT product3 AS product, CAST(REPLACE(amount3, ',', '.') AS DECIMAL(10,2)) AS amount FROM doe_table WHERE project_id = 2 AND product3 IS NOT NULL AND product3 != ''
  ) AS all_products
  GROUP BY product
) AS product_sum

如果产品数量不固定，可以通过动态SQL自动生成对应列，无需手动写CASE WHEN逻辑。

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