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

主流数据库(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 中主要用于对字符串操作,它能实现两个主要功能:

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

以下是 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 @anotherString VARCHAR(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
GROUP BY 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
GROUP BY SomeGroupingColumn; -- 如果需要按某个字段分组,如果你不需要分组,直接将所有行连接起来,则可以省略 GROUP BY 子句。

在这里插入图片描述

二、Oracle

1、方法一:使用 LISTAGG 函数

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

SELECT 
LISTAGG(value_column, ',') WITHIN GROUP (ORDER BY 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 函数结合 XMLELEMENTXMLSERIALIZE

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;
  • 21
    点赞
  • 26
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
OracleMySQLSQL Server是三种常见的关系型数据库管理系统(RDBMS),它们在数据类型方面存在一些区别。以下是它们主要的数据类型及其区别: 1. 字符串类型: - Oracle:包括CHAR、VARCHAR2、NCHAR、NVARCHAR2等。 - MySQL:包括CHAR、VARCHAR、TINYTEXT、TEXT、LONGTEXT等。 - SQL Server:包括CHAR、VARCHAR、NCHAR、NVARCHAR、TEXT等。 2. 数值类型: - Oracle:包括NUMBER、INTEGER、FLOAT、DOUBLE PRECISION等。 - MySQL:包括INT、BIGINT、FLOAT、DOUBLE等。 - SQL Server:包括INT、BIGINT、FLOAT、REAL等。 3. 日期和时间类型: - Oracle:包括DATE、TIMESTAMP、INTERVAL等。 - MySQL:包括DATE、TIME、DATETIME等。 - SQL Server:包括DATE、TIME、DATETIME等。 4. 二进制类型: - Oracle:包括BLOB、RAW等。 - MySQL:包括BLOB、BINARY等。 - SQL Server:包括BLOB、BINARY等。 5. 布尔类型: - Oracle:不支持原生的布尔类型,可以使用NUMBER(1)或CHAR(1)来表示。 - MySQL:支持BOOL或BOOLEAN类型。 - SQL Server:不支持原生的布尔类型,可以使用BIT来表示。 6. 其他特殊类型: - Oracle:包括CLOB(大文本)、BFILE(二进制文件)等。 - MySQL:包括ENUM、SET等。 - SQL Server:包括XML、CURSOR等。 需要注意的是,尽管这些数据库有一些相同的数据类型,但在具体实现和功能上可能存在一些差异。此外,每个数据库还可能支持一些特定的扩展类型或自定义类型。 在选择数据库和设计表结构时,应该根据具体的应用需求和数据库的特性来选择合适的数据类型。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值