SQL 常用函数大全：聚合、字符串、数值、日期、窗口函数解析
一、聚合函数（用于统计分析）
聚合函数用于对一组值进行计算并返回单个值，常与GROUP BY配合使用。
| 函数 | 作用 | 示例 |
|---|---|---|
COUNT() | 统计行数 | SELECT COUNT(*) AS 总记录数 FROM students;（统计学生表总人数）SELECT COUNT(DISTINCT class) AS 班级数 FROM students;（统计不重复的班级数） |
SUM() | 求和 | SELECT SUM(score) AS 总分 FROM exam WHERE student_id = 1001;（计算 1001 号学生的总分） |
AVG() | 求平均值 | SELECT AVG(score) AS 平均分 FROM exam WHERE class = '高一(1)班';（计算高一 1 班平均分） |
MAX() | 求最大值 | SELECT MAX(score) AS 最高分 FROM exam;（查询所有考试成绩的最高分） |
MIN() | 求最小值 | SELECT MIN(age) AS 最小年龄 FROM students;（查询学生表中最小年龄） |
二、字符串函数（处理文本数据）
不同数据库（MySQL/PostgreSQL/SQL Server）的字符串函数略有差异，以下以MySQL为例：
| 函数 | 作用 | 示例 |
|---|---|---|
CONCAT() | 拼接字符串 | SELECT CONCAT('姓名：', name, '，年龄：', age) AS 学生信息 FROM students WHERE id = 1;（输出：姓名：张三，年龄：18） |
LENGTH() | 计算字符串长度（字节） | SELECT LENGTH(name) AS 姓名长度 FROM students WHERE id = 1;（张三返回 6，utf8 下一个汉字占 3 字节） |
CHAR_LENGTH() | 计算字符串长度（字符数） | SELECT CHAR_LENGTH(name) AS 姓名字符数 FROM students WHERE id = 1;（张三返回 2） |
SUBSTRING() | 截取字符串 | SELECT SUBSTRING('Hello World', 1, 5) AS 截取结果;（返回 Hello，参数：字符串、起始位置、长度） |
UPPER()/LOWER() | 转大写 / 小写 | SELECT UPPER(name) AS 大写姓名, LOWER(email) AS 小写邮箱 FROM students; |
TRIM() | 去除首尾空格 | SELECT TRIM(' 张三 ') AS 去空格结果;（返回张三） |
三、数值函数（处理数字）
以 MySQL 为例：
| 函数 | 作用 | 示例 |
|---|---|---|
ROUND() | 四舍五入 | SELECT ROUND(3.14159, 2) AS 结果;（返回 3.14，第二个参数是保留小数位数） |
CEIL() | 向上取整 | SELECT CEIL(3.1) AS 结果;（返回 4） |
FLOOR() | 向下取整 | SELECT FLOOR(3.9) AS 结果;（返回 3） |
ABS() | 绝对值 | SELECT ABS(-5) AS 结果;（返回 5） |
MOD() | 取模（余数） | SELECT MOD(10, 3) AS 结果;（返回 1） |
四、日期函数（处理时间日期）
以 MySQL 为例：
| 函数 | 作用 | 示例 |
|---|---|---|
NOW() | 获取当前日期时间 | SELECT NOW() AS 当前时间;（返回 2026-01-14 10:30:00） |
CURDATE() | 获取当前日期 | SELECT CURDATE() AS 当前日期;（返回 2026-01-14） |
CURTIME() | 获取当前时间 | SELECT CURTIME() AS 当前时间;（返回 10:30:00） |
DATE_FORMAT() | 格式化日期 | SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日') AS 格式化日期;（返回 2026 年 01 月 14 日） |
DATEDIFF() | 计算两个日期差值 | SELECT DATEDIFF('2026-01-20', '2026-01-14') AS 天数差;（返回 6，单位：天） |
DATE_ADD() | 日期加减 | SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS 一周后;（返回 2026-01-21）SELECT DATE_ADD(CURDATE(), INTERVAL -1 MONTH) AS 一个月前;（返回 2025-12-14） |
五、条件函数（逻辑判断）
1. IF 函数（MySQL）
-- 语法：IF(条件, 满足条件返回值, 不满足返回值)SELECTname,score,IF(score>=60,'及格','不及格')AS成绩状态FROMexam;2. CASE WHEN（通用所有数据库）
-- 语法1：简单CASESELECTname,CASEclassWHEN'高一(1)班'THEN'一班'WHEN'高一(2)班'THEN'二班'ELSE'其他班级'ENDAS班级简称FROMstudents;-- 语法2：搜索CASE（更灵活）SELECTname,score,CASEWHENscore>=90THEN'优秀'WHENscore>=80THEN'良好'WHENscore>=60THEN'及格'ELSE'不及格'ENDAS成绩等级FROMexam;六、NULL 处理函数
空值（NULL）是 SQL 中极易踩坑的点，这类函数专门解决 NULL 的判断和替换问题，所有数据库通用。
| 函数 | 作用 | 示例 |
|---|---|---|
IS NULL / IS NOT NULL | 判断是否为空值 | SELECT name FROM students WHERE phone IS NULL;（查询手机号为空的学生）SELECT name FROM students WHERE email IS NOT NULL;（查询邮箱非空的学生） |
IFNULL()（MySQL）/ISNULL()（SQL Server） | 替换 NULL 值 | SELECT name, IFNULL(phone, '未填写') AS 手机号 FROM students;（手机号为空则显示 “未填写”） |
COALESCE()（通用） | 返回第一个非 NULL 值 | SELECT name, COALESCE(phone, email, '无联系方式') AS 联系方式 FROM students;（优先取手机号，无则取邮箱，都无则显示 “无联系方式”） |
NULLIF()（通用） | 若两个值相等返回 NULL，否则返回第一个值 | SELECT NULLIF(score, 0) AS 有效分数 FROM exam;（分数为 0 则返回 NULL，否则返回分数） |
示例：
-- 统计有效订单数（排除金额为NULL或0的订单）SELECTCOUNT(COALESCE(NULLIF(amount,0),NULL))AS有效订单数FROM`order`;七、转换函数：CAST & CONVERT（主流数据库通用）
转换函数主要分两类：CAST()（ANSI SQL 标准，跨数据库通用）和CONVERT()（部分数据库扩展，支持格式定制），优先用CAST()保证兼容性，需要格式控制时用CONVERT()。
1. 通用语法
| 函数 | 语法 | 适用场景 |
|---|---|---|
CAST() | CAST(待转换值 AS 目标类型) | 简单类型转换，跨数据库通用 |
CONVERT() | MySQL：CONVERT(待转换值, 目标类型)SQL Server：CONVERT(目标类型, 待转换值, 格式码) | MySQL 中与 CAST 等价；SQL Server 中可定制日期 / 数字格式 |
2. 支持的常见目标类型
不同数据库的类型名称略有差异，核心常用类型如下：
| 类型大类 | MySQL | SQL Server | PostgreSQL |
|---|---|---|---|
| 数值类型 | INT, DECIMAL(M,D), FLOAT | INT, DECIMAL(M,D), FLOAT | INTEGER, NUMERIC(M,D), FLOAT |
| 字符串类型 | CHAR(N), VARCHAR(N) | CHAR(N), VARCHAR(N) | CHAR(N), VARCHAR(N) |
| 日期时间类型 | DATE, DATETIME, TIMESTAMP | DATE, DATETIME, SMALLDATETIME | DATE, TIMESTAMP, TIMESTAMPTZ |
生产级实战示例（按场景分类）
场景 1：数值 ↔ 字符串转换（最常用）
解决 “字符串数字无法计算”“数字拼接文字” 问题：
-- 1. 字符串转数字（计算订单金额）-- 场景：order_amount字段是VARCHAR类型，需求和SELECTCAST(order_amountASDECIMAL(10,2))*0.9AS折扣金额FROM`order`;-- 2. 数字转字符串（拼接备注）-- 场景：将数值型的用户ID拼接成“用户-1001”格式SELECTCONCAT('用户-',CAST(user_idASCHAR))AS用户标识FROMuser;-- 3. SQL Server定制格式（数字转带千分位的字符串）SELECTCONVERT(VARCHAR(20),123456.78,1)AS格式化金额;-- 返回 123,456.78场景 2：字符串 ↔ 日期时间转换（开发高频）
解决 “字符串日期无法排序 / 筛选” 问题：
-- 1. 字符串转日期（筛选指定时间段订单）-- MySQLSELECT*FROM`order`WHERECAST(order_date_strASDATE)BETWEEN'2026-01-01'AND'2026-01-14';-- SQL Server（指定格式码，避免解析错误）-- 格式码 120：YYYY-MM-DD HH:MI:SS；111：YYYY/MM/DDSELECT*FROM`order`WHERECONVERT(DATETIME,order_date_str,120)>='2026-01-01 00:00:00';-- 2. 日期转字符串（格式化展示）-- MySQL：将DATETIME转为“YYYY年MM月DD日”格式SELECTDATE_FORMAT(CAST(create_timeASDATETIME),'%Y年%m月%d日')AS创建时间FROMuser;-- PostgreSQL（用::操作符简写）SELECTcreate_time::DATEAS创建日期FROMuser;-- 日期转字符串（默认格式）场景 3：特殊类型转换
-- 1. NULL值处理（转换时避免NULL导致失败）-- 场景：将可能为NULL的字符串字段转为数字，NULL则返回0SELECTCAST(IFNULL(score_str,'0')ASINT)AS分数FROMexam;-- 2. 布尔值转换（PostgreSQL/MySQL）-- MySQL：BOOL本质是TINYINT，TRUE=1，FALSE=0SELECTCAST(TRUEASCHAR)AS布尔转字符串;-- 返回 '1'-- PostgreSQL：布尔值转字符串SELECTCAST(TRUEASVARCHAR)AS布尔转字符串;-- 返回 't'常见错误与避坑指南
转换失败报错：比如将非数字字符串（如 ‘123a’）转为 INT，会直接报错。
解决方案：先过滤无效值
-- MySQL：用REGEXP判断是否为纯数字SELECTCASEWHENscore_strREGEXP'^[0-9]+$'THENCAST(score_strASINT)ELSE0ENDAS分数FROMexam;日期格式不匹配：不同数据库对日期字符串的解析规则不同（比如 MySQL 支持 ‘2026-01-14’，但不支持 ‘01/14/2026’ 直接转换）。
解决方案：先统一日期格式，再转换
-- MySQL：先替换分隔符，再转日期SELECTCAST(REPLACE('01/14/2026','/','-')ASDATE)AS转换后日期;精度丢失：DECIMAL 转 FLOAT 可能丢失精度，优先用 CAST (DECIMAL AS CHAR) 再处理。
转换函数是保证数据类型统一的核心工具：
优先用CAST()：遵循 ANSI 标准，跨数据库兼容性更好；需要格式定制时用CONVERT()（仅 SQL Server/MySQL）；
转换前做校验：对非标准值（如 NULL、含特殊字符的字符串）先过滤 / 替换，避免转换失败；
注意类型匹配：日期转换要保证字符串格式与数据库解析规则一致，数值转换要避免精度丢失。
八、窗口函数
普通聚合函数（SUM/COUNT/AVG）会将多行数据合并为一行（比如统计每个部门的总销售额，结果只有部门 + 总额），而窗口函数的核心是：
不合并行，在保留原有行数据的基础上，对指定范围（窗口）的数据做聚合 / 排名计算。
这解决了开发中 “既要显示明细，又要显示统计值” 的高频需求（比如：显示每个员工的销售额，同时显示该员工在部门内的排名、部门总销售额）。
窗口函数的基础语法
窗口函数的通用结构分为两部分，核心是OVER()子句（定义 “窗口” 范围）：
<窗口函数>OVER([PARTITIONBY分组字段]-- 可选，按指定字段分组（类似GROUP BY，但不合并行）[ORDERBY排序字段ASC/DESC]-- 可选，对窗口内的数据排序[ROWS/RANGEBETWEEN边界1AND边界2]-- 可选，定义窗口的行范围（比如“前1行到当前行”）)窗口函数的分类及实战示例
窗口函数主要分 3 大类，覆盖 90% 的业务场景，以下以 MySQL 8.0 + 为例（PostgreSQL/SQL Server 语法一致）：
1. 排名类窗口函数（排行榜 / 名次统计必备）
最常用的 3 个排名函数，核心区别是 “如何处理并列名次”：
| 函数 | 作用 | 特点 | 示例场景 |
|---|---|---|---|
ROW_NUMBER() | 生成连续唯一排名 | 无并列，即使值相同，排名也不同（1,2,3,4） | 生成不重复的员工销售额排名 |
RANK() | 生成排名 | 有并列，并列后跳过后续名次（1,2,2,4） | 竞赛排名（并列第 2 后，下一个是第 4） |
DENSE_RANK() | 生成密集排名 | 有并列，并列后不跳过后续名次（1,2,2,3） | 等级统计（如：A 级、B 级，并列不影响后续等级） |
实战示例：按销售额给部门内员工排名
-- 准备测试数据CREATETABLEemployee(deptVARCHAR(20),-- 部门nameVARCHAR(20),-- 姓名salesINT-- 销售额);INSERTINTOemployeeVALUES('销售部','张三',10000),('销售部','李四',8000),('销售部','王五',8000),('技术部','赵六',5000),('技术部','钱七',6000);-- 核心查询：显示每个员工的3种排名SELECTdept,name,sales,ROW_NUMBER()OVER(PARTITIONBYdeptORDERBYsalesDESC)ASrn,-- 连续排名RANK()OVER(PARTITIONBYdeptORDERBYsalesDESC)ASrk,-- 跳级排名DENSE_RANK()OVER(PARTITIONBYdeptORDERBYsalesDESC)ASdrk-- 密集排名FROMemployee;查询结果（直观对比 3 种排名）：
| dept | name | sales | rn | rk | drk |
|---|---|---|---|---|---|
| 销售部 | 张三 | 10000 | 1 | 1 | 1 |
| 销售部 | 李四 | 8000 | 2 | 2 | 2 |
| 销售部 | 王五 | 8000 | 3 | 2 | 2 |
| 技术部 | 钱七 | 6000 | 1 | 1 | 1 |
| 技术部 | 赵六 | 5000 | 2 | 2 | 2 |
2. 聚合类窗口函数（累计 / 分组统计必备）
将 SUM/COUNT/AVG/MIN/MAX 等聚合函数作为窗口函数，实现 “不合并行的聚合统计”，开发中最常用的是累计求和和分组总/ 均值。
实战场景 1：统计每个用户的累计订单金额
-- 准备订单数据CREATETABLE`order`(user_idINT,order_dateDATE,amountDECIMAL(10,2));INSERTINTO`order`VALUES(1,'2026-01-01',100.00),(1,'2026-01-02',200.00),(1,'2026-01-03',150.00),(2,'2026-01-01',300.00);-- 核心查询：按用户分组、日期排序，计算累计金额SELECTuser_id,order_date,amount,SUM(amount)OVER(PARTITIONBYuser_idORDERBYorder_date)AStotal_amount-- 累计求和FROM`order`;查询结果：
| user_id | order_date | amount | total_amount |
|---|---|---|---|
| 1 | 2026-01-01 | 100.00 | 100.00 |
| 1 | 2026-01-02 | 200.00 | 300.00 |
| 1 | 2026-01-03 | 150.00 | 450.00 |
| 2 | 2026-01-01 | 300.00 | 300.00 |
实战场景 2：显示每个员工销售额 + 部门总销售额 + 部门均值
SELECTdept,name,sales,SUM(sales)OVER(PARTITIONBYdept)ASdept_total,-- 部门总销售额AVG(sales)OVER(PARTITIONBYdept)ASdept_avg-- 部门平均销售额FROMemployee;偏移类窗口函数（环比 / 相邻数据对比必备）
用于获取 “当前行的上一行 / 下一行” 数据，核心解决 “环比增长”“前后数据对比” 问题，常用函数：
LAG(字段, n, 默认值)：获取当前行前 n 行的字段值（n 默认 1，默认值可选）
LEAD(字段, n, 默认值)：获取当前行后 n 行的字段值
实战场景：计算每日销售额的环比增长（当日 - 前日）
-- 准备日销售额数据CREATETABLEdaily_sales(sale_dateDATE,amountDECIMAL(10,2));INSERTINTOdaily_salesVALUES('2026-01-01',1000),('2026-01-02',1200),('2026-01-03',900);-- 核心查询：计算环比SELECTsale_date,amount,LAG(amount,1,0)OVER(ORDERBYsale_date)ASprev_amount,-- 前日销售额amount-LAG(amount,1,0)OVER(ORDERBYsale_date)ASdiff-- 环比差值FROMdaily_sales;查询结果：
| sale_date | amount | prev_amount | diff |
|---|---|---|---|
| 2026-01-01 | 1000 | 0 | 1000 |
| 2026-01-02 | 1200 | 1000 | 200 |
| 2026-01-03 | 900 | 1200 | -300 |
窗口函数的避坑要点
版本兼容：MySQL 8.0 + 才支持窗口函数，5.7 及以下版本需用子查询 / 变量替代；
PARTITION BY vs GROUP BY：
GROUP BY：合并行，结果集行数 = 分组数；
PARTITION BY：不合并行，结果集行数 = 原表行数；
ORDER BY 的影响：
聚合类窗口函数加ORDER BY：默认统计 “从分组第一行到当前行” 的聚合值（如累计求和）；
不加ORDER BY：统计整个分组的聚合值（如部门总销售额）；
性能优化：对PARTITION BY和ORDER BY的字段建立索引，可大幅提升窗口函数执行效率。
窗口函数是处理复杂统计的核心工具，关键要点：
核心优势：不合并行，同时保留明细和统计值，解决 “明细 + 统计” 的高频业务需求；
三大类场景：排名类（ROW_NUMBER/RANK）、聚合类（SUM/AVG OVER）、偏移类（LAG/LEAD）覆盖 90% 的统计需求；
核心语法：OVER()子句中，PARTITION BY分组，ORDER BY排序，按需组合即可实现复杂统计。
前三大常用函数的总结
聚合函数是统计分析的核心，重点掌握COUNT/SUM/AVG/MAX/MIN，需结合GROUP BY使用；
字符串 / 数值 / 日期函数需注意不同数据库的语法差异（如 SQL Server 用LEN()替代LENGTH()）；
条件函数（IF/CASE WHEN）是实现业务逻辑判断的关键，CASE WHEN兼容性更强，推荐优先使用。
