文章/答案/技术大牛

发布

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;

参考链接

页面内容是否对你有帮助？

有帮助

没帮助

将列转行，并对公共列求和

sql、database、procedures

基本上我在一个临时表中有这些列，我想将它们按动态列(付款方式)分组到行，并根据付款方式汇总税额。Tax列将始终存在，因此您可以将其视为静态列。动态列存储在变量@PaymentMethod = Cash、Card等中...

浏览 9提问于2017-07-27得票数 0

1回答

MySQL旋转行和列

mysql

pict1.jpg"我在互联网上做了一些研究，显然这是通过支点实现的，但是MySQL

浏览 4提问于2015-11-15得票数 0

回答已采纳

2回答

从MySQL服务器或应用程序计算金额

mysql、sql

我有一个MySQL表，它有两个列: ID和count。它在ID字段上有一个索引。

浏览 2提问于2014-11-14得票数 0

回答已采纳

1回答

MySQL工作台无法显示二次侧栏

mysql-workbench

在VirtuboxUbuntu21.04上，mysql-workbench-community_8.0.26-1ubuntu21.04_amd64.deb，的辅助侧栏面板，即无法显示的MySQL添加面板

浏览 5提问于2021-09-08得票数 1

1回答

赔率在两列OddsNumerator和OddsDenominator中输入。所以如果获胜的几率是5/6。5在OddsNumerator列中，6在OddsDenominator列中。$data40 = mysql_query("SELECT sum(oddsnum / oddsden) FROM stats WHERE MONTH(date) = MONTH(CURRENT_TIMESTAMP) AND winloss = 'Win'"); $info40 = <e

浏览 0提问于2013-09-11得票数 1

1回答

无法理解iloc用于反转所有行和反转所有列的语法。

python、pandas、dataframe

我无法理解用于逆转Pandas中所有行和所有列的语法。2.Reversing all columns : df.iloc[:,::-1] 在一个相关的注意事项上，将如何同时逆转行和列？

浏览 5提问于2020-04-04得票数 1

回答已采纳

1回答

用image()图表示R中的矩阵

我有一个N*M矩阵，我想在R中使用image()来可视化它，但是，默认情况是将最后一列作为第一行。如何转换我所拥有的矩阵，以便图像显示第1行第1列为图像的左上角位置的矩阵？

浏览 5提问于2017-01-25得票数 0

回答已采纳

2回答

mysql sum，带有行ID

mysql、sum

在MySQL中，我试图对一列求和，但保留了行I。当我做求和运算时，is被压缩，结果是我想要的是 1,2,3 | 1500

浏览 5提问于2011-10-18得票数 1

回答已采纳

7回答

翻转行和列

code-golf、code-challenge

目标指令您将被传递一个由3个数组/列表组成的“表”，每个数组/列表有3个项作为输入。您的工作是通过编写一个可以高效和创造性地完成此操作的程序来翻转其上的行和列。您将根据下面的示例翻转“表”。

浏览 0提问于2017-06-21得票数 -1

1回答

名称中包含空格的sum MySQL列

mysql、sum

如何简单地对MySQL数据库中的一列求和？我想：但这不管用。说我在‘数量’附近有一个语法错误)我已经找了几天了，但没有发现任何对我有用的东西。我也是一个全新的MySQL新手，所以我相信这就是为什么;)

浏览 3提问于2013-06-20得票数 1

回答已采纳

1回答

使用group by汇总列，并将其存储为MySQL中的新列(永久)

mysql、sql、mysql-workbench

我在MySQL表中有50列。我想对所有这些列求和，并在同一个表(sum50)中创建一个新列。每当我更新或插入新数据时，它都应该永久地存储到MySQL表中。我知道在执行查询时如何求和，但它不会永久存储到表中。我是否应该在表中插入一个空列并执行触发器？哪个触发器是正确的？

浏览 9提问于2020-05-18得票数 0

1回答

如果和行小于上一列和，如何反转矩阵的行？

java

如何在小于上列和和的矩阵中反转行？

浏览 0提问于2022-05-12得票数 0

回答已采纳

1回答

MYSQL求和两列

mysql、join、select、group-by、sum

我有这样的情况： SELECT uf.*, SUM(uto.points) AS total_points_live, (total_points_live + total_points_online) AS total_points LEFT JOIN users_tourney_online AS uto

浏览 34提问于2020-04-03得票数 1

回答已采纳

1回答

MySQL求和多列

mysql、sum

对于每个团队: IdTeamHome时求和ScoreHome + IdTeamAway时求和ScoreAway谢谢。

浏览 1提问于2011-10-15得票数 3

回答已采纳

1回答

MYSQL旋转行并按特定列的行进行限制

mysql

我不太清楚该怎么说这个问题，所以我给了它我最好的节目，我希望这个解释能帮助我理解这个问题。任何问题，请问。create table spooner_pec id int, policy_number int, experience_rate decimal(10,2),);insert into spooner_pec values (1,'2009',183586,&

浏览 6提问于2015-08-25得票数 0

回答已采纳

3回答

求和行并插入到新列中

php、mysql、sum

，并通过电子邮件输出单个列的总和。我需要做的是对每一行求和，并在末尾的另一列中插入总和。-我只是不知道如何将求和插入到适当的列中$result = mysql_query('SELECT email, (order01+order02+order03) ASorder_total FROM `customer_orders`'); if (mysql_num_rows($result) > 0

浏览 3提问于2011-04-16得票数 1

3回答