mysql中数学函数与聚合函数的使用方法

MySQL 数学函数用于单值计算并直接返回结果，常见如 ABS、ROUND、FLOOR、CEILING、POWER、SQRT；聚合函数如 COUNT、SUM、AVG 等需配合 GROUP BY 使用，且不可嵌套，WHERE 用数学函数过滤行，HAVING 用聚合函数过滤组。

MySQL 中常用数学函数怎么用

数学函数在 MySQL 中用于对单个数值做计算，不涉及分组或统计，直接返回一个结果。它们常出现在 SELECT、WHERE 或 ORDER BY 子句里。

容易出错的地方是传入 NULL 或非数字类型——多数数学函数遇到 NULL 会直接返回 NULL，而传入字符串（如 'abc'）会被隐式转为 0，可能掩盖数据问题。

• ABS(x)：取绝对值，ABS(-3.5) → 3.5
• ROUND(x, d)：四舍五入到小数点后 d 位，ROUND(3.14159, 2) → 3.14；省略 d 则取整
• FLOOR(x) 和 CEILING(x)：向下 / 向上取整，FLOOR(3.9) → 3，CEILING(3.1) → 4
• POWER(x, y) 或 POW(x, y)：求 x 的 y 次方，POWER(2, 3) → 8
• SQRT(x)：开平方，x 必须 ≥ 0，否则返回 NULL

SELECT    id,   price,   ROUND(price * 1.08, 2) AS price_with_tax,   FLOOR(price / 100) AS hundred_blocks FROM products WHERE SQRT(price) > 10;

GROUP BY 场景下必须用 聚合函数

只要写了 GROUP BY，所有出现在 SELECT 列表里的非分组字段，都必须包裹在聚合函数中，否则 MySQL 8.0+ 会报错：Expression #2 of SELECT list is not in GROUP BY clause。

这不是“可选优化”，而是 SQL 标准强制要求：每个非分组列必须有明确的聚合语义。即使你确定某列在组内值相同（比如 category_name 和 category_id 关联），也不能裸写，得用 MAX(category_name) 或 ANY_VALUE(category_name)（后者需关闭 sql_mode 中的 ONLY_FULL_GROUP_BY）。

• COUNT() 统计行数：COUNT(*) 计所有行，COUNT(col) 只计非 NULL 值
• SUM()、AVG()、MIN()、MAX() 对数值列操作，遇到 NULL 自动跳过
• GROUP_CONCAT(col) 把组内值拼成字符串，默认用逗号分隔，可加 SEPARATOR 和 ORDER BY

SELECT    category_id,   COUNT(*) AS total_items,   AVG(price) AS avg_price,   GROUP_CONCAT(name ORDER BY price DESC SEPARATOR '|') AS top_names FROM products GROUP BY category_id HAVING AVG(price) > 50;

聚合函数不能嵌套，但可以和数学函数混用

像 SUM(ROUND(price, 0)) 是合法的，因为 ROUND() 作用于每行，再由 SUM() 汇总；但 ROUND(SUM(price), 2) 也没问题，先汇总再四舍五入。真正禁止的是聚合套聚合，比如 SUM(AVG(price)) 或 COUNT(MAX(score))，MySQL 会直接报语法错误。

注意浮点精度问题：AVG() 返回 DECIMAL 或 DOUBLE 类型，取决于输入列；如果对 price（DECIMAL(10,2)）求平均，结果可能带多位小数，建议显式 ROUND(AVG(price), 2) 控制输出精度。

• 聚合函数内部不能出现未分组的裸列，例如 SELECT SUM(price * tax_rate) 合法，但 SELECT SUM(price * @rate)（变量）也合法；而 SELECT SUM(price * other_col) 就非法，除非 other_col 在 GROUP BY 中
• STDDEV() 和 VARIANCE() 计算标准差和方差，只对数值列有效，且忽略 NULL

WHERE 和 HAVING 的 区别 决定了函数位置

过滤单行数据用 WHERE，此时只能用数学函数（如 WHERE ABS(delta) > 5）；过滤分组结果用 HAVING，此时只能用聚合函数（如 HAVING COUNT(*) > 10）。混淆两者会导致逻辑错误或报错。

一个典型误用是试图在 WHERE 中用 AVG()：MySQL 不允许，因为 WHERE 执行时还没分组、也没算出平均值。必须改写为子查询或移到 HAVING。

• WHERE 在分组前执行，可加速过滤（减少参与聚合的数据量）
• HAVING 在分组后执行，不可避免要先算完所有组的聚合值
• 想同时用两种函数？常见模式是：先用数学函数加工字段（如 YEAR(created_at)），再用它分组，最后在 HAVING 里对聚合结果过滤

SELECT    YEAR(order_date) AS y,   COUNT(*) AS cnt FROM orders WHERE status = 'completed'  -- 先筛出行，减少聚合量 GROUP BY YEAR(order_date) HAVING COUNT(*) >= 100;     -- 再筛出满足条件的年份

实际写复杂查询时，最容易被忽略的是：聚合函数的 NULL 处理逻辑、ONLY_FULL_GROUP_BY 模式的影响、以及数学函数在 隐式类型转换 下的“静默失败”。别依赖直觉判断字符串能不能当数字用，先查 SELECT '123abc' + 0 看结果再说。