MySQL-sql聚合（多行）函数-sum()

作者：马育民 • • 阅读：10048

介绍

SUM() 是 MySQL 中用于计算数值列总和的聚合函数，它会对指定列中的所有非 NULL 值进行求和运算。该函数主要用于统计分析场景，如计算总销售额、总数量、总分等。

使用方法

数值类型

SUM() 仅适用于 数值类型（INT、DECIMAL、FLOAT、DOUBLE 等）

null 处理

  • 自动忽略列中的 NULL 值（这是聚合函数的通用特性）
  • 如果所有值都是 NULL 或没有匹配的行，SUM() 返回 NULL

计算逻辑

  • 遍历符合条件的所有行
  • 对每行的指定列值进行累加
  • 返回最终累加结果

精度处理

  • 对于整数类型，返回相同类型或更大范围的整数
  • 对于小数类型，保持相应的精度

返回值

返回 0

  • 字符串类型的字段，并且该字段都 不是数字，那么返回 0

  • 字符串类型的字段，如果该字段，有的值是数字，就返回这些数字相加

返回 NULL

  • 该列所有的值都为 null，就返回 null

没有符合条件的记录，就返回 null

例子

查询工资的总和

select sum(sal) from emp

查询工资和奖金的总和

select sum(sal+comm)  from emp

错误：上面结果是错的，如果有一个值为 null，那么 sal+comm 后的值是 null，需要对 null 的处理

函数的嵌套

将一个函数嵌套在另一个函数里

解决上面问题的方法：

select sum(nvl(sal,0)+nvl(comm,0)) from  emp;

返回null

没有符合条件的记录，就返回null

select sum(sal)  from emp where ename = '呵呵'

案例

1. 准备数据

-- 创建示例销售表
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    sale_date DATE,
    region VARCHAR(50),
    discount DECIMAL(4, 2) NULL  -- 折扣率，可为NULL
);

-- 插入示例数据
INSERT INTO sales (product_id, quantity, unit_price, sale_date, region, discount) VALUES
(101, 5, 99.99, '2024-01-15', 'North', 0.10),
(102, 3, 149.99, '2024-01-15', 'South', NULL),
(101, 2, 99.99, '2024-01-16', 'North', 0.05),
(103, 10, 29.99, '2024-01-16', 'East', 0.15),
(102, 1, 149.99, '2024-01-17', 'West', 0.20);

2. 基础求和用法

-- 计算总销售数量
SELECT SUM(quantity) AS total_quantity FROM sales;  -- 返回 21

-- 计算总销售额（数量 × 单价）
SELECT SUM(quantity * unit_price) AS total_sales_amount FROM sales;  -- 返回 1649.70

-- 处理 NULL 值（折扣率默认按0处理）
SELECT SUM(quantity * unit_price * (1 - COALESCE(discount, 0))) AS actual_sales FROM sales;

3. 结合 WHERE 条件筛选

-- 计算2024年1月15日的总销售额
SELECT SUM(quantity * unit_price) AS sales_15th 
FROM sales 
WHERE sale_date = '2024-01-15';  -- 返回 849.92

-- 计算北区的销售总量
SELECT SUM(quantity) AS north_quantity 
FROM sales 
WHERE region = 'North';  -- 返回 7

4. 结合 GROUP BY 分组统计

-- 按地区分组统计销售额
SELECT 
    region,
    SUM(quantity * unit_price) AS region_sales,
    SUM(quantity) AS region_quantity
FROM sales 
GROUP BY region;

-- 按日期分组统计每日销售
SELECT 
    sale_date,
    SUM(quantity * unit_price) AS daily_sales
FROM sales 
GROUP BY sale_date 
ORDER BY sale_date;

5. 结合 HAVING 过滤分组结果

-- 筛选销售额超过500的地区
SELECT 
    region,
    SUM(quantity * unit_price) AS region_sales
FROM sales 
GROUP BY region 
HAVING region_sales > 500;  -- 返回 North (699.93)

6. 与其他聚合函数结合

-- 综合销售统计
SELECT
    COUNT(*) AS total_orders,
    SUM(quantity) AS total_quantity,
    SUM(quantity * unit_price) AS total_sales,
    AVG(quantity * unit_price) AS avg_order_value,
    MAX(quantity * unit_price) AS max_order_value
FROM sales;

7. 处理可能的 NULL 结果

-- 使用 IFNULL 确保返回0而非NULL
SELECT IFNULL(SUM(quantity), 0) AS total_quantity 
FROM sales 
WHERE region = 'Unknown';  -- 返回 0

性能优化要点

1. 索引优化：

-- 为经常筛选的列创建索引
CREATE INDEX idx_sales_date ON sales(sale_date);
CREATE INDEX idx_sales_region ON sales(region);

-- 复合索引（适合 GROUP BY + SUM 场景）
CREATE INDEX idx_sales_region_date ON sales(region, sale_date);

2. 避免在 SUM() 中使用复杂表达式：

-- 不推荐（每次计算都要执行复杂运算）
SELECT SUM(quantity * unit_price * (1 - COALESCE(discount, 0)) * 1.13) FROM sales;

-- 推荐（先计算基础值，再统一处理）
SELECT SUM(amount) * 1.13 FROM (
SELECT quantity * unit_price * (1 - COALESCE(discount, 0)) AS amount 
FROM sales
) AS temp;

3. 限制数据范围：

  • 使用 WHERE 子句过滤不需要的数据
  • 避免对全表进行 SUM 计算

常见问题与解决方案

  1. 大数求和溢出：

    -- 使用更大范围的数据类型
SELECT SUM(CAST(quantity AS BIGINT)) FROM large_table;

  2. 精度丢失（浮点类型）：

    -- 优先使用 DECIMAL 类型而非 FLOAT/DOUBLE
SELECT SUM(CAST(float_column AS DECIMAL(18,6))) FROM sales;

总结

  1. SUM() 函数用于计算数值列的总和，自动忽略 NULL 值，主要适用于统计分析场景。
  2. 可以与 WHERE、GROUP BY、HAVING 等子句结合，实现复杂的分组统计。
  3. 性能优化的关键是合理创建索引、减少计算复杂度以及限制数据范围，同时注意处理可能的 NULL 结果和精度问题。

原文出处：/show_1GW2JjKPcUKt.html

吉ICP备15003043号