表1
personid name ...(其他字段略)
p001 001
p002 002
p003 003表2
id personid address
1 p001 aaa
2 p002 bbb
3 p002 ccc如何连接两表,使得结果如下:
personid name address
p001 001 aaa
p002 002 bbb+ccc
p003 003 null
personid name ...(其他字段略)
p001 001
p002 002
p003 003表2
id personid address
1 p001 aaa
2 p002 bbb
3 p002 ccc如何连接两表,使得结果如下:
personid name address
p001 001 aaa
p002 002 bbb+ccc
p003 003 null
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1](personid varchar(50),[name] varchar(50))
goif object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2](personid varchar(50),address varchar(50))
goinsert into tb1
select 'p001','001'
union select 'p002','002'
union select 'p003','003'insert into tb2
select 'p001','aaa'
union select 'p002','bbb'
union select 'p002','ccc'if object_id('fn_ConvertAddress') is not null drop function fn_ConvertAddress
go
create function fn_ConvertAddress
(
@personid varchar(50)
)
returns varchar(8000)
as
begin
declare @name1 varchar(50),@name2 varchar(50),@result varchar(8000)
set @result=''
select @result=@result+'+'+address from [tb2]
where personid=@personid
if(len(@result)>0) set @result=right(@result,len(@result)-1)
return @result
endselect *,dbo.fn_ConvertAddress(personid) as Address from tb1
select a.personid,a.name,b.address from tb1 a left join
(
select personid, [address]=stuff((select '+'+[address] from tb2 t where personid=tb.personid for xml path('')), 1, 1, '')
from tb2
group by personid
)b
on a.personid = b.personid
From A
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1](personid varchar(50),[name] varchar(50))
goif object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2](personid varchar(50),address varchar(50))
goinsert into tb1
select 'p001','001'
union select 'p002','002'
union select 'p003','003'insert into tb2
select 'p001','aaa'
union select 'p002','bbb'
union select 'p002','ccc'
select a.personid,a.name,b.address from tb1 a left join
(
select personid, [address]=stuff((select '+'+[address] from tb2 t where personid=tb2.personid for xml path('')), 1, 1, '')
from tb2
group by personid
)b
on a.personid = b.personid
go
create table [t1]([personid] varchar(10),[name] varchar(10))
insert [t1] select 'p001','001'
union all select 'p002','002'
union all select 'p003','003'if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([id] int,[personid] varchar(10),[address] varchar(10))
insert [t2] select 1,'p001','aaa'
union all select 2,'p002','bbb'
union all select 3,'p002','ccc'if object_id('get_str') is not null drop function get_str
go
create function get_str(@a varchar(10))
returns varchar(30)
as
begin
declare @s varchar(30)
select @s=isnull(@s+',','')+[address] from t2 where [personid]=@a
return @s
end
goselect *,dbo.get_str([personid]) as addrss from t1/*
personid name addrss
---------- ---------- ------------------------------
p001 001 aaa
p002 002 bbb,ccc
p003 003 NULL(3 行受影响)
*/
go
create table [t1]([personid] varchar(10),[name] varchar(10))
insert [t1] select 'p001','001'
union all select 'p002','002'
union all select 'p003','003'if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([id] int,[personid] varchar(10),[address] varchar(10))
insert [t2] select 1,'p001','aaa'
union all select 2,'p002','bbb'
union all select 3,'p002','ccc'if object_id('get_str') is not null drop function get_str
go
create function get_str(@a varchar(10))
returns varchar(30)
as
begin
declare @s varchar(30)
select @s=isnull(@s+'+','')+[address] from t2 where [personid]=@a
return @s
end
goselect *,dbo.get_str([personid]) as addrss from t1/*
personid name addrss
---------- ---------- ------------------------------
p001 001 aaa
p002 002 bbb+ccc
p003 003 NULL(3 行受影响)
*/逗号改加号