主流数据库（SQL Server、Mysql、Oracle）通过sql实现多行数据合为一行

一、SQL Server

1、方法一：使用 STUFF 和 FOR XML PATH 进行多行合并成一行

（1）FOR XML PATH用法

FOR XML

是 SQL Server 提供的一种功能，允许您将查询结果转换为 XML 格式。

PATH

模式则是其中一种灵活的方式来构造自定义的XML结构。

1、基本字符串连接：当您想从单列中提取所有行的数据并连接成一个字符串列表时，可以使用

FOR XML PATH('')

。

例如，假设有一个包含开发任务名称的

rwmc

列：

SELECT 
rwmc +','FROM table_name
FOR XML PATH('')

这段sql执行的结果将会返回一个XML字符串，其中每个任务名称后面跟着逗号，所有名称连接在一起。如下：

在这里插入图片描述

2、指定元素标签：若要将每个值包装在特定的XML元素内，您可以指定元素名称：

SELECT 
rwmc as'rwmcName'FROM table_name
FOR XML PATH('rwmc')

这将返回每个部门名称都在

<rwmcName>

元素内的XML结构。

在这里插入图片描述

3、创建嵌套结构：若要创建更复杂的嵌套结构，可以结合

AS

关键字和 XPath 表达式：

SELECT zyap,(SELECT 
        rwmc 
        FROM table_name as d
        FOR XML PATH('rwmc'),TYPE)FROM table_name AS e
FOR XML PATH('rwmcName'), ROOT('rwmcNames');

上述查询会创建一个XML文档，其中包含一个名为

rwmcNames

的根元素，每个任务是一个

rwmcName

元素，

zyap

是一个属性，而每个r任务名称则嵌套在

rwmc

元素中。

在这里插入图片描述

4、消除尾部逗号：如果在连接字符串时不需要末尾的分隔符（如逗号），通常会配合

STUFF()

和

SUBSTRING()

函数去除最后一个字符：

SELECT STUFF((SELECT','+rwmc
FROM table_name
WHERE mainid='03'FOR XML PATH('')),1,1,'')AS UnitsList;

在这里插入图片描述

5、类型指示符：

TYPE：返回的结果是XML数据类型，而不是字符串。
ELEMENTS：在PATH模式下强制所有标量值成为元素，而不是属性。

6、注意事项

在 FOR XML PATH 中，空格和特殊字符可能会被转义，如果需要原样输出文本，可以使用 TEXTPATH 或者设置 OPTION (QUOTE_IDENTIFIER OFF)。
对于复杂层次的XML构建，或者需要完全控制XML结构的情况，可能需要结合 FOR XML EXPLICIT 使用。

（2）STUFF 用法

STUFF()

函数在 SQL Server 中主要用于对字符串操作，它能实现两个主要功能：

删除字符串中的指定部分字符。
插入新的字符序列到原始字符串中的指定位置。

以下是

STUFF()

函数的基本语法和用法：

STUFF ( character_expression,start, length, add_string )

character_expression: 这是要进行操作的原始字符串表达式。它可以是常量、变量或列名等。
start: 此参数表示从原始字符串的哪个位置开始删除字符。这个位置是从1开始计数的，即第一个字符的位置是1。
length: 指定要从 start 位置开始删除多少个字符。如果 length 为0，则不删除任何字符，仅插入 add_string。
add_string: 要插入到原始字符串中的新字符序列。在删除了 start 位置开始的 length 个字符后，这个字符串将被插入到指定位置。

-- 示例1：删除并替换字符串中的部分内容DECLARE@originalString NVARCHAR(100)='Hello, World!';SELECT STUFF(@originalString,7,6,'there');-- 结果：'Hello, there!'-- 此例中，从第7个位置开始删除了6个字符（'World'），然后插入了'there'。-- 示例2：简单插入字符串DECLARE@anotherStringVARCHAR(50)='SQLServer';SELECT STUFF(@anotherString,7,0,'2019 ');-- 结果：'SQL2019 Server'-- 此例中，没有删除任何字符，而是在第7个位置插入了'2019 '。-- 示例3：在具有多个记录的表中使用STUFF进行字符串拼接SELECT 
    ID, 
    STUFF((SELECT', '+ AnotherColumn
        FROM YourTable AS YT2
        WHERE YT1.ID = YT2.ID -- 确保只拼接同一ID下的记录FOR XML PATH ('')),1,2,'')AS ConcatenatedValues
FROM YourTable AS YT1
GROUPBY ID;-- 这个例子中，STUFF与FOR XML PATH结合使用来拼接同一个ID下AnotherColumn列的所有值，以逗号+空格分隔，并移除了开头产生的额外的', '。

（3）实现多行数据合并为一行

下面的sql可以直接使用

SELECT STUFF((SELECT','+column_name --这里替换为你的列名FROM your_table --这里替换为你的表名--这里添加你的查询条件FOR XML PATH('')),1,1,'')AS UnitsList;

方法二：使用

STRING_AGG

函数

（1）STRING_AGG用法

STRING_AGG()

是一个在 SQL 中用于将多行数据合并成一列字符串的聚合函数，在 SQL Server 2017 及更高版本中，

STRING_AGG()

函数的基本用法如下：

STRING_AGG ( expression, separator )

expression: 这是你想要连接起来的列或表达式的名称，或者是你要拼接的字符串字面量。
separator: 这是在连接相邻字符串之间的分隔符，可以是任何字符或字符串。

（2）实现多行数据合并为一行

例如，假设有一个包含开发任务名称的

rwmc

列，你想把所有任务名称连接起来，中间用逗号分隔：

SELECT STRING_AGG(rwmc,', ')AS rwmc
FROM table_name
GROUPBY SomeGroupingColumn;-- 如果需要按某个字段分组,如果你不需要分组，直接将所有行连接起来，则可以省略 GROUP BY 子句。

在这里插入图片描述

二、Oracle

1、方法一：使用

LISTAGG

函数

LISTAGG

是Oracle从11g Release 2版本开始提供的内置聚合函数，它可以方便地将多行数据按指定分隔符串联起来：

SELECT 
LISTAGG(value_column,',')WITHINGROUP(ORDERBY value_column)AS merged_column
FROM your_table

在这里插入图片描述

2、方法二：使用

WM_CONCAT

函数

WM_CONCAT

是Oracle的一个非标准函数，曾经在早期版本中非常流行，但是在官方文档中并未正式支持。尽管如此，在某些旧版本的Oracle数据库中仍可找到这个函数并使用它来合并数据：

SELECT
WM_CONCAT(value_column) AS merged_column
FROM your_table

在这里插入图片描述

3、方法三：使用

XMLAGG

函数结合

XMLELEMENT

和

XMLSERIALIZE

当

LISTAGG

函数无法满足需求，比如合并后的字符串长度超过限制时，可以采用XML相关的函数来实现：

SELECT
RTRIM(XMLAGG(XMLELEMENT(e, value_column ||',')).EXTRACT('//text()'),',')AS merged_column
FROM your_table

在这里插入图片描述

三、Mysql

1、方法一：使用

GROUP_CONCAT()

函数

GROUP_CONCAT()

是MySQL内置的聚合函数，可以用来将同一分组内的多个值合并成一个字符串，以指定的分隔符分隔各个值：

SELECT
GROUP_CONCAT(column2 SEPARATOR ',')AS merged_column
FROM your_table
--column1 是你想要分组的列，column2 是你想合并的列，而 , 是分隔符，你可以替换为你想要的任何字符。

2、方法二：子查询结合

GROUP_CONCAT()

函数

如果你需要更复杂的合并逻辑，可以结合子查询来使用

GROUP_CONCAT()

：

SELECT t1.id,
       (SELECT GROUP_CONCAT(column2 SEPARATOR ', ')
        FROM your_table t2
        WHERE t1.id = t2.id) AS merged_column
FROM your_table t1;

标签：数据库 mysql oracle

本文转载自: https://blog.csdn.net/m0_59415345/article/details/136964055
版权归原作者 VT.馒头 所有，如有侵权，请联系我们删除。

主流数据库（SQL Server、Mysql、Oracle）通过sql实现多行数据合为一行