很多人学SQL学到表连接就停下来觉得函数不过是“临时查一下用的时候再去翻文档”。真到了写业务报表、处理数据清洗或者面试手撕SQL的时候才会发现自己被一堆函数名卡住明明需求很简单就是写不出来。这篇笔记是我自己从“看见函数就头疼”到“能按套路拆解SQL函数”的真实记录围绕的核心就是“SQL函数”这个主题并且会把聚合函数、字符串函数、窗口函数这些最常用的类别按场景讲清楚还会附上我踩过的坑和排查套路。适合刚入门想进阶的同学也适合写SQL偶尔卡壳的老手拿来即用。正文1. SQL函数的学习框架别上来就背1.1 为什么SQL函数是数据分析的基本功我见过太多人学SQL只会SELECT * FROM table遇到要“按月度汇总”“取每位用户最新一条订单”“把手机号中间四位打码”这样的需求就懵。这些需求本质都是靠函数完成的。函数是你对数据做加工的最小单元不会函数你只能看表会了函数你才能改表、算表、筛表。SQL函数学得扎实不扎实直接决定你写出来的语句是“能用”还是“能扛”。我的体会是函数并不需要死记硬背而是需要建立一套自己的“函数地图”。数据库不同函数名会有差异但底层的分类和解决问题的思路是相通的。比如MySQL里截取字符串是SUBSTRINGSQL Server里也是SUBSTRING而Oracle里是SUBSTR日期函数MySQL用DATE_ADDSQL Server用DATEADD。名字不同思想相同。你只要把思想学到手换数据库只是换查表而已。1.2 用“输入-处理-输出”模型理解函数学函数的时候我建议把所有函数都想象成一个黑盒子给它几个输入参数它执行一段固定逻辑然后吐出一个结果。以SUBSTRING(abc123, 4, 3)为例输入是字符串、开始位置、长度处理逻辑是“从第4个字符开始取3个字符”输出是123。再比如DATEDIFF(2024-01-10, 2024-01-01)输入是两个日期输出是相差的天数。这个模型虽然简单但能帮你规避绝大多数使用错误。很多人出错是因为搞不清“参数顺序”和“参数类型”。比如DATEDIFF在MySQL和SQL Server里参数顺序就正好相反MySQL是DATEDIFF(日期1, 日期2)SQL Server是DATEDIFF(单位, 开始日期, 结束日期)。如果你带着MySQL的习惯去写SQL Server结果就会完全不对。所以我学习一个新函数时会强制自己先看一遍官方文档里的参数表而不是直接抄示例这样能省掉后面一堆调试时间。1.3 函数分类一览我从实际使用频率出发把SQL函数分成六大类字符串函数、数值函数、日期时间函数、转换函数、聚合函数、窗口函数。另外还有逻辑控制类的比如CASE WHEN、COALESCE这些虽然不是传统意义上的函数但经常和函数混着用我也会记在一起。分类典型函数典型场景字符串函数CONCAT、SUBSTRING、REPLACE、CHARINDEX、TRIM拼接地址、截取编码、清洗空格数值函数ROUND、CEILING、FLOOR、ABS、MOD金额取整、余数计算日期时间函数CURRENT_DATE、DATE_ADD、DATEDIFF、DATE_PART算年龄、按月汇总、计算留存转换函数CAST、CONVERT字符串转数字、日期转字符串聚合函数COUNT、SUM、AVG、MAX、MIN统计总数、求均价窗口函数ROW_NUMBER、RANK、LAG、SUM OVER分组TopN、同比环比、累计值这张表是我自己整理速查表的起点后面每学到新函数就往里塞一行慢慢就成了自己的知识库。2. 核心函数拆解高频场景逐个过2.1 字符串函数别再用LIKE硬刚字符串函数是平时用得最杂的一类。我来举几个真实场景。第一个是拼接字段。用户表里有first_name和last_name要输出完整姓名直接用CONCAT(first_name, last_name)就行。但要注意如果其中一个字段是NULL在MySQL里整条结果会变成NULL这时候要先加IFNULL处理比如CONCAT(IFNULL(first_name,), IFNULL(last_name,))。这个坑我至少踩过三次别嫌啰嗦。第二个是截取字符串。比如订单号是ORD20250101001你想只取后面三位序列号用SUBSTRING(order_no, -3)在MySQL里可以但换到SQL Server就又不一定支持负数了。更稳的方式是SUBSTRING(order_no, LENGTH(order_no) - 2, 3)虽然丑但跨库兼容性好。类似需求还包括从身份证号里提取出生日期你可以用SUBSTRING(id_card, 7, 8)直接拿到YYYYMMDD不过别忘了再转成日期类型。第三个是替换和清洗。手机号中间四位打码用INSERT(phone, 4, 4, ****)在MySQL里很顺手在SQL Server里则可能用STUFF函数。如果你不想纠结数据库差异可以用最朴素的CONCAT(LEFT(phone,3), ****, RIGHT(phone,4))效果一样而且思路一眼就能看懂。字符串函数的核心教训是先想清楚你要“拼接、截取、替换还是定位”再去找对应函数。不要一上来就LIKE %…%那既慢又容易错。2.2 日期与时间函数格式化、时区、日期差日期函数看着不难但坑最多。我的建议是在SQL里日期就用日期类型别把日期当字符串到处转。很多慢SQL就是这么来的——在索引列上套了DATE()函数导致索引失效后面我会详细讲。先说最常用的取当前日期和时间MySQL里NOW()返回当前时间CURRENT_DATE只返回日期SQL Server用GETDATE()。跨平台项目里我习惯用CURRENT_TIMESTAMP因为这是SQL标准大多数数据库都支持。日期加减也很常见。比如你要统计过去30天的订单MySQL可以写WHERE order_date DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)SQL Server写WHERE order_date DATEADD(DAY, -30, CAST(GETDATE() AS DATE))。两边的思路都是“先取得一个基准日期再减30天”只是语法不同。日期差计算要看你的目标。计算两个日期相差几天MySQL用DATEDIFF(end, start)返回天数SQL Server用DATEDIFF(DAY, start, end)。计算月份差SQL Server用DATEDIFF(MONTH, start, end)MySQL则没有直接函数通常用PERIOD_DIFF(DATE_FORMAT(end,%Y%m), DATE_FORMAT(start,%Y%m))来凑。这也是为什么我强调要按数据库版本整理函数别拿一个习惯到处用。还有一个很容易忽略的问题是“时区”。如果你的数据库和应用服务器不在一个时区用NOW()拿到的时间可能和业务认为的“今天”对不上。我建议业务统计的截止时间统一用应用层传入的“业务日期”少依赖数据库服务器本地时间否则存储过程或定时任务会出莫名其妙的边界数据。2.3 数值与转换函数警惕隐式转换数值函数本身不难比如ROUND(amount, 2)做四舍五入CEILING(price)向上取整FLOOR(price)向下取整。真正容易出问题的是把字符串数字和数值类型混着算。举一个我真实遇到过的例子订单表里的discount字段是VARCHAR类型里面存的是0.8这样的折扣率。某天我写WHERE amount * discount 100结果出现一堆奇奇怪怪的记录。排查到最后发现是那些discount字段里有空格或0.8折这种带单位的值隐式转换直接把字符串转成了0。从那以后凡是字段类型可疑我都会先CAST(discount AS DECIMAL(10,2))一下并且用WHERE discount REGEXP ^[0-9](\.[0-9])?$过滤脏数据。转换函数里CAST(value AS type)是标准写法MySQL和SQL Server都支持。CONVERT在SQL Server里多了样式的选项可以顺便做日期格式化比如CONVERT(VARCHAR(10), order_date, 120)会输出2025-01-01。如果只是转日期格式这种写法很方便但可读性一般我个人更喜欢在查询里直接DATE_FORMAT(order_date, %Y-%m-%d)牺牲一点通用性换来一眼能看懂。数值计算的另一个注意点是“精度”。ROUND在某些数据库里对DECIMAL和DOUBLE的表现并不完全一致做金额计算时尽量用DECIMAL而不是FLOAT否则会让对账变成一场灾难。2.4 聚合函数与GROUP BY的配合聚合函数是写报表的基础。COUNT、SUM、AVG、MAX、MIN大家都熟但有几个细节我见过不少人写错。第一COUNT(*)和COUNT(列名)不一样。COUNT(*)统计的是行数包括所有NULL行COUNT(列名)统计的是该列非NULL值的个数。如果你想知道某列到底填充了多少个值用后者如果你只是想要总行数用前者不要混。第二SUM遇到NULL不会报错但结果可能不是你想要的。比如SUM(amount discount)如果某行的discount是NULL那amount discount就是NULL这行就被SUM忽略了。正确写法是SUM(amount IFNULL(discount, 0))。这就是为什么我说函数学习必须把NULL处理放在第一位。第三GROUP BY之后SELECT里能出现的列其实是受限的。除了分组列本身其他列必须用聚合函数包起来不然很多数据库会直接报错或者查出毫无意义的值。这个规则理解起来容易但我见过新手为了查“每个类目的商品名”在GROUP BY category后直接SELECT product_name结果查出来一个随机商品名还以为是bug。聚合函数也经常和HAVING一起用。WHERE是在分组前过滤HAVING是在分组后过滤。比如你要看“订单数超过100个的用户”就必须先按用户分组再用HAVING COUNT(*) 100。这个顺序写错了会出现明明分组后只有5个用户前一刻还查出了全表记录的情况。3. 窗口函数SQL进阶必学3.1 窗口函数解决什么问题窗口函数是近几年面试和实际业务里出镜率最高的函数类别。它解决的核心问题是在不合并行的前提下对每组数据进行计算。听起来有点绕我举个例子。普通聚合函数GROUP BY会把多行压成一行比如按部门汇总薪资结果每个部门只有一行汇总数。但有时候你想看每一条员工记录同时旁边带一列“本部门平均薪资”这时候窗口函数就派上用场了。SELECT employee_name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary FROM employee;这条SQL不会减少行数每一行都保留同时多出一列dept_avg_salary这就是窗口函数“在窗口内计算、但不合并行”的精髓。窗口函数特别适合做排名、累计、移动平均、同比环比这类分析型需求。3.2 常用窗口函数逐个讲我最常用的窗口函数有五个ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()/LEAD()以及聚合函数配合OVER()。ROW_NUMBER()是给每一行一个不重复的序号适合取TopN、去重。RANK()和DENSE_RANK()处理并列名次的方式不同比如成绩是100、99、99、98RANK()出来的名次是1、2、2、4DENSE_RANK()是1、2、2、3。面试官很喜欢考这个区别一定要记牢。LAG(列, N)可以取当前行往前N行的值LEAD是往后N行。做同环比特别方便比如要算每个用户相邻两笔订单的时间差就可以用LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date)拿到下一笔订单日期再做DATEDIFF。聚合函数加上OVER()后可以算累计值比如SELECT month, amount, SUM(amount) OVER (ORDER BY month) AS cumulative_amount FROM monthly_sales;这个ORDER BY month定义了“从开头到当前行”的窗口范围结果就是逐月累计的销售额。窗口函数的完整语法是函数 OVER (PARTITION BY ... ORDER BY ...)PARTITION BY决定分组ORDER BY决定组内排序和窗口范围两者都可以省略但省略后语义会变化新手容易搞混。3.3 用窗口函数解决去重难题网上关于“SQL语句去重”的问题特别多传统写法是SELECT DISTINCT或者GROUP BY。但DISTINCT只能去掉完全相同的行如果两条记录主键不同、只是业务键重复DISTINCT就无能为力了。比如订单表里同一个订单号因为重试产生了多条记录你想保留最新一条标准的做法是窗口函数加序号。SELECT order_id, user_id, amount FROM ( SELECT order_id, user_id, amount, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY create_time DESC ) AS rn FROM orders ) t WHERE rn 1;这个写法先按order_id分组再按create_time倒序排序给每个分组内的行标号最后只取标号为1的行。这个模式可以套用到非常多场景比如“每个用户最近一篇文章”“每个商品最近一次库存变动”。比起GROUP BY加聚集函数的笨办法窗口函数的可读性和维护性好得多。4. 实战教学从笔记到能用的SQL4.1 案例一计算用户留存率留存率是运营分析里绕不开的指标。假设用户表有注册日期register_date和最后活跃日期last_active_date想算每天新增用户里有多少人在注册次日还活跃。SELECT register_date, COUNT(DISTINCT user_id) AS new_users, COUNT(DISTINCT CASE WHEN last_active_date DATE_ADD(register_date, INTERVAL 1 DAY) THEN user_id END) AS retained_users FROM user_info GROUP BY register_date;这里用的是聚合函数CASE WHEN。CASE WHEN在满足条件时输出user_id不满足时输出NULL然后COUNT(DISTINCT)只会统计非NULL值这样就把次日活跃人数算出来了。如果你要算3日留存、7日留存把DATE_ADD后面的间隔改一下就行。我第一次写这个SQL时犯了一个错没有加DISTINCT结果因为用户一天有多次活跃记录把活跃人数算重复了。所以只要涉及“人数”大脑就要自动反射出“可能要COUNT DISTINCT”。4.2 案例二分组取TopN面试高频题来了每个部门工资最高的三个人。SELECT department_id, employee_id, salary FROM ( SELECT department_id, employee_id, salary, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rn FROM employee ) t WHERE rn 3;这道题的关键是记住“窗口函数先排好序外层用WHERE过滤序号”。如果只想取薪资最高的那一个人把rn 3改成rn 1就行。但要注意如果存在并列薪资ROW_NUMBER()会随机分配顺序这时候你需要明确业务规则是允许并列还是必须唯一排序。允许并列就用RANK()或DENSE_RANK()不允许并列就用ROW_NUMBER()加一个唯一排序键。4.3 案例三函数与慢SQL优化别在索引列上套函数这是我要重点强调的优化坑。假设订单表在create_time上有索引你想查某一天的订单有些人会这么写SELECT * FROM orders WHERE DATE(create_time) 2024-01-01;这个写法逻辑上没毛病但它对create_time套了DATE()函数会导致索引失效数据库只能全表扫描。表小时无所谓表一上千万行查询就卡死。正确的改法是范围查询SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;这样既保留的函数的表达意图也不会破坏索引。推而广之所有对索引列套函数的写法都要警惕比如WHERE YEAR(create_time) 2024改成create_time 2024-01-01 AND create_time 2025-01-01。慢SQL优化的大头往往不是加个索引而是先把这种“函数套索引列”的写法清干净。4.4 案例四JSON字段查询新场景新函数现在很多系统会把扩展信息直接存成JSON字段。以前只能取出来在代码里解析现在主流数据库原生支持JSON函数。比如MySQL里可以用JSON_EXTRACT(ext_info, $.level)取JSON中某个键的值JSON_UNQUOTE再去掉引号SELECT user_id, JSON_UNQUOTE(JSON_EXTRACT(ext_info, $.channel)) AS channel, JSON_UNQUOTE(JSON_EXTRACT(ext_info, $.level)) AS user_level FROM user_log WHERE JSON_EXTRACT(ext_info, $.channel) app;再新一点的MySQL版本还提供JSON_VALUE可以直接返回标量值比JSON_EXTRACT方便。SQL Server也有JSON_VALUE(column, $.key)的写法。遇到这种需求先查一下自己数据库版本支持的JSON函数不要上来就写字符串截取。字符串截取JSON的思路不仅慢而且遇到嵌套结构或数组基本就崩了。5. 学习笔记方法论如何高效积累函数5.1 建自己的函数速查表我从第二个月学SQL开始就不再零零散散抄函数了而是建了一个表格专门记录函数名、作用、语法示例、适用数据库和坑。每次遇到一个新函数就多一行。这样到后面虽然不能背出所有函数但看一眼需求马上就能在速查表里定位到“该用哪一类函数”。表格里我还专门加了一列“容易踩的坑”。比如MySQL的GROUP_CONCAT默认长度限制是1024字节超出的部分会被截断SQL Server的STRING_AGG在SQL Server 2017以后才有老版本得用STUFF FOR XML PATH。这些坑在文档里不会很明显必须自己记。5.2 排查SQL问题的顺序先数据、后函数、再索引我排查SQL问题有一个固定顺序。第一步查数据本身该列有没有NULL有没有脏数据有没有隐式类型问题。第二步查函数逻辑参数顺序是不是写反了CASE WHEN是不是漏了ELSE窗口函数的分组和排序是不是漏了。第三步看执行计划索引走没走扫描行数是不是异常有没有临时文件排序。这个顺序能省下大量时间。很多人一遇到SQL慢第一反应就是“换个写法”结果换了半天最后发现是数据里混了空格。所以我建议你把“数据优先”刻在脑子里函数写错最多算算错数据脏才是结果错乱的根源。5.3 面试考点这些函数最容易被问根据我自己面试和被面试的经验SQL函数相关的考点集中在四个地方。第一个是聚合函数和GROUP BY的配合尤其WHERE和HAVING的区别以及COUNT(*)和COUNT(列名)的区别。第二个是窗口函数ROW_NUMBER、RANK、DENSE_RANK的排序规则OVER里PARTITION BY和ORDER BY的作用。第三个是NULL值处理COALESCE、IFNULL、IS NULL的组合用法。第四个是字符串和日期的转换问“如何把字符串转日期”“如何计算两个日期间隔”这类基础题。另外一个常被问到的点是函数在WHERE和SELECT中的性能影响。你需要能说出“条件列套函数可能导致索引失效”这个基本结论并且能给出改写思路。面试官不期待你把所有函数倒背如流但期待你有意识地去评估写法的代价。6. 踩坑笔记函数学习中的真实教训6.1 NULL参与运算的坑NULL在SQL里不是0也不是空字符串它代表“未知”。这意味着任何和NULL做算术运算的结果都是NULL。比如amount discount只要discount是NULL结果就变NULL再被聚合函数忽略最后统计就少了这条数据。我处理这类问题的习惯是在进入运算前先用IFNULL(字段, 默认值)或者COALESCE(字段, 字段2, 默认值)把可能的NULL兜住。COALESCE能接受多个参数返回第一个非NULL值比IFNULL更灵活推荐优先使用。6.2 隐式转换造成的类型错误隐式类型转换是排查成本比较高的坑。字符串列和数字列比较时数据库会自动把字符串转成数字比如WHERE user_rank 2可能命中user_rank2和user_rank 2但也可能把abc转成0导致误命中。更麻烦的是隐式转换可能让索引失效。比如一个VARCHAR类型的列存了电话号码你拿数字WHERE phone 13800138000去查MySQL通常会把字符串列转成数字来比较索引就用不上。解决办法是让类型始终一致查字符串就写WHERE phone 13800138000不要偷懒省引号。6.3 日期格式化的区域差异日期格式化函数的写法不同数据库差异很大。MySQL用DATE_FORMATSQL Server用FORMATOracle用TO_CHAR返回结果也可能因为数据库区域设置而不同。比如SQL Server里FORMAT(order_date, yyyy-MM-dd)会随语言环境变化有的环境会返回yyyy-MM-dd有的会翻译成别的格式。更保险的做法是能用标准函数解决的就不要用方言函数。例如获取当前时间标准SQL委员会指定的CURRENT_TIMESTAMP适配性最好。如果一定要用方言函数先在测试环境验证一次别直接在正式环境改。6.4 字符串函数在不同数据库的差异字符函数是跨库差异的重灾区。SUBSTRING和SUBSTR只是其中一个例子。截断空格MySQL有TRIM()SQL Server也有TRIM()但老版本没有需要用LTRIM和RTRIM组合。字符串长度MySQL的LENGTH()返回字节数CHAR_LENGTH()返回字符数SQL Server的LEN()默认返回字符数但不包括末尾空格这点就够让人懵一阵。如果你的项目可能要从MySQL迁移到SQL Server或者反过来强烈建议在函数速查表里做一个“跨库差异”专区把凡是涉及字符串和日期的函数都标出来。否则一上线出问题的一定是这些看起来最简单的函数。6.5 函数结果不稳定导致的预估错误最后一个坑是关于函数本身的结果稳定性。有些函数每次执行的结果是确定的比如ABS(-1)永远是1。有些函数则不那么稳定比如RAND()返回随机数NOW()和CURRENT_TIMESTAMP在不同时间执行返回不同值。如果你在WHERE条件里直接写NOW()同一个查询在不同时间执行会得到不同结果这在做数据抽取或定时任务时特别容易出问题。我的建议是在脚本里明确指定基准时间变量需要固定当前时间就先SET today CURRENT_DATE再在后续所有地方引用这个变量。这样整个任务的口径才会一致也方便后期排查。SQL函数的学习说白了就是“积累套路记录坑”。我到现在写SQL也偶尔会翻记录文字类的文档和速查表比脑子好使。每次遇到新函数或新坑先想清楚它是解决什么场景的问题然后把语法和注意点记下来。时间长了函数就不再是零散的碎片而是一张能帮你快速定位方案的网。