1. 需求拆解与方案选型思路做SQL开发这些年遇到最频繁的“小需求”之一就是多行数据合并成一个字段。前两天还有同事拿着报表需求来找我说要把一个客户名下的所有订单号拼到一行里用逗号隔开方便放进Excel做后续核对。我一听就乐了这活儿看着简单但真要把每种数据库的写法、边界条件和性能坑都说明白能写一篇长文。说真的这个需求背后的本质是什么就是把一对多关系里的“多”压缩成“一”用分隔符把多个值塞进同一个字符串字段。业务上最常见的使用场景包括订单明细汇总、标签合并、类别归类、导入导出前的数据预处理。凡是需要给业务人员做“一眼能看懂”的明细清单时这个操作几乎绕不开。技术方向上不同数据库给出的方案差别很大。SQL Server靠FOR XML PATH配合STUFF实现MySQL有专门的GROUP_CONCATOracle用LISTAGGPostgreSQL则是STRING_AGG。还有个通用的办法是写自定义聚合函数或用窗口函数拼字符串。这篇文章会把常用的几种方案都拆开讲清楚还会聊一聊我实际踩过的坑比如拼接出来的内容长了被截断、排序失效、XML转义把你坑哭之类的样样都是真实工作中会遇到的问题。适合看这篇文章的朋友包括日常写SQL的业务分析师、刚转行的数据分析师、后端开发工程师以及所有需要在报表或数据导出里做“多行合并”的人。我会尽量把原理和实操都覆盖到不管你是用SQL Server还是MySQL应该都能找到能直接拿去用的写法。1.1 什么业务场景会用到多行合并先说说我遇到的几个真实场景方便你对号入座。第一个场景是销售明细归集。订单主表和订单明细表是一对多的关系老板想看每个客户买了哪些产品如果一个客户买过五个产品传统查询出来就是五行老板说看不清你就得把这五个产品拼到一行里。第二个场景是标签聚合。用户表和一个中间关联表关联一个用户有多个标签你需要把标签拼到一个字段里方便下游系统直接取用。第三个场景是数据导出前的加工。很多业务系统导出的Excel不支持一对多结构你需要在SQL层面先把数据压平拼成一个带分隔符的字段导出去之后再由Excel的“分列”功能拆开处理。第四个场景是日志或轨迹拼接。比如一个工单经过了多个审批节点你需要把这些节点的审批意见和操作时间拼成一个完整的流程链条放进一个字段里展示。这些场景的共同点都是底表是多行的目标输出是单行单字段的中间需要聚合字符串。理解了这个需求结构再去看各种方案的原理就顺了。1.2 主流方案与适用数据库盘点我把市面上主流的实现方式做一个横向对比先让大家心里有个谱。后面再逐个展开。数据库推荐方案核心函数/语法版本要求SQL ServerSTUFF FOR XML PATHFOR XML PATH()2005MySQLGROUP_CONCATGROUP_CONCAT(字段 SEPARATOR )4.1OracleLISTAGGLISTAGG(字段 ) WITHIN GROUP (ORDER BY 列)11.2PostgreSQLSTRING_AGGSTRING_AGG(字段 ORDER BY 列)9.0部分数据库自定义聚合函数自己写CLR或存储过程根据版本选型的逻辑很明确优先用数据库自带的字符串聚合函数这是性能最好、代码最简洁的方案。只有当版本太老、不支持内置函数时才考虑用递归CTE、游标或者自写聚合函数去模拟。这里有个容易被忽视的点即使是同一类方案不同数据库的用法差异也很大。比如SQL Server的FOR XML PATH本质上不是为拼接设计的而是利用XML序列化的副作用来实现所以它的转义行为会让你踩坑。MySQL的GROUP_CONCAT虽然好用但有默认长度限制。Oracle的LISTAGG在12c之前有4000字符的硬上限超了直接报错。这些都是要提前知道的。2. 核心实现四大数据库的拼接方案拆解市面上真正让人头疼的从来不是“能不能拼”而是“怎么拼得对”。这一节我会把SQL Server、MySQL、Oracle、PostgreSQL四种数据库的完整写法写出来并且把每个写法背后的原理和容易出错的地方讲透。2.1 SQL ServerSTUFF FOR XML PATH组合拳SQL Server没有像MySQL那样直接提供GROUP_CONCAT所以最流行、最通用的做法是用STUFF配合FOR XML PATH。看一段最经典的示例代码SELECT t.CustomerId, STUFF(( SELECT OrderNo FROM SalesOrderDetail AS o WHERE o.CustomerId t.CustomerId FOR XML PATH() ), 1, 1, ) AS OrderNos FROM SalesOrderHeader AS t GROUP BY t.CustomerId;这段代码的思路是先通过子查询按CustomerId把该客户的所有订单号查出来FOR XML PATH()会将行结果拼接成一段XML字符串分隔符我用的是 OrderNo也就是每个订单编号前面加一个逗号。拼完的结果会多出第一个逗号所以外层套一个STUFF从第一个字符开始替换掉就得到了干净的结果。很多人第一次看到FOR XML PATH都会被吓到觉得这是不是太绕了。其实原理不复杂FOR XML PATH本身是用来将查询结果输出为XML格式的当你指定路径为空字符串时XML的标签会消失只留下节点内容于是每一行就只剩下你拼进去的文本。SQL Server在生成XML时会自动对特殊字符做转义比如会变成amp;会变成lt;导致拼接结果里出现乱码。这是这个方案最典型的坑。处理转义问题的方法也不复杂可以在拼的时候把特殊字符先替换掉。我常用的写法是SELECT t.CustomerId, STUFF(( SELECT REPLACE(REPLACE(REPLACE(OrderNo, , and), , [lt]), , [gt]) FROM SalesOrderDetail AS o WHERE o.CustomerId t.CustomerId FOR XML PATH() ), 1, 1, ) AS OrderNos FROM SalesOrderHeader AS t GROUP BY t.CustomerId;这段代码比之前多做了三个REPLACE把替换成and把尖括号替换成方括号形式。实际项目中如果拼接内容来自用户输入强烈建议加上这层处理。如果拼接内容只是数字、日期和普通文本一般可以不用管但我自己会统一处理免得哪天出现脏数据时排查起来麻烦。2.2 MySQLGROUP_CONCAT的便捷与陷阱MySQL的GROUP_CONCAT函数是销售口径下最简单好用的方案。语法如下SELECT CustomerId, GROUP_CONCAT(OrderNo SEPARATOR ) AS OrderNos FROM SalesOrderDetail GROUP BY CustomerId;这行SQL就能完成多行到单字段的合并。如果后续要对结果排序GROUP_CONCAT内部支持ORDER BY子句SELECT CustomerId, GROUP_CONCAT(OrderNo ORDER BY OrderDate DESC SEPARATOR ) AS OrderNos FROM SalesOrderDetail GROUP BY CustomerId;需要注意两个细节。第一默认情况下GROUP_CONCAT的最大长度是1024个字符这是由系统变量group_concat_max_len控制的。如果拼接的字符串超过这个长度后面的内容会被静默截断不会报错很容易造成数据不完整。我遇到过不止一次因为这个问题导致报销单号丢失的情况排查了很久才找到真凶。解决办法是修改会话变量把上限调大SET SESSION group_concat_max_len 102400;建议在跑汇总脚本之前先执行这行语句。如果是在Java、Python等程序里调用可以在连接初始化时执行确保整个会话都生效。对于数据量特别大、拼接长度超过几十万字符的情况要评估是否应该放在应用层去拼不然SQL返回的结果集太大内存压力也会上来。第二GROUP_CONCAT默认会忽略值为NULL的行这个特性大多数时候是好事但如果你拼接的是像“备注”这类可空字段要注意合并结果里不会出现空字符串业务侧看到的结果可能是不对齐的。如果需要保留空值位置可以在查询里先用IFNULL把NULL转成某个占位符再参与拼接。去重也是高频需求GROUP_CONCAT支持DISTINCT用法很直接SELECT CustomerId, GROUP_CONCAT(DISTINCT CategoryName SEPARATOR ) AS Categories FROM ProductCategoryMap GROUP BY CustomerId;这个写法在标签去重场景里简直是标配我基本每天都用。2.3 OracleLISTAGG的优雅与上限Oracle的LISTAGG是我觉得最直观的聚合函数因为它自带排序能力语法也干净SELECT CustomerId, LISTAGG(OrderNo, ) WITHIN GROUP (ORDER BY OrderDate) AS OrderNos FROM SalesOrderDetail GROUP BY CustomerId;WITHIN GROUP (ORDER BY OrderDate)这一段就完成了排序不需要像MySQL那样依赖子查询。对于11.2及以上版本这是首选方案。但是LISTAGG有个著名的限制在19c之前拼接结果的总长度不能超过4000字节。一旦超过Oracle会直接抛出ORA-01489: result of string concatenation is too long错误。遇到这个报错时常见的处理方式有三种。第一种是从业务层面限制长度只保留排序后前N条记录比如只拼最近5个订单SELECT CustomerId, LISTAGG(OrderNo, ) WITHIN GROUP (ORDER BY OrderDate) KEEP (DENSE_RANK FIRST ORDER BY OrderDate DESC) AS OrderNos FROM SalesOrderDetail GROUP BY CustomerId;这种写法比较复杂不容易一眼看懂。更简单的方式是套子查询先按业务规则过滤比如用ROW_NUMBER()取前几条SELECT CustomerId, LISTAGG(OrderNo, ) WITHIN GROUP (ORDER BY OrderDate) AS OrderNos FROM ( SELECT CustomerId, OrderNo, OrderDate, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn FROM SalesOrderDetail ) WHERE rn 5 GROUP BY CustomerId;第二种是把LISTAGG的结果转为CLOB。Oracle 12c以后推出了LISTAGG ... ON OVERFLOW子句可以指定溢出后截断或者返回CLOB类型。但实际生产中我用得比较少因为转成CLOB后很多下游客户端展示和导出都会变麻烦。第三种是把拼接逻辑挪到应用层用Java或Python读取明细数据后再自行拼装。这个方案最灵活也最稳妥性能上通常更好因为数据库的资源往往是最宝贵的。2.4 PostgreSQLSTRING_AGG与窗口函数的配合PostgreSQL的STRING_AGG在用法上和MySQL的GROUP_CONCAT非常接近而且没有默认长度限制用起来更省心SELECT CustomerId, STRING_AGG(OrderNo, ORDER BY OrderDate) AS OrderNos FROM SalesOrderDetail GROUP BY CustomerId;注意ORDER BY是写在聚合函数内部的这一点和Oracle的WITHIN GROUP很像比MySQL的写法更规范。如果你要去重还能加DISTINCTSELECT CustomerId, STRING_AGG(DISTINCT CategoryName, ORDER BY CategoryName) AS Categories FROM ProductCategoryMap GROUP BY CustomerId;另外PostgreSQL有一个其他数据库不太常见的神操作可以用FILTER子句做条件聚合。比如我只拼接金额大于100的订单SELECT CustomerId, STRING_AGG(OrderNo, ORDER BY OrderDate) FILTER (WHERE Amount 100) AS OrderNos FROM SalesOrderDetail GROUP BY CustomerId;FILTER比把条件拼在WHERE里更灵活因为它可以针对不同字段使用不同的过滤条件而且不会影响其他聚合函数的计算范围。这个特性在做复杂报表时非常实用。3. 进阶玩法分隔符、排序、去重与性能优化会了基础写法只是入门现实业务里的需求没那么乖巧。这一节专门讨论分隔符怎么选、排序怎么稳定、去重怎么做以及在大数据量场景下如何写出性能可控的合并SQL。3.1 分隔符怎么选才不踩坑很多新人在这方面吃过亏。分隔符选得不好后面业务解析的时候就会出错。选分隔符的第一原则是不要让分隔符出现在业务数据本身里。比如你拼接的是订单编号而订单编号可能是纯数字或字母逗号相对安全。但如果你拼接的是地址、备注、产品描述这类自由文本里面很可能夹杂着逗号、顿号、分号那再用逗号做分隔符就是给自己埋雷。我常用的方案是拼接纯编码类的字段订单号、用户ID、手机号用英文逗号或竖线|。拼接名称类的字段产品名、部门名、标签名用顿号、或分号;。拼接地址、描述等长文本优先考虑竖线||或自定义的罕见分隔符比如###。选好分隔符之后还有一个容易忽略的点SQL拼接只是中间环节数据最终要到Excel、CSV或者前端展示。如果目标格式本身有转义规则比如CSV中用逗号作为列分隔符那你拼出来的内容含逗号会让CSV解析错乱。这时要么在拼接时就选一个CSV不冲突的分隔符要么在导出层再做一次转义。我还在项目中遇到过一个很麻烦的情况同一个字段在A系统拼接时用了逗号到B系统读取时却按分号切分。源头没有统一规范导致下游每次都要做兼容处理。所以在设计之初就要把“谁来消费这个字段”问清楚不要等上线了才补救。3.2 排序、去重、空值过滤的细节点排序是很多人会忽略的环节。你得想清楚拼接出来的顺序到底有没有意义。比如拼订单号通常希望按订单日期排序拼审批记录通常希望按审批时间排序。不同的数据库对聚合内部的排序支持力度不一样需要分情况处理。以MySQL为例GROUP_CONCAT内部虽然支持ORDER BY但如果你在子查询里先排好序外层再聚合顺序也是不稳定的因为优化器可能改写执行计划。最可靠的写法是直接在GROUP_CONCAT内部写ORDER BY。同理SQL Server的FOR XML PATH方案里子查询的ORDER BY是可以生效的但前提是子查询里有明确的排序条件否则执行计划可能改变行的返回顺序。去重也是一个高频需求。GROUP_CONCAT(DISTINCT ...)和STRING_AGG(DISTINCT ...)都能直接去重LISTAGG也有DISTINCT支持SQL Server的FOR XML PATH没有内置去重需要在子查询里先SELECT DISTINCT。空值方面GROUP_CONCAT、STRING_AGG、LISTAGG默认都会跳过NULL。如果业务要求NULL显示为占位符需要提前用COALESCE处理。比如拼备注字段可以把NULL转成无确保每一行的拼接结果里都有对应位置的内容。还有一点容易被忽略拼接的字段本身可能存在前后空格或中间换行符。比如从外部系统导入的订单号可能带着\r\n拼出来的结果在Excel里显示会异常换行很丑。我在写清洗脚本时都会加上一层TRIM和REPLACE把换行符、回车符提前清理掉再做聚合。3.3 性能问题与慢SQL优化建议多行合并本质上是分组聚合操作最怕的是在数据量很大的表上对每一行都执行一次子查询。比如SQL Server的STUFF FOR XML PATH方案如果写成相关子查询执行计划可能退化为逐行扫描性能会非常难看。优化的核心思路只有两条一是减少扫描行数二是让聚合在索引覆盖的范围内完成。第一条尽量先用WHERE把数据范围缩小再进行拼接。比如只拼当月的订单在底层就过滤掉历史数据能极大减少聚合的量。第二条确保连接键和分组键上有索引。无论用哪种方案最终都要按CustomerId分组那么CustomerId上必须有索引。如果SQL Server的相关子查询要通过CustomerId关联明细表那明细表的CustomerId索引必不可少。同样的MySQL的GROUP_CONCAT在GROUP BY CustomerId时如果CustomerId没有索引临时表排序的开销会非常大。第三条尽量用内置聚合函数而不要用游标或递归CTE去拼字符串。游标的性能有多差不用多说递归CTE在数据量大时也会产生巨大的中间结果集。内置函数经过数据库优化器大量优化虽然不保证最快但通常比手写方案稳定。对于非常大的数据集还有一个思路是分而治之先按业务维度把大表拆成小段分别拼接完再合并。比如按分区当月处理一遍再处理历史数据最后把结果合并。虽然SQL层面看起来复杂了一点但整体执行时间往往更可控。在我实际优化过的案例中有一个4000万行级别的订单明细表原来用相关子查询FOR XML PATH拼接需要跑将近40分钟后来改成先按客户维度把结果放到临时表再一次性拼接执行时间直接从40分钟降到3分钟以内。核心变化就是减少了重复子查询的次数用临时表先做一次宽表化再从宽表做字符串聚合。这个思路值得大家借鉴。4. 踩坑记录与排查技巧写SQL这么多年围绕多行合并这个简单操作翻过的车可不算少。有些问题是因为数据库版本的差异导致的有些是数据质量问题还有些纯粹是我自己没看清需求。这一节把典型的坑和排查思路整理成一个速查表再补上我的一些实操心得。4.1 常见问题速查表问题现象可能原因排查与解决拼接结果被截断MySQL的group_concat_max_len太小或Oracle的LISTAGG超过4000字节调大group_concat_max_len改用CLOB或应用层拼接结果中出现、等乱码SQL Server FOR XML PATH自动转义拼接前对特殊字符做REPLACE替换拼接顺序错乱子查询排序被优化器改写或没有稳定的排序字段在聚合函数内部指定ORDER BY确保排序字段唯一稳定拼接结果有重复值明细表存在一对多关联产生的重复行先DISTINCT子查询或使用DISTINCT聚合性能极慢相关子查询逐行执行缺乏索引用临时表宽表化确保连接键有索引先缩小数据范围拼接结果包含换行符/空格源数据存在脏数据聚合前用TRIM和REPLACE清理\r\n、\t等字符Oracle报ORA-01489LISTAGG结果超过4000字节限制输出条数或转换为CLOB或改应用层拼接空值被跳过导致结果“缺位”聚合函数默认忽略NULL值用COALESCE/IFNULL提前填充占位符这张表看着简单但几乎覆盖了我工作里遇到的九成问题。每次排查的时候我会按“数据本身有没有脏值—SQL写法对不对—函数限制有没有超—性能和索引是不是瓶颈”的顺序去定位。这样做效率最高不会东一榔头西一棒子。4.2 留个心眼这些坑我也踩过先说一个印象最深的翻车案例。有一次我用SQL Server的FOR XML PATH拼接订单号结果发现拼出来的字符串里出现了amp;怎么都看着别扭。刚开始以为是数据有问题查了半天才发现是FOR XML PATH在生成XML时会把转义。那次之后我养成了习惯只要用FOR XML PATH做拼接不管字段类型是什么都会检查一遍特殊字符并做替换。这个习惯一直保留到现在。再说一个MySQL的坑。我负责过一个数据量比较大的标签系统每天生成用户标签汇总标签数量多、长后来发现导出结果有时候会少几个标签。查了一遍业务逻辑发现没问题最后定位到是group_concat_max_len默认1024导致尾部被截断。从那儿以后在写所有GROUP_CONCAT相关脚本前我都会在SQL里加上SET SESSION group_concat_max_len1024000;宁可多设一点也不能让它悄悄截断。关于排序我还吃过一次闷亏。当时在MySQL里用子查询先按时间倒序排好再用GROUP_CONCAT拼接希望得到倒序的结果结果实际输出是乱的。原因是GROUP_CONCAT内部有自己的排序机制外部子查询的ORDER BY在优化阶段就被忽略了。后来改成在GROUP_CONCAT内部直接写ORDER BY问题才解决。这些坑单独看都很小但组合在一起会浪费不少时间。我把它们写出来的目的就是希望大家能直接跳过这些弯路。说实话SQL多行合并看网上教程一抓一大把但真正能用的、能应对生产环境复杂情况的干货还真得靠自己踩过坑之后才总结得出来。这个内容后续还可以继续扩展比如加上“把合并结果再拆回多行”的逆操作或者聊聊在ETL工具里怎么处理类似的聚合逻辑。反正思路是通用的多花点时间把原理吃透后面用起来才顺手。
