查询两张表，拼接其中某字段值
现在有2张表，father，son。他们是一对多关系。
father:(id,name)
1,王爸爸
2,张爸爸
son:(name,father_id)
大王子,1
二王子,1
三王子,1
大张子,2
二张子,2
现在需要实现输出如下
王爸爸 大王子，二王子，三网子
张爸爸 大张子，二张子
谢谢
[最优解释]
--创建数据开始
if(object_id('father')is not null) drop table father
go
create table father
(
id int,
[name] varchar(50)
)
go
insert into father
select 1,'王爸爸' union
select 2,'张爸爸'
go
if(object_id('son')is not null) drop table son
go
create table son
(
[name] varchar(50),
father_id int
)
go
insert into son
select '大王子',1 union all
select '二王子',1 union all
select '三王子',1 union all
select '大张子',2 union all
select '二张子',2
go
--创建数据结束
--创建函数
create function getSonName()
returns @GetName table(fatherName varchar(50),sonName varchar(6000))
as
begin
declare @fatherID int
declare @fatherName varchar(50)
declare @SonName1 varchar(6000)
declare Cfather cursor for select id,[name] from father
open Cfather
fetch next from Cfather into @fatherID,@fatherName
while(@@fetch_status=0)
begin
set @sonName1=''
select @sonName1 = isnull(@sonName1+',',' ')+ [name] from son where father_id = @fatherid
insert into @Getname values(@fatherName,right(@sonName1,len(@sonName1)-1))
fetch next from Cfather into @fatherID,@fatherName
end
close Cfather
deallocate Cfather
return
end
go
--调用函数
select * from GetSonName()
--结果展示
/*
fatherName sonName
-------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
王爸爸 大王子,二王子,三王子
张爸爸 大张子,二张子
(2 行受影响)
*/
drop table father
go
drop table son
go
create table father(
id int not null,
name varchar(30))
go
create table son(
father_id int not null,
name varchar(30))
go
insert father
select 1,'A'
union all
select 2,'B'
go
insert son
select 1,'a1'
union all
select 1,'a2'
union all
select 1,'a3'
union all
select 2,'b1'
union all
select 2,'b2'
go
--插入数据
insert father
select 1,'A'union all
select 2,'B'union all
select 1,'A'union all
select 2,'B'
go
insert son
select 'a1',1union all
select 'a2',1union all
select 'a3',1union all
select 'b1',2union all
select 'b2',2
go
--father 表信息
select * from father
/*
id name
----------- --------------------------------------------------
1 王爸爸
2 张爸爸
1 A
2 B
1 A
2 B
(6 行受影响)
*/
--son 表信息
select * from son
/*
name father_id
-------------------------------------------------- -----------
大王子 1
二王子 1
三王子 1
大张子 2
二张子 2
a1 1
a2 1
a3 1
b1 2
b2 2
(10 行受影响)
*/
--调用函数，函数在一楼已经创建，
select * from GetSonName()
--结果展示
/*
fatherName sonName
-------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
王爸爸 大王子,二王子,三王子,a1,a2,a3
张爸爸 大张子,二张子,b1,b2
A 大王子,二王子,三王子,a1,a2,a3
B 大张子,二张子,b1,b2
A 大王子,二王子,三王子,a1,a2,a3
B 大张子,二张子,b1,b2
(6 行受影响)
*/
----------------------------
-- Author :TravyLee(物是人非事事休，欲语泪先流！)
-- Date :2012-11-11 19:00:20
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------
--> 测试数据：[father]
if object_id('[father]') is not null
drop table [father]
go
create table [father]
(
[id] int,
[name] varchar(6)
)
insert [father]
select 1,'王爸爸' union all
select 2,'张爸爸'
--> 测试数据：[son]
if object_id('[son]') is not null
drop table [son]
go
create table [son]
(
[name] varchar(6),
[father_id] int
)
insert [son]
select '大王子',1 union all
select '二王子',1 union all
select '三王子',1 union all
select '大张子',2 union all
select '二张子',2
;with t
as(
select
f.name,
s.name as Son_name
from
[father] f
inner join
[son] s
on
f.id=s.father_id
)
select
distinct name,
Son_name=stuff((select ','+Son_name
from t b
where b.name=t.name for xml path('')), 1, 1, '')
from t
/*
(2 行受影响)
(5 行受影响)
name Son_name
------ --------------------------
王爸爸 大王子,二王子,三王子
张爸爸 大张子,二张子
(2 行受影响)
*/
[其他解释]
select
distinct f.name as name,
son_name=stuff(
(select ','+son.name from son join father on
son.father_id=father.id for xml path('')),1,1,'')
from father f
nameson_name
Aa1,a2,a3,b1,b2
Ba1,a2,a3,b1,b2
