数据:
Table1:id C1
-----------
1 cc
2 cdTable2:id TypeIndex
-----------------
1 1
1 2
1 3Table3:
TypeIndex Type
-----------------------
1 a
2 b
3 c
4 d要求获得如下数据:id C1 StrType
------------------------
1 cc a;b;c
2 cd null有正解即结贴。请牛人帮忙
Table1:id C1
-----------
1 cc
2 cdTable2:id TypeIndex
-----------------
1 1
1 2
1 3Table3:
TypeIndex Type
-----------------------
1 a
2 b
3 c
4 d要求获得如下数据:id C1 StrType
------------------------
1 cc a;b;c
2 cd null有正解即结贴。请牛人帮忙
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select
@re=@re+';'+t3.type
from t1 inner join t2 on t1.id=t2.id
inner join t3 on t2.typeindex=t3.typeindex
return stuff(@re,1,1,'')
end
go
select id,c1,strtype=dbo.f1(id)
from t1
create function f1(@id int)
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select
@re=@re+';'+t3.type
from t1 inner join t2 on t1.id=t2.id
inner join t3 on t2.typeindex=t3.typeindex
where t1.id=@id
return stuff(@re,1,1,'')
end
go
select id,c1,strtype=dbo.f1(id)
from t1
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select
@re=@re+';'+t3.type
from t2 inner join t3 on t2.typeindex=t3.typeindex
where t2.id=@id
return stuff(@re,1,1,'')
end
go
select id,c1,strtype=dbo.f1(id)
from t1
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select
@re=@re+';'+t3.type
from t1 inner join t2 on t1.id=t2.id
inner join t3 on t2.typeindex=t3.typeindex
where t1.id=@id
return stuff(@re,1,1,'')
end
go
select id,c1,strtype=dbo.f1(id)
from t1