[这问题有点难]不循环两次,能不能做这样的列表? 本帖最后由 chetnik 于 2009-11-04 17:21:50 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 千万别百度 要找就找Google 样例很多 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,bb2 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,2b 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,2b 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 1a 1,2b 1b 1,2b 1,2,3--*/--得到最终结果SELECT col1,col2=MAX(col2)FROM #tGROUPBY col1/*--结果col1 col2---------- -----------a 1,2b 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,2b 1,2c 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,2b 1,2,3c 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 Bid cname----------- ----------------------------------------------------------------------------------------------------1 张三,李四,王五2 王五,蔡六(2 個資料列受到影響) 都帖乱了,看这个帖子吧http://topic.csdn.net/u/20081020/10/86f4ff90-8dad-4e34-bb65-18cdad463703.html#replyachor 不用转存过程,将产品和数量 合并成新字段,然后用GROUP_CONCAT将新字段聚合用逗号分隔,成为新字段,然后用的时候用explode拆分聚合的新字段,就1条sql就可以搞定~SELECT `stroe`,GROUP_CONCAT(CONCAT(`product`,`number`)) AS `items` FROM `你的表` GROUP BY `stroe`; 一个sql获取所有的结果,然后一个foreach循环吧所有的结果组成列表 PHP实现局部刷新 joomla 打开网站之前先显示一个flash,或者图片背景 关于php代码加解密的问题 新手请教 求一个政府门户 这样不行么 svn子目录权限配置 求教一个 git php 问题 微信 出现参数错误,请重新填写 谁有PHP的培训机构的完整视频?求分享!谢谢 php 导出成 exl如何让 exl表格+上边框? 前台使用php写入session数据,后台使用C语言从session文件中读取数据-可行性分析
无论是在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 Bid cname----------- ----------------------------------------------------------------------------------------------------1 张三,李四,王五2 王五,蔡六(2 個資料列受到影響)
http://topic.csdn.net/u/20081020/10/86f4ff90-8dad-4e34-bb65-18cdad463703.html#replyachor
SELECT `stroe`,GROUP_CONCAT(CONCAT(`product`,`number`)) AS `items` FROM `你的表` GROUP BY `stroe`;