jjzjj

SQL Server实现group_concat功能

小灯数据-脚本小王子 2024-01-05 原文

小灯数据版权所有，如转载请注明出处

https://blog.csdn.net/mysqltop/article/details/124252173

一、实现

#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的作用

我们使用STＵFF函数的目的是把第一个分隔符去掉。先看看效果：

上图可以看到，STＵFF函数把字符串“abcdefg”中的第一个字符“a”删除。

使用该函数我们可以很轻松的把上图得到的结果集去掉第一个逗号分隔符：

需要详细了解STＵFF函数可继续看该函数的语法，没兴趣的可以忽略。

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

Select　RegionID 
FROM #tmp A
Group by RegionID

　　这个sql各位看官都十分熟悉，已经没什么好说的了。

2.3.2、在select语句后面加上子查询

Select　RegionID,
　　　　(
　　　　　　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语句。

长按下图二维码，关注更多数据库实用技巧。

group_concatServerxffxff0cxff0sqlserver

有关SQL Server实现group_concat功能的更多相关文章

  1. ruby - 如何根据特征实现 FactoryGirl 的条件行为 - 2

    我有一个用户工厂。我希望默认情况下确认用户。但是鉴于unconfirmed特征，我不希望它们被确认。虽然我有一个基于实现细节而不是抽象的工作实现，但我想知道如何正确地做到这一点。factory:userdoafter(:create)do|user,evaluator|#unwantedimplementationdetailshereunlessFactoryGirl.factories[:user].defined_traits.map(&:name).include?(:unconfirmed)user.confirm!endendtrait:unconfirmeddoenden

  2. ruby-on-rails - Cucumber 是否只是 rspec 的包装器以帮助将测试组织成功能？ - 2

    只是想确保我理解了事情。据我目前收集到的信息，Cucumber只是一个“包装器”，或者是一种通过将事物分类为功能和步骤来组织测试的好方法，其中实际的单元测试处于步骤阶段。它允许您根据事物的工作方式组织您的测试。对吗？ 最佳答案 有点。它是一种组织测试的方式，但不仅如此。它的行为就像最初的Rails集成测试一样，但更易于使用。这里最大的好处是您的session在整个Scenario中保持透明。关于Cucumber的另一件事是您(应该)从使用您的代码的浏览器或客户端的角度进行测试。如果您愿意，您可以使用步骤来构建对象和设置状态，但通常您

  3. 华为OD机试用Python实现 -【明明的随机数】 2023Q1A - 2

    华为OD机试题本篇题目：明明的随机数题目输入描述输出描述：示例1输入输出说明代码编写思路最近更新的博客华为od2023|什么是华为od，od薪资待遇，od机试题清单华为OD机试真题大全，用Python解华为机试题|机试宝典【华为OD机试】全流程解析+经验分享,题型分享,防作弊指南华为o

  4. 基于C#实现简易绘图工具【100010177】 - 2

    C#实现简易绘图工具一.引言实验目的:通过制作窗体应用程序(C#画图软件),熟悉基本的窗体设计过程以及控件设计,事件处理等,熟悉使用C#的winform窗体进行绘图的基本步骤,对于面向对象编程有更加深刻的体会.Tutorial任务设计一个具有基本功能的画图软件**·包括简单的新建文件,保存,重新绘图等功能**·实现一些基本图形的绘制,包括铅笔和基本形状等,学习橡皮工具的创建**·设计一个合理舒适的UI界面**注明:你可能需要先了解一些关于winform窗体应用程序绘图的基本知识,以及关于GDI+类和结构的知识二.实验环境Windows系统下的visualstudio2017C#窗体应用程序三.

  5. MIMO-OFDM无线通信技术及MATLAB实现（1）无线信道：传播和衰落 - 2

     MIMO技术的优缺点优点通过下面三个增益来总体概括：阵列增益。阵列增益是指由于接收机通过对接收信号的相干合并而活得的平均SNR的提高。在发射机不知道信道信息的情况下，MIMO系统可以获得的阵列增益与接收天线数成正比复用增益。在采用空间复用方案的MIMO系统中，可以获得复用增益，即信道容量成倍增加。信道容量的增加与min(Nt,Nr)成正比分集增益。在采用空间分集方案的MIMO系统中，可以获得分集增益，即可靠性性能的改善。分集增益用独立衰落支路数来描述，即分集指数。在使用了空时编码的MIMO系统中，由于接收天线或发射天线之间的间距较远，可认为它们各自的大尺度衰落是相互独立的，因此分布式MIMO

  6. 【Java入门】使用Java实现文件夹的遍历 - 2

    遍历文件夹我们通常是使用递归进行操作，这种方式比较简单，也比较容易理解。本文为大家介绍另一种不使用递归的方式，由于没有使用递归，只用到了循环和集合，所以效率更高一些！一、使用递归遍历文件夹整体思路1、使用File封装初始目录，2、打印这个目录3、获取这个目录下所有的子文件和子目录的数组。4、遍历这个数组，取出每个File对象4-1、如果File是否是一个文件，打印4-2、否则就是一个目录，递归调用代码实现publicclassSearchFile{publicstaticvoidmain(String[]args){//初始目录Filedir=newFile("d:/Dev");Datebeg

  7. ruby - Arrays Sets 和 SortedSets 在 Ruby 中是如何实现的 - 2

    通常，数组被实现为内存块，集合被实现为HashMap，有序集合被实现为跳跃列表。在Ruby中也是如此吗？我正在尝试从性能和内存占用方面评估Ruby中不同容器的使用情况 最佳答案 数组是Ruby核心库的一部分。每个Ruby实现都有自己的数组实现。Ruby语言规范只规定了Ruby数组的行为，并没有规定任何特定的实现策略。它甚至没有指定任何会强制或至少建议特定实现策略的性能约束。然而，大多数Rubyist对数组的性能特征有一些期望，这会迫使不符合它们的实现变得默默无闻，因为实际上没有人会使用它:插入、前置或追加以及删除元素的最坏情况步骤复

  8. ruby - "public/protected/private"方法是如何实现的，我该如何模拟它？ - 2

    在ruby中，你可以这样做:classThingpublicdeff1puts"f1"endprivatedeff2puts"f2"endpublicdeff3puts"f3"endprivatedeff4puts"f4"endend现在f1和f3是公共(public)的，f2和f4是私有(private)的。内部发生了什么，允许您调用一个类方法，然后更改方法定义？我怎样才能实现相同的功能(表面上是创建我自己的java之类的注释)例如...classThingfundeff1puts"hey"endnotfundeff2puts"hey"endendfun和notfun将更改以下函数定

  9. ruby-on-rails - rails group by 和 order by column - 2

    在我的Controller中，我得到了按类别分组的所有Extras:defindex@categories=Extra.all.group_by(&:category)end结果类似于哈希数组:{#=>[#,#=>[#,#]}我想按类别“排序”列而不是id排序，它应该如下所示:{#=>[#,#=>[#,#]}当我尝试时:defindex@categories=Extra.all.group_by(&:category).sort_by{|s|s[:sort]}end我得到“没有将符号隐式转换为整数”。那是因为我在“sort_by”中使用了一个符号吗？ 最佳答

  10. ruby-on-rails - rails 功能测试 - 2

    在Rails自动生成的功能测试(test/functional/products_controller_test.rb)中，我看到以下代码:classProductsControllerTest我的问题是:方法调用products()在哪里/如何定义？products(:one)到底是什么意思？看代码，大概意思是“创建一个产品”，但是它是如何工作的呢？注意我是Ruby/Rails的新手，如果这些是微不足道的问题，我深表歉意。 最佳答案 如果您查看test/fixtures文件夹，您会看到一个products.yml文件。这是在您创建

随机推荐