SQL Server实现group_concat功能的详细实例
作者:小灯数据-脚本小王子 时间:2024-01-20 11:15:47
一、实现
#tmp表内容如下:
实现group_concat的sql语句为:
Select
RegionID,
STUFF(
(
SELECT ',' + T.c1
FROM #tmp T
WHERE A.regionid = T.regionid
FOR XML PATH('')
), 1, 1, ''
) as group_concat
FROM #tmp A
Group by RegionID
实现效果如下:
二、原理分析
2.1、FOR XML PATH的作用
FOR XML PATH 的作用是将查询结果集以XML形式展现,将多行的结果,展示在同一行,例如:
select c1 from #tmp where RegionID = 41653
其结果集如下:
select c1 from #tmp where RegionID = 41653 FOR XML PATH('')
当sql语句加上 FOR XML PATH('') 后,其结果集输出是:
具体输出的字符如下:
<c1>30.326809</c1><c1>30.327982</c1><c1>30.347933</c1><c1>30.388104</c1><c1>30.392830</c1><c1>30.367931</c1><c1>30.368052</c1><c1>30.367842</c1><c1>30.357318</c1><c1>30.357349</c1><c1>30.357349</c1>
通过字符拼接后可以把xml信息清除,并以指定的字符进行分割:
select ',' + c1 from #tmp where RegionID = 41653 FOR XML PATH('')
此时已基本达到group_concat的效果,但第一个字符串有分隔符需要去掉。
2.2、STUFF函数
2.2.1、STUFF函数在本SQL的作用
我们使用STUFF函数的目的是把第一个分隔符去掉。先看看效果:
上图可以看到,STUFF函数把字符串“abcdefg”中的第一个字符“a”删除。
使用该函数我们可以很轻松的把上图得到的结果集去掉第一个逗号分隔符:
需要详细了解STUFF函数可继续看该函数的语法,没兴趣的可以忽略。
2.2.2、STUFF函数语法
STUFF函数的作用是将字符串插入到另一个字符串中。它从第一个字符串的开始位置删除指定长度的字符,然后将第二个字符串插入到第一个字符串的开始位置。其语法为:
STUFF(character_expression , start , length , replaceWith_expression)
character_expression:字符数据的表达式,可以是常量、变量,也可以是字符列或二进制数据列。
start:一个整数值(从1开始),指定删除和插入的开始位置。start的类型可以是bigint。
• 如果 start 为负或为零,则返回空字符串。
• 如果 start 的长度大于第一个 character_expression,则返回空字符串。
length:一个整数,指定要删除的字符数。length的类型可以是 bigint。
• 如果 length 为负,则返回空字符串。
• 如果 length 的长度大于character_expression,则最多可以删除到character_expression 中的最后一个字符。
• 如果 length 为零,则不删除字符直接在指定位置插入内容。
replaceWith_expression:字符数据的表达式,可以是常量、变量,也可以是字符列或二进制数据列。此表达式从 start 开始替换 length 个字符的character_expression。
• 如果 replaceWith_expression 为 NULL,则在不插入任何内容的情况下删除字符。
2.3、sql语分分析
2.3.1、一个简单的group by
SelectRegionID
FROM #tmp A
Group by RegionID
这个sql各位看官都十分熟悉,已经没什么好说的了。
2.3.2、在select语句后面加上子查询
SelectRegionID,
(
SELECT ',' + T.c1
FROM #tmp T
WHERE A.regionid = T.regionid
FOR XML PATH('')
)
FROM #tmp A
Group by RegionID
在上述简单的group by语句基础上加入一个select里的子查询,其结果如下:
在该子查询中,当外层的group by返回结果集中的第一行RegionID为41653时,这个值被子查询的where条件所使用,相当于:
SELECT ',' + T.c1
FROM #tmp T
WHERE T.regionid = 41653
FOR XML PATH('')
因为FOR XML PATH把多行记录打平成一条记录,因此此时的返回结果为:
接着第group by返回结果集中的第二行45761传入该子查询,依次类似上面描述的执行,直到所有外层的值遍历完成。
2.3.3、去掉子查询结果集的第一个分隔符
Select
RegionID,
STUFF(
(
SELECT ',' + T.c1
FROM #tmp T
WHERE A.regionid = T.regionid
FOR XML PATH('')
), 1, 1, ''
) as group_concat
FROM #tmp A
Group by RegionID
利用STUFF函数,去掉了第一个逗号,完成了最终sql语句。
来源:https://blog.csdn.net/mysqltop/article/details/124252173
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python使用正则表达式提取网页URL的方法
彻底解决页面文字编码乱码问题
django 基于中间件实现限制ip频繁访问过程详解
使用SqlBulkCopy时应注意Sqlserver表中使用缺省值的列
如何更改mysql命令下提示信息
Python编辑器Pycharm安装配置超详细教程
![](https://img.aspxhome.com/file/2023/2/101062_0s.jpg)
Python基于百度API识别并提取图片中文字
![](https://img.aspxhome.com/file/2023/6/84946_0s.jpg)
怎么用Python识别手势数字
![](https://img.aspxhome.com/file/2023/1/88901_0s.png)
Python中三个不可思议的返回功能分享
bootstrap table 服务器端分页例子分享
ASP调用数据库常见错误的解决
Python入门教程之运算符重载详解
安装pytorch报错torch.cuda.is_available()=false问题的解决过程
![](https://img.aspxhome.com/file/2023/8/124308_0s.jpg)
MySQL触发器简单用法示例
不同版本中Python matplotlib.pyplot.draw()界面绘制异常问题的解决
python的继承详解
![](https://img.aspxhome.com/file/2023/2/123572_0s.png)
python 提取tuple类型值中json格式的key值方法
laravel 解决crontab不执行的问题
![](https://img.aspxhome.com/file/2023/5/75875_0s.jpg)