根据某一字段值相同合并字符串 - - SQL

做项目的过程中,遇到一个问题,相同id,不同value的记录希望合并成一条记录,value以逗号分隔,从网上搜了搜解决方案,整理如下,备忘。

 

一、           字符串合并

表名:test

字段:

         id               int

         name        nvarchar(50)

字段值:

          根据某一字段值相同合并字符串 - - SQL_sql

期望结果:

         id               nameStr

         -----------------------------

         1                a,b,c

         2                d,e

         3                f

SQL实现:

1、 利用函数解决

 

-- 创建处理函数

CREATE FUNCTION dbo.f_str(@id int)

RETURNS varchar(8000)

AS

BEGIN

    DECLARE @r varchar(8000)

    SET @r = ''

    SELECT @r = @r + ',' + [name]

    FROM [test]

    WHERE [id]=@id

    RETURN STUFF(@r, 1, 1, '')

END

GO

 

-- 调用函数

SELECT [id], [nameStr]=dbo.f_str(id)

FROM [test]

GROUP BY [id]

 

运行结果:

   根据某一字段值相同合并字符串 - - SQL_sql_02

 

 

2、 直接用SQL解决

 

SELECT *

FROM(

    SELECT DISTINCT [id]

    FROM [test]

)A

OUTER APPLY(

    SELECT

       [nameStr]= STUFF(REPLACE(REPLACE(

       (

           SELECT [name] FROM [test] N

           WHERE [id] = A.id

           FOR XML AUTO

       ), '<N name="', ','), '"/>', ''), 1, 1, '')

)M

 

运行结果:

   根据某一字段值相同合并字符串 - - SQL_sql_03

 

看不懂这个SQL不要紧,下面有详细分析

 

二、           详细分析

1、  STUFF

作用:

         STUFF函数将字符串插入另一字符串。它在第一个字符串中从开始位置删

除指定长度的字符;然后将第二个字符串插入第一个字符串的开始位置。

语法:

        STUFF ( character_expression , start , length ,character_expression )

参数:

character_expression

                     一个字符数据表达式。character_expression可以是常量、变量,也可

                     以是字符列或二进制数据列。

start

                     一个整数值,指定删除和插入的开始位置。如果startlength为负,

      则返回空字符串。如果start比第一个character_expression长,则返

      回空字符串。start可以是bigint类型。

                             length

                                        一个整数,指定要删除的字符数。如果length比第一个

character_expression长,则最多删除到第一个character_expression

中的最后一个字符。Length可以是bigint类型。

返回类型:

         如果character_expression是受支持的字符数据类型,则返回字符数据。

 如果character_expression是一个受支持的 binary 数据类型,则返回二

 进制数据。

注释:

   如果开始位置或长度值是负数,或者如果开始位置大于第一个字符串的长

 度,将返回空字符串。如果要删除的长度大于第一个字符串的长度,将删

 除到第一个字符串中的最后一个字符。如果结果值大于返回类型支持的最

 大值,则产生错误。

示例:

              SELECT STUFF('abcdef', 2, 3, 'ijklmn');

GO

结果:

          根据某一字段值相同合并字符串 - - SQL_sql_04

 

SELECT STUFF('abcdef', -2, 3, 'ijklmn');

GO

结果:

          根据某一字段值相同合并字符串 - - SQL_sql_05

 

SELECT STUFF('abcdef', 2, 10, 'ijklmn');

GO

结果:

          根据某一字段值相同合并字符串 - - SQL_sql_06

 

2、  CROSS APPLY  OUTER APPLY

 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入,外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表。

 APPLY 有两种形式:CROSS APPLY  OUTER APPLYCROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列中的值为NULL

 

用示例来区分两者:

第一张表:

            表名:student

            字段名:

                      根据某一字段值相同合并字符串 - - SQL_sql_07

            字段值:

                      根据某一字段值相同合并字符串 - - SQL_sql_08

第二张表:

            表名:student_class

            字段名:

                      根据某一字段值相同合并字符串 - - SQL_sql_09

            字段值:

                      根据某一字段值相同合并字符串 - - SQL_sql_10

 

CROSS APPLY

SELECT *

FROM [student] a

CROSS APPLY(

  SELECT [class], [score]

  FROM [student_class]

  WHERE student_id = a.id

) b

 

运行结果:

   根据某一字段值相同合并字符串 - - SQL_sql_11

 

 

OUTER APPLY

SELECT *

FROM [student] a

OUTER APPLY(

  SELECT [class], [score]

  FROM [student_class]

  WHERE student_id = a.id

) b

 

运行结果:

      根据某一字段值相同合并字符串 - - SQL_sql_12

 

3、  FOR XML AUTO

将结果转换为XML结构,例如对于上面2中的student表,执行以下语句

SELECT *

FROM [student]

FOR XML AUTO

 

运行结果:

   根据某一字段值相同合并字符串 - - SQL_sql_13

 

 

其中FOR XML还有其他方式:RAWEXPLICITPATH,有兴趣的同学可以深入研究,这里不再赘述。