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

妖狐艹你老母 2022-04-03 07:26 217阅读 0赞

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

**一、****字符串合并**

**表名：**test

**字段：**

idint

namenvarchar(50)

**字段值：**

![0\_1290680988iP2E.gif][0_1290680988iP2E.gif]

**期望结果：**

idnameStr

\-----------------------------

1a,b,c

2d,e

**SQL****实现：**

**1、****利用函数解决**

\-- 创建处理函数

CREATEFUNCTION dbo.f\_str(@id int)

RETURNSvarchar(8000)

BEGIN

DECLARE @r varchar(8000)

SET @r =''

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

FROM \[test\]

WHERE \[id\]=@id

SET @r =STUFF(@r, 1, 1,'')

RETURN@r

END

\-- 调用函数

SELECT \[id\], \[nameStr\]=dbo.f\_str(id)

FROM \[test\]

GROUPBY \[id\]

运行结果：

![0\_1290681055a4dx.gif][0_1290681055a4dx.gif]

**2、****直接用SQL****解决**

SELECT\*

FROM(

SELECTDISTINCT \[id\]

FROM \[test\]

OUTERAPPLY(

SELECT

\[nameStr\]=STUFF(REPLACE(REPLACE(

(

SELECT \[name\] FROM \[test\] N

WHERE \[id\] = A.id

FORXMLAUTO

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

运行结果：

![0\_12906810987Irl.gif][0_12906810987Irl.gif]

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

**二、****详细分析**

1、STUFF

**作用：**

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

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

**语法：**

STUFF ( character\_expression , start , length ,character\_expression )

**参数：**

*character\_expression*

一个字符数据表达式。*character\_expression*可以是常量、变量，也可

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

*start*

一个整数值，指定删除和插入的开始位置。如果*start*或*length*为负，

则返回空字符串。如果*start*比第一个*character\_expression*长，则返

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

*length*

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

*character\_expression*长，则最多删除到第一个*character\_expression*

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

**返回类型：**

如果*character\_expression*是受支持的字符数据类型，则返回字符数据。

如果*character\_expression*是一个受支持的 binary 数据类型，则返回二

进制数据。

**注释：**

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

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

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

大值，则产生错误。

**示例：**

SELECTSTUFF('abcdef', 2, 3,'ijklmn');

结果：

![0\_1290681135G5TE.gif][0_1290681135G5TE.gif]

SELECTSTUFF('abcdef',\-2, 3,'ijklmn');

结果：

![0\_12906811620rRZ.gif][0_12906811620rRZ.gif]

SELECTSTUFF('abcdef', 2, 10,'ijklmn');

结果：

![0\_1290681193WGGJ.gif][0_1290681193WGGJ.gif]

2、CROSS APPLY 与OUTERAPPLY

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

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

**用示例来区分两者：**

**第一张表：**

表名：student

字段名：

![0\_1290681221fZ0d.gif][0_1290681221fZ0d.gif]

字段值：

![0\_1290681253zHgH.gif][0_1290681253zHgH.gif]

**第二张表：**

表名：student\_class

字段名：

![0\_1290681283kKD8.gif][0_1290681283kKD8.gif]

字段值：

![0\_12906813110Efr.gif][0_12906813110Efr.gif]

CROSS APPLY

SELECT\*

FROM \[student\] a

CROSSAPPLY(

SELECT \[class\], \[score\]

FROM \[student\_class\]

WHERE student\_id = a.id

) b

运行结果：

![0\_1290681340eVzk.gif][0_1290681340eVzk.gif]

OUTERAPPLY

SELECT\*

FROM \[student\] a

OUTERAPPLY(

SELECT \[class\], \[score\]

FROM \[student\_class\]

WHERE student\_id = a.id

) b

运行结果：

![0\_1290681375xw6o.gif][0_1290681375xw6o.gif]

3、FORXMLAUTO

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

SELECT\*

FROM \[student\]

FORXMLAUTO

运行结果：

![0\_12906814068f49.gif][0_12906814068f49.gif]

其中FOR XML还有其他方式：RAW、EXPLICIT和PATH，有兴趣的同学可以深入研究，这里不再赘述。

转载至：http://blog.csdn.net/xiaomianao2010/article/details/6035634

[0_1290680988iP2E.gif]: /images/20220403/999789612a6645e1872361be39b5b099.png
[0_1290681055a4dx.gif]: /images/20220403/db2fe14b8d0f4c43911ddc1a9ae3a53d.png
[0_12906810987Irl.gif]: /images/20220403/45b529e0002a4207b7eb779705d701b8.png
[0_1290681135G5TE.gif]: /images/20220403/35ea328f21ce42389c7daa9e5ac921e3.png
[0_12906811620rRZ.gif]: /images/20220403/01007c3cf8d44555953c3739f9ddd648.png
[0_1290681193WGGJ.gif]: /images/20220403/296297fee0a540a292a973d39088ab68.png
[0_1290681221fZ0d.gif]: /images/20220403/169724c754d544059839237732dea864.png
[0_1290681253zHgH.gif]: /images/20220403/62b4b435e8a94ed6a900b38ea817c8dd.png
[0_1290681283kKD8.gif]: /images/20220403/70854b28c8b24cc79c858b5b94af7f5f.png
[0_12906813110Efr.gif]: /images/20220403/6b1f021f82494be6bd8013287ef24d77.png
[0_1290681340eVzk.gif]: /images/20220403/bc0776c4d00d4387b4e875ef6c38654b.png
[0_1290681375xw6o.gif]: /images/20220403/408c828befd8460483064df7c94a2b26.png
[0_12906814068f49.gif]: /images/20220403/112f169e804149cd8706d231679ee5d3.png