A表 A_ID A_NAME
4B70P3A8EP478 a_name
B表 B_ID A_ID C_ID D_ID E_ID
1280881908856000 4B70P3A8EP478 0000000000000003 0000000000000002 1105
1280881908865000 4B70P3A8EP478 0000000000000003 0000000000000004 1104
1280881908867000 4B70P3A8EP478 0000000000000003 0000000000000004 1103
1280881908871000 4B70P3A8EP478 0000000000000004 0000000000000001 1102
1280881908850000 4B70P3A8EP478 0000000000000004 0000000000000001 1101
1265847745188000 4B70P3A8EP478 0000000000000005 0000000000000001 1100 C表
C_ID C_NAME
0000000000000003 c_name1
0000000000000004 c_name2
0000000000000005 c_name3 D表 D_ID D_NAME
0000000000000001 d_name1
0000000000000002 d_name2
0000000000000003 d_name3
0000000000000004 d_name4
0000000000000005 d_name5E表 E_ID E_NAME
1100 e_n0
1101 e_n1
1102 e_n2
1103 e_n3
1104 e_n4
1105 e_n5查询的结果如下 A_ID A_NAME C_NAME D_NAME E_NAME
4B70P3A8EP478 a_name c_name1 d_name2 e_n5
4B70P3A8EP478 a_name c_name1 d_name4 e_n3,e_n4
4B70P3A8EP478 a_name c_name2 d_name1 e_n1,e_n2
4B70P3A8EP478 a_name c_name3 d_name1 e_n0
4B70P3A8EP478 a_name
B表 B_ID A_ID C_ID D_ID E_ID
1280881908856000 4B70P3A8EP478 0000000000000003 0000000000000002 1105
1280881908865000 4B70P3A8EP478 0000000000000003 0000000000000004 1104
1280881908867000 4B70P3A8EP478 0000000000000003 0000000000000004 1103
1280881908871000 4B70P3A8EP478 0000000000000004 0000000000000001 1102
1280881908850000 4B70P3A8EP478 0000000000000004 0000000000000001 1101
1265847745188000 4B70P3A8EP478 0000000000000005 0000000000000001 1100 C表
C_ID C_NAME
0000000000000003 c_name1
0000000000000004 c_name2
0000000000000005 c_name3 D表 D_ID D_NAME
0000000000000001 d_name1
0000000000000002 d_name2
0000000000000003 d_name3
0000000000000004 d_name4
0000000000000005 d_name5E表 E_ID E_NAME
1100 e_n0
1101 e_n1
1102 e_n2
1103 e_n3
1104 e_n4
1105 e_n5查询的结果如下 A_ID A_NAME C_NAME D_NAME E_NAME
4B70P3A8EP478 a_name c_name1 d_name2 e_n5
4B70P3A8EP478 a_name c_name1 d_name4 e_n3,e_n4
4B70P3A8EP478 a_name c_name2 d_name1 e_n1,e_n2
4B70P3A8EP478 a_name c_name3 d_name1 e_n0
4B70P3A8EP478 a_name c_name1 d_name2 e_n5
4B70P3A8EP478 a_name c_name1 d_name4 e_n3,e_n4
4B70P3A8EP478 a_name c_name2 d_name1 e_n1,e_n2
4B70P3A8EP478 a_name c_name3 d_name1 e_n0想要的理想查询结果如下:
A_ID A_NAME C_NAME D_NAME E_NAME D_NAME E_NAME
4B70P3A8EP478 a_name c_name1 d_name2 e_n5 d_name4 e_n3,e_n4
4B70P3A8EP478 a_name c_name2 d_name1 e_n1,e_n2
4B70P3A8EP478 a_name c_name3 d_name1 e_n0
--*******************************************************************************************
表结构,数据如下:
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) */ drop table tb