20,359
社区成员
发帖
与我相关
我的任务
分享
SELECT `stroe`,GROUP_CONCAT(CONCAT(`product`,`number`)) AS `items` FROM `你的表` GROUP BY `stroe`;
QL codeSQL code问题描述:
无论是在sql2000,还是在 sql2005 中,都没有提供字符串的聚合函数,
所以,当我们在处理下列要求时,会比较麻烦:
有表tb, 如下:
id value----- ------1 aa1 bb2 aaa2 bbb2 ccc
需要得到结果:
idvalues------ -----------1 aa,bb2 aaa,bbb,ccc
即,groupby id, 求 value 的和(字符串相加)1. 旧的解决方法-- 1. 创建处理函数CREATEFUNCTION dbo.f_str(@idint)RETURNSvarchar(8000)ASBEGINDECLARE@rvarchar(8000)SET@r=''SELECT@r=@r+','+ valueFROM tbWHERE id=@idRETURNSTUFF(@r,1,1,'')ENDGO-- 调用函数SELECt id,values=dbo.f_str(id)FROM tbGROUPBY id-- 2. 新的解决方法
-- 示例数据DECLARE@tTABLE(idint, valuevarchar(10))INSERT@tSELECT1,'aa'UNIONALLSELECT1,'bb'UNIONALLSELECT2,'aaa'UNIONALLSELECT2,'bbb'UNIONALLSELECT2,'ccc'-- 查询处理SELECT*FROM(SELECTDISTINCT
idFROM@t
)AOUTER APPLY(SELECT[values]=STUFF(REPLACE(REPLACE(
(SELECT valueFROM@t NWHERE id= A.idFOR XML AUTO
),'<N value="',','),'"/>',''),1,1,'')
)N/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据CREATETABLE tb(col1varchar(10),col2int)INSERT tbSELECT'a',1UNIONALLSELECT'a',2UNIONALLSELECT'b',1UNIONALLSELECT'b',2UNIONALLSELECT'b',3--合并处理
--定义结果集表变量DECLARE@tTABLE(col1varchar(10),col2varchar(100))--定义游标并进行合并处理DECLARE tbCURSOR LOCALFORSELECT col1,col2FROM tbORDERBY col1,col2DECLARE@col1_oldvarchar(10),@col1varchar(10),@col2int,@svarchar(100)OPEN tbFETCH tbINTO@col1,@col2SELECT@col1_old=@col1,@s=''WHILE@@FETCH_STATUS=0BEGINIF@col1=@col1_oldSELECT@s=@s+','+CAST(@col2asvarchar)ELSEBEGININSERT@tVALUES(@col1_old,STUFF(@s,1,1,''))SELECT@s=','+CAST(@col2asvarchar),@col1_old=@col1ENDFETCH tbINTO@col1,@col2ENDINSERT@tVALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--显示结果并删除测试数据SELECT*FROM@tDROPTABLE tb/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/GO/*==============================================*/--3.3.2 使用用户定义函数,配合SELECT处理完成字符串合并处理的示例
--处理的数据CREATETABLE tb(col1varchar(10),col2int)INSERT tbSELECT'a',1UNIONALLSELECT'a',2UNIONALLSELECT'b',1UNIONALLSELECT'b',2UNIONALLSELECT'b',3GO--合并处理函数CREATEFUNCTION dbo.f_str(@col1varchar(10))RETURNSvarchar(100)ASBEGINDECLARE@revarchar(100)SET@re=''SELECT@re=@re+','+CAST(col2asvarchar)FROM tbWHERE col1=@col1RETURN(STUFF(@re,1,1,''))ENDGO--调用函数SELECT col1,col2=dbo.f_str(col1)FROM tbGROUPBY col1--删除测试DROPTABLE tbDROPFUNCTION f_str/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/GO/*==============================================*/--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据CREATETABLE tb(col1varchar(10),col2int)INSERT tbSELECT'a',1UNIONALLSELECT'a',2UNIONALLSELECT'b',1UNIONALLSELECT'b',2UNIONALLSELECT'b',3--合并处理SELECT col1,col2=CAST(col2asvarchar(100))INTO #tFROM tbORDERBY col1,col2DECLARE@col1varchar(10),@col2varchar(100)UPDATE #tSET@col2=CASEWHEN@col1=col1THEN@col2+','+col2ELSE col2END,@col1=col1,
col2=@col2SELECT*FROM #t/*--更新处理后的临时表
col1 col2
---------- -------------
a 1
a 1,2
b 1
b 1,2
b 1,2,3
--*/--得到最终结果SELECT col1,col2=MAX(col2)FROM #tGROUPBY col1/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/--删除测试DROPTABLE tb,#tGO/*==============================================*/--3.3.4.1 每组 <=2 条记录的合并
--处理的数据CREATETABLE tb(col1varchar(10),col2int)INSERT tbSELECT'a',1UNIONALLSELECT'a',2UNIONALLSELECT'b',1UNIONALLSELECT'b',2UNIONALLSELECT'c',3--合并处理SELECT col1,
col2=CAST(MIN(col2)asvarchar)+CASEWHENCOUNT(*)=1THEN''ELSE','+CAST(MAX(col2)asvarchar)ENDFROM tbGROUPBY col1DROPTABLE tb/*--结果
col1 col2
---------- ----------
a 1,2
b 1,2
c 3
--*/--3.3.4.2 每组 <=3 条记录的合并
--处理的数据CREATETABLE tb(col1varchar(10),col2int)INSERT tbSELECT'a',1UNIONALLSELECT'a',2UNIONALLSELECT'b',1UNIONALLSELECT'b',2UNIONALLSELECT'b',3UNIONALLSELECT'c',3--合并处理SELECT col1,
col2=CAST(MIN(col2)asvarchar)+CASEWHENCOUNT(*)=3THEN','+CAST((SELECT col2FROM tbWHERE col1=a.col1AND col2NOTIN(MAX(a.col2),MIN(a.col2)))asvarchar)ELSE''END+CASEWHENCOUNT(*)>=2THEN','+CAST(MAX(col2)asvarchar)ELSE''ENDFROM tb aGROUPBY col1DROPTABLE tb/*--结果
col1 col2
---------- ------------
a 1,2
b 1,2,3
c 3
--*/GOifnotobject_id('A')isnulldroptable AGoCreatetable A([id]int,[cname]nvarchar(2))Insert Aselect1,N'张三'unionallselect2,N'李四'unionallselect3,N'王五'unionallselect4,N'蔡六'Go--> -->ifnotobject_id('B')isnulldroptable BGoCreatetable B([id]int,[cname]nvarchar(5))Insert Bselect1,N'1,2,3'unionallselect2,N'3,4'Gocreatefunction F_str(@cnamenvarchar(100))returnsnvarchar(100)asbeginselect@cname=replace(@cname,ID,[cname])from Awherepatindex('%,'+rtrim(ID)+',%',','+@cname+',')>0return@cnameendgoselect[id],dbo.F_str([cname])[cname]from B
id cname----------- ----------------------------------------------------------------------------------------------------1 张三,李四,王五2 王五,蔡六
(2 個資料列受到影響)