接手一个老项目的报表模块时我一度以为SQL就是把表里的数据查出来顶多加个WHERE条件过滤一下。直到那个需求摆在我面前要把一段混乱的备注字段拆出订单号、把不同格式的日期统一口径、还要在结果集里做排名和同环比。我翻遍了搜索引擎花了整整两天才拼凑出一堆函数用法。后来我痛定思痛把日常开发里真正高频的SQL方法函数按场景整理成了笔记也就是你现在看到的这篇。这篇“SQL方法函数1”没有任何教科书式的面面俱到全部是我们写报表、做数据清洗、优化查询时真正会碰到的内容字符串怎么拆、日期怎么算、分组统计怎么才不踩坑、窗口函数到底解决什么问题、以及函数用不好为什么会让查询慢得离谱。适合正在学SQL的入门者也适合写SQL写到怀疑人生的报表开发。这一篇先把内置函数讲透下一篇再聊存储过程和自定义函数。1. 先说清楚SQL里的“方法函数”到底指什么很多刚接触SQL的人会把“方法”和“函数”混在一起问其实SQL标准里并没有严格意义上的“方法”概念这是面向对象编程的词汇。在SQL Server、MySQL、PostgreSQL这类关系型数据库里我们说的“方法”通常指两类东西一类是数据库内置函数比如字符串处理、日期计算、聚合统计另一类是用户自定义的存储过程和函数对象可以理解成把一段SQL逻辑封装起来、按名调用。本文重点放在内置函数上它们是SQL语句里最基础也最常用的“函数式能力”。我见过太多同行写复杂报表时宁可复制粘贴一大段重复的过滤逻辑也不愿意花两分钟查一下有没有现成函数结果代码动辄几百行改一个口径要全文搜索替换。其实很多看似复杂的处理一个函数就能解决。举一个最典型的例子。在SQL Server里字符串拼接用但MySQL里用的是CONCAT如果某个连接字段里混入了NULL值拼接结果会变成NULL。这种差异在跨数据库迁移时最容易炸。我自己就吃过一次亏项目从MySQL迁到SQL Server原来跑得好好的报表突然大量返回空值排查半天才发现是CONCAT和IFNULL的兼容性差异。所以整理SQL方法函数的第一价值不是背语法而是建立一种“SQL是有逻辑处理能力”的思维方式。你写SELECT * FROM 订单表 WHERE 下单时间 2024-01-01只是查数据你写SELECT 客户ID, COUNT(*) FROM 订单表 GROUP BY 客户ID HAVING COUNT(*) 10就已经在做统计判断了。函数就是让这种判断变得干净利落的工具。2. 字符串处理函数清洗脏数据的头号武器2.1 拆分和截取CHARINDEX SUBSTRING 的组合拳报表需求里十个有八个是清洗脏数据。比如我们的订单备注字段是这么存的“订单号SO20241108客户张三金额399”。要单独提取订单号不能靠眼睛看得用函数。SQL Server里最核心的两个字符串函数是CHARINDEX找位置和SUBSTRING截取。逻辑很简单-- 找出“订单号”后面第一个字符的位置 SELECT CHARINDEX(订单号, 备注) AS 起始位置 FROM 订单表;CHARINDEX返回的是子字符串在父字符串中第一次出现的位置从1开始计数。配合SUBSTRING就能精确截取DECLARE 备注 VARCHAR(200) 订单号SO20241108客户张三金额399; SELECT SUBSTRING( 备注, CHARINDEX(订单号, 备注) LEN(订单号), -- 从订单号后面开始 CHARINDEX(, 备注, CHARINDEX(订单号, 备注)) - CHARINDEX(订单号, 备注) - LEN(订单号) -- 到分号结束 ) AS 提取的订单号;这个写法虽然绕但是通用。核心思想是先定位关键标识符再计算截取长度。我给新人培训时常说字符串处理就是“找位置、量长度、下刀子”三步位置找对了剩下的都好办。MySQL里对应的写法是LOCATE和SUBSTRING_INDEX逻辑类似但函数名不同跨库的时候要注意。比如SUBSTRING_INDEX(备注, , 1)就能直接取分号前的内容比SQL Server那一串嵌套简洁不少。2.2 替换和去空格REPLACE 与 LTRIM/RTRIM 的日常应用数据从Excel导入数据库时最常见的问题就是字段前后带着空格、中间混着全角半角字符。LTRIM去掉左边空格RTRIM去掉右边空格SQL Server 2017及以上版本可以用TRIM一次解决两边。REPLACE就更常用了把字符串里的指定内容替换成新内容。我们处理用户地址时经常要把“省”“市”这类冗余词去掉再入库一条语句就搞定UPDATE 客户表 SET 地址 REPLACE(REPLACE(地址, 省, ), 市, );这里有两个容易忽略的细节。第一REPLACE在数据量大的表上会逐行更新建议先SELECT预览结果再执行UPDATE别一股脑跑了。第二SQL Server的REPLACE不支持正则表达式遇到复杂的模式匹配得用PATINDEX或者交给应用层处理不要硬怼。2.3 去重场景里DISTINCT 和 ROW_NUMBER 怎么选热搜词里“SQL语句去重”出现了很多次我单独说下函数层面的处理。很多人一说到去重就只知道DISTINCT它确实简单但它是对整个结果集去重粒度太粗。如果你要“每个客户取最近一单”DISTINCT就无能为力了这需要窗口函数配合。-- 只去重保留所有客户ID SELECT DISTINCT 客户ID FROM 订单表; -- 每个客户取最近一单窗口函数方案 SELECT 客户ID, 订单号, 下单时间 FROM ( SELECT 客户ID, 订单号, 下单时间, ROW_NUMBER() OVER (PARTITION BY 客户ID ORDER BY 下单时间 DESC) AS rn FROM 订单表 ) t WHERE rn 1;窗口函数去重的思路相当于给每一行编号再筛编号为1的行。这个方案看起来很繁琐但它是处理“分组TopN”类需求的标准做法模型上新人都应该学会。DISTINCT适合整行唯一性检查ROW_NUMBER适合按组去重且要保留明细两者的使用场景完全不同用错一个就会导致结果多一行或者少一行。3. 日期与数值函数统计口径的统一全靠它们3.1 日期获取与格式化GETDATE 和 FORMAT 的灵活组合业务统计里最闹心的往往不是算数而是日期口径。比如“近30天”到底是自然月还是滚动天数“上个月”的起始时间怎么定这时候日期函数就是唯一的依靠。获取当前时间SQL Server用GETDATE()MySQL用NOW()PostgreSQL用NOW()或CURRENT_TIMESTAMP。差异不大但返回格式有区别。SQL Server的GETDATE()返回包含毫秒的日期时间类型直接拿来和字符串比较容易出现隐式转换问题。格式化日期SQL Server的FORMAT函数很好用但性能上要留个心眼SELECT FORMAT(下单时间, yyyy-MM-dd) AS 下单日期 FROM 订单表;FORMAT走的是.NET的格式化机制灵活是真灵活慢也是真慢。在几百万行的表上用FORMAT做GROUP BY的分组键查询计划往往不理想因为索引列被函数包裹后再也无法利用索引。我自己的经验是报表里能用CONVERT解决的格式化就尽量别用FORMAT比如CONVERT(VARCHAR(10), 下单时间, 120)结果一样但性能差距很大。3.2 日期计算DATEADD 与 DATEDIFF 做同环比日期加减用DATEADD算两个日期之间的差值用DATEDIFF这两个函数在写同环比报表时简直是救星。月度同比的思路是这样的先计算出上个月同期的日期范围再去对比本期的数据。比如统计2024年5月的订单量和2023年5月的订单量SELECT YEAR(下单时间) AS 年份, MONTH(下单时间) AS 月份, COUNT(*) AS 订单量 FROM 订单表 WHERE 下单时间 DATEADD(YEAR, -1, 2024-05-01) AND 下单时间 2024-06-01 GROUP BY YEAR(下单时间), MONTH(下单时间);这里的DATEADD(YEAR, -1, 2024-05-01)直接算出2023年5月1日配合半开区间和把整个月份包含进去就不用再费劲拼接日期字符串了。DATEDIFF算账龄也很直观。比如应收账款表里要按账龄分段SELECT 客户名称, 应收金额, DATEDIFF(DAY, 应还日期, GETDATE()) AS 逾期天数, CASE WHEN DATEDIFF(DAY, 应还日期, GETDATE()) 30 THEN 30天以内 WHEN DATEDIFF(DAY, 应还日期, GETDATE()) 60 THEN 31-60天 ELSE 60天以上 END AS 账龄区间 FROM 应收账款表;这里有个小坑DATEDIFF按边界取整比如从“2024-01-01”到“2024-01-31”算DAY差值是30但到2月1日才变成31。如果业务上的逾期天数包含当天你得自己决定要不要加1口径一定要在需求评审时确认清楚不然报表数据对不上账背锅的就是你。3.3 数值处理ROUND、FLOOR、CEILING、ABS 别混着用数值函数的坑相对少但语义差异明显。ROUND四舍五入到指定小数位FLOOR向下取整CEILING向上取整ABS取绝对值。做金额报表时我强烈建议把舍入规则写死在SQL里不要依赖前端展示时再处理。比如订单折扣后金额经常出现小数点后三位账务上要求保留两位且四舍五入那就用ROUND(金额, 2)。如果业务规则要求“抹零”那就用FLOOR(金额 * 100) / 100先放大再向下取整再还原这类细节在代码评审中最容易被忽略但恰恰是财务对账的核心。还有一个常被问到的点ROUND返回的结果类型可能改变。SQL Server中ROUND返回和输入参数相同类型的值如果你传入的是int结果还是int所以做金额计算时最好先把字段转成DECIMAL避免出现ROUND(9/2, 2)得到4而不是4.50的情况。4. 聚合函数与分组统计从数人头到算占比都要稳4.1 五大聚合函数的基础用法与常见误用SUM、AVG、COUNT、MAX、MIN是SQL聚合的“五虎将”。它们的共同特点是配合GROUP BY对一组行做汇总输出的每行代表一个分组的结果。容易出问题的是COUNT。COUNT(*)统计行数COUNT(列名)统计该列非NULL值的个数。如果某列存在NULL两者的结果就会不同。我遇到过开发人员用COUNT(备注)去统计订单数结果因为部分订单备注是NULL报表少算了几行排查半天才找到原因。经验就是统计行数用COUNT(*)统计非空值用COUNT(具体列)不要混。SUM和AVG遇到NULL会自动忽略但如果全部是NULL结果就是NULL不是0。这时候要用ISNULL或COALESCE兜底后面专门讲。4.2 GROUP BY 的进阶玩法HAVING、ROLLUP 和 GROUPING SETSGROUP BY之后如果要过滤分组结果很多人会用错地方把过滤条件写在WHERE里试图过滤统计后的结果然后发现报错或者结果不对。WHERE是分组前的过滤HAVING是分组后的过滤这个顺序是SQL执行逻辑的核心。记住WHERE先执行GROUP BY再执行HAVING最后执行。举一个销售报表的例子找出下单次数超过20次的客户SELECT 客户ID, COUNT(*) AS 下单次数 FROM 订单表 WHERE 下单状态 已支付 -- 分组前过滤掉未支付订单 GROUP BY 客户ID HAVING COUNT(*) 20; -- 分组后再过滤次数这个SQL的执行顺序是先在订单表里筛掉未支付的然后按客户分组最后保留下单次数不低于20的客户。如果把这个条件误放到WHERESQL会直接报错因为COUNT(*)此时还不存在。ROLLUP和GROUPING SETS可能用得少一些但做分段汇总特别省事。比如按“年月”统计销售额同时要一个小计合计用ROLLUP一步就能出来SELECT YEAR(下单时间) AS 年份, MONTH(下单时间) AS 月份, SUM(金额) AS 销售额 FROM 订单表 GROUP BY ROLLUP(YEAR(下单时间), MONTH(下单时间));结果里会出现小计行年份有值、月份为NULL和总计行年份和月份都为NULL。配合GROUPING函数可以识别哪些行是汇总行这在生成报表时特别好用。4.3 去重统计的聚合写法COUNT(DISTINCT 列)热搜里反复出现“SQL语句去重”除了前面说的窗口函数方案聚合统计里还有个高频用法就是COUNT(DISTINCT 列)按列去重后再计数。-- 统计有多少个不同的客户下过单 SELECT COUNT(DISTINCT 客户ID) AS 下单客户数 FROM 订单表;这个写法简洁但从性能角度看并不友好。COUNT(DISTINCT 列)在大表上通常需要额外的排序或哈希操作数据量上去之后会很吃力。如果只是报表里偶尔用一下问题不大要是跑在千万级大表上建议考虑预处理成汇总表或者用近似算法SQL Server里可以用APPROX_COUNT_DISTINCT来换取性能。5. 条件逻辑与类型转换让SQL学会“判断”和“兜底”5.1 CASE WHEN万能的条件分支工具CASE WHEN是SQL里最重要的逻辑函数没有之一。它相当于编程语言里的if-else可以用来打标、分段、做映射。SELECT 订单号, 金额, CASE WHEN 金额 1000 THEN 大额订单 WHEN 金额 500 THEN 中等订单 ELSE 小额订单 END AS 订单等级 FROM 订单表;写CASE WHEN的时候有几个习惯建议养成。第一条件从上往下匹配命中即停所以先写范围大的条件会短路后面的条件。第二ELSE能写就写不写的话未匹配的行会返回NULL在报表里就被当成空值处理了。第三一个CASE表达式里的THEN返回类型要一致不能一会返回字符串一会返回数字。CASE WHEN还能配合聚合函数做“条件聚合”这是我特别推荐的一个技巧。比如要统计各渠道订单里大额订单的占比一条SQL就搞定SELECT 渠道, COUNT(*) AS 总订单数, SUM(CASE WHEN 金额 1000 THEN 1 ELSE 0 END) AS 大额订单数, AVG(CASE WHEN 金额 1000 THEN 1.0 ELSE 0 END) AS 大额占比 FROM 订单表 GROUP BY 渠道;这里最关键的是CASE WHEN和聚合函数的嵌套SUM里套CASE相当于先打标再汇总省去了子查询的麻烦。很多人不知道这个写法绕了很大的圈。5.2 空值处理ISNULL、COALESCE 的差异与选择数据库里的NULL是个幽灵值它不等于空字符串也不等于0。很多查询结果莫名其妙的“少了一行”都是因为WHERE 列 NULL这种错误写法导致的——NULL和任何值比较都返回未知必须用IS NULL判断。处理NULL的显示和计算SQL Server有ISNULL和COALESCE两个函数。ISNULL只能接收两个参数第一个为NULL就返回第二个COALESCE可以接收多个参数从左到右返回第一个非NULL的值。SELECT 客户名称, ISNULL(手机号, 未留联系方式) AS 联系方式, COALESCE(微信, 手机号, 邮箱, 无) AS 首选联系渠道 FROM 客户表;我给团队定的统一规范是能用COALESCE就不用ISNULL原因很简单——COALESCE是SQL标准迁移数据库不用改代码。而且ISNULL在SQL Server里还有个隐藏坑它的返回值类型以第一个参数为准如果第一个参数是INT第二个参数写了个字符串会触发隐式转换。5.3 类型转换CAST 与 CONVERT 的选择原则SQL Server提供了CAST和CONVERT两个转换函数。CAST是标准写法CONVERT是SQL Server扩展多一个可选的样式码参数。比如日期转字符串时CONVERT(VARCHAR(10), GETDATE(), 120)的120代表yyyy-MM-dd格式用起来很顺手。但类型转换最大的坑在“隐式转换”。如果某字段是VARCHAR你拿一个INT去和它比较SQL Server会尝试把字符串转成数字一旦字段里有非数字字符查询就会报错。更麻烦的是隐式转换会让索引失效导致全表扫描。我自己排查慢SQL时第一眼就找WHERE条件里的字段类型是否和外层参数一致这是最容易被忽略的性能杀手。经验法则在应用层尽量传和字段类型一致的值别依赖数据库帮你转。如果确实要转显式写CAST或CONVERT至少代码可读性会好很多也方便后续优化。6. 窗口函数排行榜、累计值、同环比的最佳解6.1 为什么窗口函数是“方法函数”里的高阶玩法窗口函数是在不改变结果集行数的情况下对每一行做跨行计算。说人话就是GROUP BY会把多行合并成一行而窗口函数保持明细行不变同时在旁边加一列“计算出来的值”。它解决的是“既要看到每一笔订单明细又要看到这笔订单在整体里的排名/占比/累计值”这类需求。SQL Server从2012版本开始完整支持窗口函数MySQL从8.0开始支持。如果你的数据库还停留在MySQL 5.7那窗口函数只能靠变量模拟非常痛苦。这也是很多老项目升级数据库的理由之一。窗口函数的基本语法是函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)。PARTITION BY负责分组ORDER BY负责组内排序。理解了这个骨架剩下的就是不同函数的微调。6.2 ROW_NUMBER、RANK、DENSE_RANK 三兄弟怎么区分面试和实际开发里都容易被问到的经典问题。先看一个具体例子假设表里有三个订单金额都是500SELECT 订单号, 金额, ROW_NUMBER() OVER (ORDER BY 金额 DESC) AS rn, -- 1, 2, 3 RANK() OVER (ORDER BY 金额 DESC) AS rk, -- 1, 1, 1 DENSE_RANK() OVER (ORDER BY 金额 DESC) AS drk -- 1, 1, 1 FROM 订单表;三个函数的区别在于并列时的处理方式函数并列时的排名下一个排名典型场景ROW_NUMBER强制分先后随机或按附加条件连续分页、去重取一条RANK排名相同跳过如1,1,3比赛名次DENSE_RANK排名相同连续如1,1,2榜单密度统计实际工作中分组取每组TopN是最常见的需求。比如按城市取销售额前三的门店SQL长这样SELECT 城市, 门店名称, 销售额 FROM ( SELECT 城市, 门店名称, 销售额, ROW_NUMBER() OVER (PARTITION BY 城市 ORDER BY 销售额 DESC) AS rn FROM 门店销售表 ) t WHERE rn 3;这个模式在我写的报表里出现频率极高强烈建议抄进自己的常用SQL片段里。6.3 累计值与同环比SUM OVER 和 LAG/LEAD窗口函数另一个大杀器是累计计算。比如统计每个月的累计销售额SELECT 月份, 月销售额, SUM(月销售额) OVER (ORDER BY 月份) AS 累计销售额 FROM 月度销售汇总表;SUM(...) OVER (ORDER BY 月份)会按月份递增累加这个逻辑在计算“累计到当月”的漏斗报表时非常方便不用再自连接或者子查询取区间了。同环比计算用LAG和LEAD最合适。LAG取上一行LEAD取下一行。月度环比的计算思路是把上一个月的销售额取到当前行然后直接算百分比SELECT 月份, 月销售额, LAG(月销售额) OVER (ORDER BY 月份) AS 上月销售额, ROUND( (月销售额 - LAG(月销售额) OVER (ORDER BY 月份)) * 100.0 / LAG(月销售额) OVER (ORDER BY 月份), 2 ) AS 环比增长率 FROM 月度销售汇总表;需要注意LAG在没有上一行时返回NULL环比增长率也是NULL这正好符合“第一期没有环比”的业务语义。但如果你用这个结果去做前端图表要把NULL处理成0或空字符串别让前端展示直接报错。窗口函数虽然强大也不是没有代价。PARTITION BY和ORDER BY都会触发额外的排序和内存占用在同一查询里叠加多个窗口函数时尤其明显。我的建议是能用一条窗口函数解决的别用两条如果报表确实需要多层窗口计算考虑拆成临时表或CTE分步处理至少排查问题的时候好定位。7. 函数虽好但别乱用性能边界与安全底线7.1 标量函数逐行执行隐藏的性能黑洞SQL Server里写自定义标量函数比如FUNCTION dbo.自定义函数()很方便但它有个隐藏的性能问题标量函数在查询中往往被逐行调用。如果你的结果集有100万行这个自定义函数就被执行100万次每次还要切换上下文查询速度可以慢到一个数量级。我在项目里遇到过真实案例某报表用了自定义函数做金额分档数据量才50万行却跑了快三分钟。去掉自定义函数、改写成CASE WHEN之后查询时间直接掉到3秒。这就是100倍的差距。结论能用内置函数解决的别写自定义函数能改写成CASE WHEN或联表解决的别用标量函数。如果实在要封装逻辑优先考虑用视图或INLINE TVF内联表值函数它们可以参与查询计划优化性能远好于标量函数。7.2 函数作用于索引列让索引失效的经典操作前面提到格式化和隐式转换会让索引失效这里展开说明。假设下单时间字段上建了索引下面这个查询就无法使用索引-- 不走索引函数包住了索引列 SELECT * FROM 订单表 WHERE YEAR(下单时间) 2024;因为数据库没法直接根据索引定位“2024年”必须先把每一行的下单时间都计算成年份再和2024比较这等于全表扫描。正确的写法是让索引列保持原样把条件改成范围查询-- 走索引把条件转成范围 SELECT * FROM 订单表 WHERE 下单时间 2024-01-01 AND 下单时间 2025-01-01;这条对比在慢SQL优化里属于必讲案例。排查慢SQL时我一般先EXPLAIN或看执行计划只要看到索引列被函数包裹第一件事就是重写条件。7.3 SQL注入的防御底线参数化查询才是正解热搜词里能看到“SQL注入”相关的内容说明这是绕不开的话题。SQL注入的原理是在拼接SQL字符串时破坏了原本的SQL语法让数据库执行了非预期的命令。防御的核心非常简单且绝对不要用字符串拼接SQL改用参数化查询。以Python为例错误写法# 危险直接拼接用户输入 sql SELECT * FROM 用户表 WHERE 用户名 用户名 AND 密码 密码 正确写法# 安全参数化查询 sql SELECT * FROM 用户表 WHERE 用户名 %s AND 密码 %s cursor.execute(sql, (用户名, 密码))参数化查询让数据库把用户输入当作“数据”而不是“SQL代码”从根本上杜绝了注入的可能。任何把外部输入直接拼进SQL字符串的做法无论加了什么过滤函数都不够安全。这一点在团队代码评审里应该作为一票否决项。7.4 慢SQL优化的常见思路先看执行计划再谈改写最后聊聊慢SQL优化这个主题和函数使用关系很大。很多慢查询不是数据库配置问题而是SQL本身写法不好。我的优化排查顺序是先EXPLAIN或打开执行计划看是否全表扫描、有没有用到索引。检查WHERE条件里的列有没有被函数包裹或者存在隐式类型转换。看有没有多余的排序、重复的子查询能改写为JOIN或窗口函数的就改写。分批处理大数据量操作避免一次性更新或删除百万行级数据。这套顺序在执行计划里看得清清楚楚。比如KEY那一列显示NULL基本就是没走索引Extra里出现Using filesort说明有额外排序成本。先读执行计划再动手改SQL比盲猜快太多了。写到这里常见的内置函数和常规用法已经覆盖得差不多了。说实话整理这篇笔记的过程我自己也重新梳理了一遍才发现平时写报表时很多“觉得应该有更省事写法”的瞬间其实都有对应的函数可以顺手解决。比如最初那个从备注里提取订单号的需求现在闭着眼睛就能写出CHARINDEX和SUBSTRING组合。这一篇先覆盖了字符串、日期、数值、聚合、条件判断和窗口函数下一篇我打算把存储过程、自定义函数和临时表这三个“方法级”内容展开写。如果你想自己练手建议找一张几万行的订单表把文里的每个例子都跑一遍重点体会第6章的窗口函数和第7章的索引对比——这两块在实际工作里最能帮你拉开和别人的差距。