如何用SQL进行数据库表的小计与总计计算？ROLLUP功能详解

当前位置：知识管理交流 →『技术文档交流』

admin

2024年3月13日 12:10 本文热度 359

在数据分析中，常常需要进行数据的汇总和聚合分析。

之前的文章中已经介绍了聚合函数、开窗函数、group by语句等方法来实现数据表的聚合分析。接下来，我们将使用MySQL和PostgreSQL这两个常见的关系型数据库，来演示如何在SQL中使用ROLLUP实现数据库表的小计和总计计算。

示例数据：

首先，我们创建一个简单的sales表，并插入一些数据。

-- 建表
CREATE TABLE sales (
year INT,
quarter INT,
amount DECIMAL(10, 2)
);
 
-- 插入数据
INSERT INTO sales VALUES
(2021, 1, 100),
(2021, 2, 150),
(2021, 3, 200),
(2021, 4, 250),
(2022, 1, 120),
(2022, 2, 180),
(2022, 3, 220),
(2022, 4, 270);
 
-- 数据表如下：
SELECT
  *
FROM
  Sales
;

数据表如下：

01 MySQL中的ROLL UP

以下查询按年份和季度对销售额进行汇总，并使用ROLLUP生成各级别的汇总。

SELECT
  year,
  quarter,
  SUM(amount) as total_amount
FROM
  Sales
GROUP BY
  year,
  quarter WITH ROLLUP
;

结果如下：

当使用ROLLUP时，任何被GROUP BY的列都可能在结果集中出现NULL值，这表示该列的汇总值。上面的示例中，2021年所有季度的汇总值为700，2022年所有季度的汇总值为790，所有年份的汇总值为1490。

若要将NULL换成小计或者总计，可以结合COALESCE函数，见如下SQL示例：

SELECT
  COALESCE( year,'总计') as year,
  COALESCE( quarter,'小计') as quarter,
  SUM(amount) as total_amount
FROM
  sales
GROUP BY
  year,
  quarter WITH ROLLUP
;

结果如下：

02 PostgreSQL中的ROLL UP

PostgreSQL也支持ROLLUP，但其语法略有不同。在PostgreSQL中，你需要在GROUP BY子句后使用ROLLUP()函数。

SELECT
  year,
  quarter,
  SUM(amount) as total_amount
FROM
  Sales
GROUP BY
  ROLLUP(year, quarter)
ORDER BY  year,quarter
;

结果如下，和MySQL的结果一致：

若要将NULL换成小计或者总计，可以结合COALESCE函数。这里要注意的是需要将year和quarter字段转换为字符串类型，SQL如下：

SELECT
  COALESCE(year::text,'总计') AS year,
  COALESCE(quarter::text,'小计') AS quarter,
  SUM(amount) as total_amount
FROM
  sales
GROUP BY
  ROLLUP(year, quarter)
ORDER BY
  year,quarter
;

若不转换为字符串类型，会报如下错误：

查询结果如下：

在实践中，根据实际需求和数据库类型，可以使用相应的语法来实现 ROLL UP 功能，并根据结果进行进一步的数据分析和报表制作。

该文章在 2024/3/14 9:12:37 编辑过

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。