mysql列转行求和

基础概念

MySQL中的列转行求和是指将某一列的数据转换为多行，并对这些数据进行求和操作。这通常涉及到数据的透视和聚合操作。

类型

使用UNION ALL：将多列数据合并为一列，并进行求和。
使用CASE语句：根据条件将数据转换为多行，并进行求和。
使用PIVOT操作：将某一列的数据转换为多列，并进行求和（MySQL 8.0及以上版本支持）。

应用场景

销售数据分析：将不同产品的销售额转换为多行，并进行求和。
用户行为分析：将不同用户的操作次数转换为多行，并进行求和。
财务报表：将不同科目的金额转换为多行，并进行求和。

示例代码

假设我们有一个销售数据表sales，结构如下：

CREATE TABLE sales (
    product_id INT,
    sales_amount DECIMAL(10, 2)
);

使用`UNION ALL`进行列转行求和

SELECT 'Total' AS product_id, SUM(sales_amount) AS total_sales_amount
FROM sales
UNION ALL
SELECT product_id, SUM(sales_amount) AS total_sales_amount
FROM sales
GROUP BY product_id;

使用`CASE`语句进行列转行求和

SELECT 
    'Total' AS product_id, 
    SUM(CASE WHEN product_id IS NULL THEN sales_amount ELSE 0 END) AS total_sales_amount,
    SUM(CASE WHEN product_id = 1 THEN sales_amount ELSE 0 END) AS product_1_sales_amount,
    SUM(CASE WHEN product_id = 2 THEN sales_amount ELSE 0 END) AS product_2_sales_amount
FROM sales;

使用`PIVOT`操作进行列转行求和（MySQL 8.0及以上版本）

SELECT 
    COALESCE(product_id, 'Total') AS product_id,
    SUM(CASE WHEN product_id = 1 THEN sales_amount ELSE 0 END) AS product_1_sales_amount,
    SUM(CASE WHEN product_id = 2 THEN sales_amount ELSE 0 END) AS product_2_sales_amount,
    SUM(sales_amount) AS total_sales_amount
FROM (
    SELECT product_id, sales_amount
    FROM sales
) AS src
PIVOT(
    SUM(sales_amount)
    FOR product_id IN (1, 2)
) AS pvt
UNION ALL
SELECT 'Total', SUM(product_1_sales_amount), SUM(product_2_sales_amount), SUM(total_sales_amount)
FROM (
    SELECT 
        COALESCE(product_id, 'Total') AS product_id,
        SUM(CASE WHEN product_id = 1 THEN sales_amount ELSE 0 END) AS product_1_sales_amount,
        SUM(CASE WHEN product_id = 2 THEN sales_amount ELSE 0 END) AS product_2_sales_amount,
        SUM(sales_amount) AS total_sales_amount
    FROM (
        SELECT product_id, sales_amount
        FROM sales
    ) AS src
    PIVOT(
        SUM(sales_amount)
        FOR product_id IN (1, 2)
    ) AS pvt
) AS sub;

遇到的问题及解决方法

问题：为什么使用`UNION ALL`时会出现重复数据？

原因：UNION ALL会将所有数据合并在一起，不会去除重复数据。

解决方法：使用UNION代替UNION ALL，UNION会自动去除重复数据。

SELECT 'Total' AS product_id, SUM(sales_amount) AS total_sales_amount
FROM sales
UNION
SELECT product_id, SUM(sales_amount) AS total_sales_amount
FROM sales
GROUP BY product_id;

问题：为什么使用`CASE`语句时求和结果不正确？

原因：可能是由于CASE语句中的条件不正确或数据类型不匹配。

解决方法：检查CASE语句中的条件是否正确，并确保数据类型匹配。

SELECT 
    'Total' AS product_id, 
    SUM(CASE WHEN product_id IS NULL THEN sales_amount ELSE 0 END) AS total_sales_amount,
    SUM(CASE WHEN product_id = 1 THEN sales_amount ELSE 0 END) AS product_1_sales_amount,
    SUM(CASE WHEN product_id = 2 THEN sales_amount ELSE 0 END) AS product_2_sales_amount
FROM sales;

问题：为什么使用`PIVOT`操作时出现错误？

原因：可能是由于MySQL版本不支持PIVOT操作，或者PIVOT语句的语法不正确。

解决方法：确保MySQL版本为8.0及以上，并检查PIVOT语句的语法是否正确。

SELECT 
    COALESCE(product_id, 'Total') AS product_id,
    SUM(CASE WHEN product_id = 1 THEN sales_amount ELSE 0 END) AS product_1_sales_amount,
    SUM(CASE WHEN product_id = 2 THEN sales_amount ELSE 0 END) AS product_2_sales_amount,
    SUM(sales_amount) AS total_sales_amount
FROM (
    SELECT product_id, sales_amount
    FROM sales
) AS src
PIVOT(
    SUM(sales_amount)
    FOR product_id IN (1, 2)
) AS pvt
UNION ALL
SELECT 'Total', SUM(product_1_sales_amount), SUM(product_2_sales_amount), SUM(total_sales_amount)
FROM (
    SELECT 
        COALESCE(product_id, 'Total') AS product_id,
        SUM(CASE WHEN product_id = 1 THEN sales_amount ELSE 0 END) AS product_1_sales_amount,
        SUM(CASE WHEN product_id = 2 THEN sales_amount ELSE 0 END) AS product_2_sales_amount,
        SUM(sales_amount) AS total_sales_amount
    FROM (
        SELECT product_id, sales_amount
        FROM sales
    ) AS src
    PIVOT(
        SUM(sales_amount)
        FOR product_id IN (1, 2)
    ) AS pvt
) AS sub;