现有数据:
codeGC nameGC addressGC explainGCXXX001 name001 address001 explain001
XXX002 name002 address002 explain002
XXX003 name003 address003 explain003
XXX004 name004 address004 explain004
XXX001 name001 address005 explain001
XXX002 name006 address006 explain002
XXX003 name003 address007 explain003
这4列都是varchar(50)的类型;我要查出结果:codeGC nameGC addressGC explainGCxxx001 name001 address001 address005 explain001
xxx002 name002 address002 address006 explain002
xxx003 name003 address003 address007 explain003
xxx004 name004 address004 explain004
也就是说按codeGC进行分组查询,并且把他们的addressGC合并成一列。麻烦各位帮派给个SQL语句谢谢了,原帖地址:http://topic.csdn.net/u/20110918/13/8fcf9cc7-a5a7-4ba5-bc08-70695297c77c.html?67829
codeGC nameGC addressGC explainGCXXX001 name001 address001 explain001
XXX002 name002 address002 explain002
XXX003 name003 address003 explain003
XXX004 name004 address004 explain004
XXX001 name001 address005 explain001
XXX002 name006 address006 explain002
XXX003 name003 address007 explain003
这4列都是varchar(50)的类型;我要查出结果:codeGC nameGC addressGC explainGCxxx001 name001 address001 address005 explain001
xxx002 name002 address002 address006 explain002
xxx003 name003 address003 address007 explain003
xxx004 name004 address004 explain004
也就是说按codeGC进行分组查询,并且把他们的addressGC合并成一列。麻烦各位帮派给个SQL语句谢谢了,原帖地址:http://topic.csdn.net/u/20110918/13/8fcf9cc7-a5a7-4ba5-bc08-70695297c77c.html?67829
我的SQL太差,我大概也知道是怎么回事,但是就是不知道怎么写语句啊
nameGC =tb.nameGC and explainGC =tb.explainGC for xml path('')), 1, 1, '')
from tb
group by codeGC, nameGC,explainGC
--试试
(
codeGC varchar(50),
nameGC varchar(50),
addressGC varchar(50),
explainGC varchar(50)
)
insert into tb values('XXX001' ,'name001', 'address001' ,'explain001')
insert into tb values('XXX002' ,'name002', 'address002' ,'explain002')
insert into tb values('XXX003' ,'name003', 'address003' ,'explain003')
insert into tb values('XXX004' ,'name004', 'address004' ,'explain004')
insert into tb values('XXX001' ,'name001', 'address005' ,'explain001')
insert into tb values('XXX002' ,'name002', 'address006' ,'explain002')
insert into tb values('XXX003' ,'name003', 'address007' ,'explain003')
select codeGC, nameGC,explainGC, addressGC=stuff((select ' '+addressGC from tb t where codeGC=tb.codeGC and
nameGC =tb.nameGC and explainGC =tb.explainGC for xml path('')), 1, 1, '')
from tb
group by codeGC, nameGC,explainGC /*
codeGC,nameGC,explainGC,addressGC
XXX001,name001,explain001,address001 address005
XXX002,name002,explain002,address002 address006
XXX003,name003,explain003,address003 address007
XXX004,name004,explain004,address004(4 行受影响)
--*******************************************************************************************
表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc 需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加) 1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb /*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc (2 行受影响)
*/ --SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id /*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc (2 row(s) affected) */
select codeGC, nameGC,explainGC, addressGC=stuff((select ' '+addressGC from tb t where codeGC=tb.codeGC
for xml path('')), 1, 1, '')
from tb
group by codeGC, nameGC,explainGC
或者这样
DROP TABLE TEST
create table test
(
CODEGC VARCHAR(10),
NAMEGC VARCHAR(10),
ADDRESSGC VARCHAR(10),
EXPLAINGC VARCHAR(10)
)
GO
insert into test
select 'xxx01','name001','address001','explain001'
union all
select 'xxx01','name001','address005','explain001'
union all
select 'xxx02','name002','address002','explain002'SELECT DISTINCT C.CODEGC,D.NAMEGC,C.ADDRESSGC,D.EXPLAINGC
FROM (SELECT A.CODEGC,STUFF((SELECT ' ' + B.ADDRESSGC FROM TEST AS B WHERE A.CODEGC = B.CODEGC FOR XML PATH('')),1,1,'' ) AS ADDRESSGC FROM TEST AS A GROUP BY A.CODEGC) AS C INNER JOIN TEST AS D
ON C.CODEGC = D.CODEGC结果xxx01 name001 address001 address005 explain001
xxx02 name002 address002 explain002
insert into tb select '001','name001','address001','explain001'
insert into tb select '002','name002','address002','explain002'
insert into tb select '003','name003','address003','explain003'
insert into tb select '004','name004','address004','explain004'
insert into tb select '001','name001','address005','explain001'
insert into tb select '002','name002','address006','explain002' --如果这行nameGC是 002
insert into tb select '003','name003','address007','explain003'
go
select codeGC,nameGC,stuff((select ','+addressGC from tb where codeGC=a.codeGC for xml path('')),1,1,'')addressGC,explainGC
from tb a group by codeGC,nameGC,explainGC
/*
codeGC nameGC addressGC explainGC
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
001 name001 address001,address005 explain001
002 name002 address002,address006 explain002
003 name003 address003,address007 explain003
004 name004 address004 explain004(4 行受影响)*/go
drop table tb
XXX001 name001 explain001 address001 address005
XXX002 name002 explain002 address002
XXX002 name006 explain002 address006
XXX003 name003 explain003 address003 address007
XXX004 name004 explain004 address004他的XXX002出现了2次,只要XXX002的出现一次,name002 ,name006出现一个就行呢?
出现两次重复的数据就用Distinct搞定它。
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + addressGC FROM tb WHERE codeGC=@codeGC
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt codeGC,nameGC,explainGC, addressGC = dbo.f_strUnite(codeGC) FROM tb GROUP BY codeGC ,nameGC,explainGC/*
codeGC,nameGC,explainGC,addressGC
XXX001,name001,explain001,address001,address005
XXX002,name002,explain002,address002,address006
XXX003,name003,explain003,address003,address007
XXX004,name004,explain004,address004(4 行受影响)
怎么取法?
也就是说:
这个是你第一种查出来的:XXX001 name001 explain001 address001 address005
XXX002 name002 explain002 address002
XXX002 name006 explain002 address006
XXX003 name003 explain003 address003 address007
XXX004 name004 explain004 address004XXX002 这个编号的只出现一次,名字出现任意一个,然后address002,address006这2个和其一样的合并。
这个大哥 你发的那个我试了,是可以的,呵呵。
只不过 我如果想把codeGC这列编号相同的并且nameGC这一列不同的只出现一次的话,他还是会查出2条、
这两个分组