SQL Server STUFF() 函数详解
https://www.cnblogs.com/gzb1/p/16401441.html
https://www.cnblogs.com/niyl/p/10579132.html
STUFF():
在 SQL Server 中,stuff() 函数用于从源字符串中删除给定长度的字符序列,并从指定的起始索引插入给定的字符序列。
用法:
STUFF (source_string, start, length, change_string)
source_string:字符数据
start :指定删除和插入的开始位置
length :指定要删除的字符数
change_string : 替换start 到 length 的字符数据
实例1
select STUFF('abcdefg',1,0,'1234') --结果为'1234abcdefg' select STUFF('abcdefg',1,1,'1234') --结果为'1234bcdefg' select STUFF('abcdefg',2,1,'1234') --结果为'a1234cdefg' select STUFF('abcdefg',2,2,'1234') --结果为'a1234defg'
相当于从第几个字符开始,替换掉几个字符成后面的
实例2、SQL 将列转成字符串并用逗号分隔
SELECT STUFF((SELECT ',' + FieldName FROM TableName FOR XML PATH('')),1,1,'') AS T
SQL实现代码
select patientid,plusid=(STUFF((select ',' + plusid from test a where a.patientid=b.patientid for xml path('')),1,1,'')) from test b group by patientid
SQLServer中没有MySQL中的group_concat函数,可以把分组的数据连接在一起. SELECT a, stuff((SELECT ',' + b FROM #tb WHERE a = t.a FOR xml path('')), 1, 1, '' )AS b from # tb AS t GROUP BY a; 先对a列进行分组,对分组中的b以Xml形式输出,再使用stuff将开关多出的,删掉
去重:
SELECT sell_date,count(distinct(product)) as num_sold,
stuff (( select ',' + a.product FROM activities as a
where a.sell_date=b.sell_date
group by product
FOR XML PATH ('')),1,1,'') as products
from activities as b
group by sell_date
order by sell_date