SQL分组难题
表
字段A ,字段B
1 A
1 B
1 C
2 D
3 EF
2 KG
…………………
要求按字段A分组（字段B是字符串)
显示结果:
1 ABC
2 DKG
3 EF
……
请问如何实现？？？
[解决办法]
--sql server 2000用一函数解决.create table tb(A int, B varchar(10))insert into tb values(1, 'A') insert into tb values(1, 'B') insert into tb values(1, 'C') insert into tb values(2, 'D') insert into tb values(3, 'EF') insert into tb values(2, 'KG') go--创建一个合并的函数create function f_hb(@a int)returns varchar(8000)asbegin declare @str varchar(8000) set @str = '' select @str = @str+ cast(b as varchar) from tb where a = @a set @str = right(@str , len(@str)) return(@str)Endgo--调用自定义函数得到结果：select distinct a ,dbo.f_hb(a) as b from tbdrop table tbdrop function f_hb/*a b----------- ---1 ABC2 DKG3 EF(3 行受影响)*/
[解决办法]
--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理--定义结果集表变量DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理DECLARE tb CURSOR LOCALFORSELECT col1,col2 FROM tb ORDER BY col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SELECT @col1_old=@col1,@s=''WHILE @@FETCH_STATUS=0BEGIN IF @col1=@col1_old SELECT @s=@s+','+CAST(@col2 as varchar) ELSE BEGIN INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1 END FETCH tb INTO @col1,@col2ENDINSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--显示结果并删除测试数据SELECT * FROM @tDROP TABLE tb/*--结果col1 col2---------- -----------a 1,2b 1,2,3--*/GO/*==============================================*/--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3GO--合并处理函数CREATE FUNCTION dbo.f_str(@col1 varchar(10))RETURNS varchar(100)ASBEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(col2 as varchar) FROM tb WHERE col1=@col1 RETURN(STUFF(@re,1,1,''))ENDGO--调用函数SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1--删除测试DROP TABLE tbDROP FUNCTION f_str/*--结果col1 col2---------- -----------a 1,2b 1,2,3--*/GO/*==============================================*/--3.3.3 使用临时表实现字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理SELECT col1,col2=CAST(col2 as varchar(100)) INTO #t FROM tbORDER BY col1,col2DECLARE @col1 varchar(10),@col2 varchar(100)UPDATE #t SET @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END, @col1=col1, col2=@col2SELECT * FROM #t/*--更新处理后的临时表col1 col2---------- -------------a 1a 1,2b 1b 1,2b 1,2,3--*/--得到最终结果SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1/*--结果col1 col2---------- -----------a 1,2b 1,2,3--*/--删除测试DROP TABLE tb,#tGO/*==============================================*/--3.3.4.1 每组 <=2 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'c',3--合并处理SELECT col1, col2=CAST(MIN(col2) as varchar) +CASE WHEN COUNT(*)=1 THEN '' ELSE ','+CAST(MAX(col2) as varchar) ENDFROM tbGROUP BY col1DROP TABLE tb/*--结果col1 col2 ---------- ----------a 1,2b 1,2c 3--*/--3.3.4.2 每组 <=3 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3UNION ALL SELECT 'c',3--合并处理SELECT col1, col2=CAST(MIN(col2) as varchar) +CASE WHEN COUNT(*)=3 THEN ',' +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar) ELSE '' END +CASE WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar) ELSE '' ENDFROM tb aGROUP BY col1DROP TABLE tb/*--结果col1 col2---------- ------------a 1,2b 1,2,3c 3--*/GO
[解决办法]
--SQL SERVER 2005的写法.create table tb(A int, B varchar(10))insert into tb values(1, 'A') insert into tb values(1, 'B') insert into tb values(1, 'C') insert into tb values(2, 'D') insert into tb values(3, 'EF') insert into tb values(2, 'KG') goSELECT * FROM(SELECT DISTINCT A FROM tb)A OUTER APPLY( SELECT [B]= STUFF(REPLACE(REPLACE( ( SELECT B FROM tb N WHERE A = A.A FOR XML AUTO ), '<N B="', ' '), '"/>', ' '), 1, 1, ' '))Ndrop table tb/*a b----------- ---1 ABC2 DKG3 EF(3 行受影响)*/
[解决办法]
create table tb(A int, B varchar(10))insert into tb values(1, 'A') insert into tb values(1, 'B') insert into tb values(1, 'C') insert into tb values(2, 'D') insert into tb values(3, 'EF') insert into tb values(2, 'KG') gocreate function fn_Test(@a int)returns varchar(8000)asbegin declare @str varchar(8000) set @str = '' select @str = @str+ cast(b as varchar) from tb where a = @a set @str = right(@str , len(@str)) return(@str)Endgoselect a ,dbo.fn_Test(a) as b from tb group by a/*a b----------- --------------1 ABC2 DKG3 EF(3 row(s) affected)*/drop table tbdrop function fn_Test
[解决办法]
--SQL SERVER 2005写法.create table tb(A int, B varchar(10))insert into tb values(1, 'A') insert into tb values(1, 'B') insert into tb values(1, 'C') insert into tb values(2, 'D') insert into tb values(3, 'EF') insert into tb values(2, 'KG') goSELECT * FROM(SELECT DISTINCT A FROM tb)A OUTER APPLY( SELECT [B]= STUFF(REPLACE(REPLACE( ( SELECT B FROM tb N WHERE A = A.A FOR XML AUTO ), '<N B="', ''), '"/>', ''), 1, 0, ''))Ndrop table tb/*a b----------- ---1 ABC2 DKG3 EF(3 行受影响)*/
[解决办法]
create table tb(A int, B varchar(10))insert into tb values(1, 'A') insert into tb values(1, 'B') insert into tb values(1, 'C') insert into tb values(2, 'D') insert into tb values(3, 'EF') insert into tb values(2, 'KG') gocreate function wsp(@a int)returns varchar(1000)asbegin declare @B varchar(1000) select @B= isnull(@str,'')+ b from tb where a = @a return(@B)Endgo--调用自定义函数得到结果：select distinct a ,dbo.wsp(a) b from tb
[解决办法]
以上以','作为分隔...
[code=SQL]-- 1. 创建处理函数CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + value FROM tb WHERE id=@id RETURN @r--去掉分隔符ENDGO -- 调用函数SELECt id, values=dbo.f_str(id) FROM tb GROUP BY id -- 2. 新的解决方法 -- 示例数据DECLARE @t TABLE(id int, value varchar(10))INSERT @t SELECT 1, 'aa'UNION ALL SELECT 1, 'bb'UNION ALL SELECT 2, 'aaa'UNION ALL SELECT 2, 'bbb'UNION ALL SELECT 2, 'ccc' -- 查询处理SELECT *FROM( SELECT DISTINCT id FROM @t)AOUTER APPLY( SELECT [values]= REPLACE(REPLACE( ( SELECT value FROM @t N WHERE id = A.id FOR XML AUTO ), '<N value="', ''), '"/>', ''))N----去掉分隔符
