MSSQL里那些算法怎么写才更顺手，分享下我平时用的技巧和思路

说到在MSSQL里写算法，很多人可能觉得这不是SQL该干的事，应该交给应用程序，但实际情况是，有时候数据量太大，来回传输不现实；或者就是需要在数据库层面完成一些复杂的逻辑计算，这时候在SQL里写算法就成了必备技能，我的核心思路是：把面向过程的算法思维，转换成基于集合的SQL思维。 别总想着用游标一行一行去处理,那玩意儿又慢又麻烦。

最核心的技巧：用自连接和窗口函数代替循环

很多算法，比如计算累计和、移动平均，或者找出连续出现的记录，本质上是需要看到数据的前后关系，以前用游标，现在用窗口函数,效率是天壤之别。

有个销售表，我想计算每个销售员每个月销售额的累计和，用游标的思路是：先按销售员、月份排序，然后一行一行读，读到同一个销售员就把销售额累加起来,这在SQL里会写得很痛苦。

用窗口函数就一行代码： SELECT 销售员, 销售月份, 销售额, SUM(销售额) OVER (PARTITION BY 销售员 ORDER BY 销售月份) AS 累计销售额 FROM 销售表 你看，PARTITION BY相当于分组循环，ORDER BY确定了累加的顺序，SUM() OVER就是在每个分组内按顺序做累加，这就是把循环算法转化成了集合操作，类似的，LAG()和LEAD()能轻松拿到前一行或后一行的数据，用来计算环比、同比或者判断连续状态非常方便，这是我用的最多的技巧,没有之一。

处理层次结构：递归CTE是神器

像部门树、菜单树、论坛评论的楼中楼这种层次结构的数据，你要写算法遍历它们，用循环会很复杂，MSSQL里的递归公用表表达式（CTE）就是专门干这个的。

我的思路是分两步：

1. 定位起点（Anchor Member）：先找到所有的根节点，比如顶级部门、主贴。
2. 递归下去（Recursive Member）：然后通过连接,一层一层地找到它们的子节点。

举个例子，查某个部门及其所有下级部门： WITH DepartmentCTE AS ( SELECT DeptID, DeptName, ParentID FROM Department WHERE DeptID = 1 -- 假设1是顶级部门ID UNION ALL SELECT d.DeptID, d.DeptName, d.ParentID FROM Department d INNER JOIN DepartmentCTE cte ON d.ParentID = cte.DeptID ) SELECT * FROM DepartmentCTE; 这个CTE会先查出ID为1的部门，然后union all所有ParentID等于1的部门，再union all所有ParentID等于上一步结果的部门...直到找不到为止，这就完成了一次深度优先的遍历，写的时候一定要注意递归的退出条件,不然就死循环了。

集合思维处理复杂匹配：比如分组排序

有一个经典问题：找出每个班级成绩最高的学生，用过程式思维会想：先循环班级，对每个班级循环学生，找出最高分，在SQL里，我们可以用窗口函数中的ROW_NUMBER()。

SELECT * FROM ( SELECT 学生, 班级, 成绩, ROW_NUMBER() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) as 排名 FROM 成绩表 ) t WHERE 排名 = 1 思路是：先通过PARTITION BY和ORDER BY在集合层面给每个班的学生从高到低打上排名这个标记，然后外层查询直接取排名为1的，这比用子查询WHERE 成绩 = (SELECT MAX(成绩) FROM ...)那种方式通常性能更好，也更灵活，比如你要取前三名，只要把条件改成WHERE 排名 <= 3就行了。

巧用临时表或表变量分解复杂算法

如果一个算法逻辑特别复杂，一步写出来SQL语句会又长又难懂还难调试，我的习惯是把它拆成几个有逻辑意义的步骤，每个步骤的结果存到临时表或表变量里，这样每一步都清晰,也方便检查中间结果对不对。

我要分析用户购买行为，可能要经历：1. 清洗无效数据；2. 计算每个用户的首次购买时间；3. 根据首次购买时间划分用户群；4. 统计不同用户群的复购率，我不会试图用一个超级复杂的查询完成所有事，而是会创建2-3个临时表，把第2步的结果存下来，再用这个结果去做第3步，代码的可读性和可维护性会大大提高，虽然可能有多步操作，但只要索引建得好,整体效率往往比一个臃肿的查询更高。

少用游标，如果非用不可，记得快进快出

我前面一直在说避免游标，但确实有极少数情况绕不开，比如需要逐行调用一个复杂的存储过程，万一真到了这一步,我的技巧是：

1. 选择正确的游标类型：默认的游标性能最差，尽量用FAST_FORWARD或LOCAL STATIC这类只进、只读的游标,它们开销小一些。
2. 把要处理的数据范围缩到最小：游标查询的SELECT语句一定要用WHERE条件把不必要的数据过滤掉,结果集越小越好。
3. 操作要快：循环体内的逻辑要尽可能高效，避免在循环内再做复杂的查询或计算，快进快出,减少单次循环时间。

总结一下我的思路： 别把SQL当成C#或Java来写，拿到一个算法问题，先别想循环和分支，先想数据怎么集合在一起，怎么排序，怎么分组，怎么打标记，窗口函数、CTE、Case When等高级语法就是你的武器，它们的本质都是对数据集合进行操作，临时表是你的草稿纸，用来分解难题，游标是最后的救命稻草，能不用就不用，坚持这样思考,你在MSSQL里写算法就会越来越顺手。