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


猜你喜欢
- select * from table where DATE_FORMAT(FROM_UNIXTIME(createtime),'%
- 印象中最早看老外个人网站就挺纳闷,怎么人家都没有www,这样也可以?经过不断尝试,我发现确实不录入www要快捷的多,但不清楚怎么能做到。几年
- 写在前面:本文是vue-手摸手教你使用vue-cli脚手架-详细步骤图文解析之后,又一篇关于vue-cli脚手架配置相关的文章,因为有些文章
- 一个XML文档如果符合一些基本的规范,那它就是结构规范的。XML格式有一套比HTML简单的解析规则,允许XML解析器不需要外部描述或了解数据
- 1. yum list installed | grep php 查看安装的php版本mod_php72w.x86_64 7.2.1-1.w
- 前言上一篇文章中有同学提到路由鉴权,由于时间关系没有写,本文将针对这一特性对 vue 和 react 做专门说明,希望同学看了以后能够受益匪
- 前言:这里再回顾一下函数的local空间,首先我们往global空间添加一个键值对相当于定义一个全局变量,那么如果往函数的local空间里面
- 用js封装一些常用的jquery方法 记录一下hasClass:判断是否有classfunction hasClass(ele, cls)
- 1.下载MySQL Community Server 5.7.16 安装好2.进去mysql安装目录的bin目录下,在文件地址栏输入cmd回
- 本文实例讲述了Go语言执行系统命令行命令的方法。分享给大家供大家参考。具体如下:执行Go代码时可以附加参数,包括要执行的命令和给命令的参数p
- 1. 前言随着互联网的发展和普及,网络安全问题越来越突出,网络在为用户提供越来越多服务的同时,也要面对各类越来越复杂的恶意攻击。SQL注入(
- 希腊Web 设计师Christos Chiotis 发表在 CssGlobe 的一篇文章,讲述了黄金分割率在 CSS 中的应用。黄金分割率是
- 使用现有的txt文本和图片,就可以用wordcloud包生成词云图。大致步骤是:1、读取txt文本并简单处理;2、读取图片,以用作背景;3、
- 前些日子在SmashingMagazine看到一篇关于CSS3新技术不错的文章,它详细介绍了CSS3的新特性和它的使用方法,它包括:浏览器专
- 题目描述从上到下按层打印二叉树,同一层结点从左至右输出。每一层输出一行。思路:1、把每层节点的val值用list存好2、把每层节点存好:①计
- 问题描述输入一串字符,由字母、数字和空格组成,长度 < 1000, 判断其中是否存在日期格式的数据。日期格式的数据具有如下的特征,连续
- 使用drop()方法删除pandas.DataFrame的行和列。在0.21.0版之前,请使用参数labels和axis指定行和列。从0.2
- 本文将讲述vue-cli+vux-scroller实现移动端的上拉加载功能:纠错声明:网上查阅资料看到很多人都将vux和vuex弄混,在这里
- 本文实例为大家分享了python sklearn分类算法模型调用的具体代码,供大家参考,具体内容如下实现对'NB',&nbs
- 1、终极方法:条件注释<!--[if lte IE 6]> 这段文字仅显示在 IE6及IE6以下版本。 <![endif]