用三张表做关连,希望得出
--------------------------------------------
TA_ID TA_NAME TB_NAME_STR
--------------------------------------------
0001 TA1 TB1,TB2
0002 TA2 TB2
0003 TA3
-------------------------------------------------是
--------------------------------------------
TA_ID TA_NAME TB_NAME_STR
--------------------------------------------
0001 TA1 TB1,TB2
0002 TA2 TB1
0003 TA3吧?
--------------------------------------------
TA_ID TA_NAME TB_NAME_STR
--------------------------------------------
0001 TA1 TB1,TB2
0002 TA2 TB2
0003 TA3
-------------------------------------------------是
--------------------------------------------
TA_ID TA_NAME TB_NAME_STR
--------------------------------------------
0001 TA1 TB1,TB2
0002 TA2 TB1
0003 TA3吧?
go
create table tb(tb_id varchar(50),tb_tablename varchar(50))
go
create table tatb(ta_id varchar(50),tb_id varchar(50))
go--测试数据
insert into tatb
select '0001','001' union
select '0001','002' union
select '0002','001'
goinsert into tb
select '001','tb1'
union select '002','tb2'
goinsert into ta
select '0001','ta1' union
select '0002','ta2' union
select '0003','ta3'
go--创建一个函数
create function fser(@a varchar(50))
returns varchar(2000)
as
begin
declare @c varchar(50)
select @c=''
select @c=@c+b.tb_tablename+',' from tatb a inner join tb b on a.tb_id=b.tb_id where a.ta_id=@a
if @c<>''
select @c=left(@c,len(@c)-1)
if @c=''
select @c=null
return @c
end
go
select distinct a.ta_id,a.ta_tablename,dbo.fser(a.ta_id) from ta a left join tatb b on a.ta_id=b.ta_id left join tb c on b.tb_id=c.tb_id
go
drop function fser
drop table ta
drop table tb
drop table tatb测试通过,你试试看.
两年后,我想出来了. 那位能解决么?