两个表联合查询

[zyskjfsj]

table1 table2
id fieldX fieldY id A B C
1 1,2,3 2,3 1 fieldX Xtest1 1
2 2 1,3 2 fieldX Xtest2 2
3 fieldX Xtest3 3
4 fieldY Ytest1 1
5 fieldY Ytest2 2
6 fieldY Ytest3 3
7 fieldY Ytest4 4

最后结果
id fieldX fieldY
1 Xtest1，Xtest2，Xtest3 Ytest2，Ytest3
2 Xtest2 Ytest1，Ytest3

[zyskjfsj]

mark

[xluzhong]

/*
table1 table2
id fieldX fieldY id A B C
1 1,2,3 2,3 1 fieldX Xtest1 1
2 2 1,3 2 fieldX Xtest2 2
3 fieldX Xtest3 3
4 fieldY Ytest1 1
5 fieldY Ytest2 2
6 fieldY Ytest3 3
7 fieldY Ytest4 4

最后结果
id fieldX fieldY
1 Xtest1，Xtest2，Xtest3 Ytest2，Ytest3
2 Xtest2 Ytest1，Ytest3
*/
create table a(id int,fieldx nvarchar(10),fieldy nvarchar(10))
insert into a select 1,'1,2,3','2,3' union all select 2,'2','1,3'

create table b(id int identity(1,1),a nvarchar(100),b nvarchar(100),c int)
insert into b select 'fieldx','xtest1',1
union all select 'fieldx','xtest2',2
union all select 'fieldx','xtest3',3
union all select 'fieldy','ytest1',1
union all select 'fieldy','ytest2',2
union all select 'fieldy','ytest3',3
union all select 'fieldy','ytest4',4

go
create function fieldx(@fieldx nvarchar(100))
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
set @s=''
select @s=@s+','+B from b where a='fieldx' and charindex(','+cast(c as varchar(10))+',',','+@fieldx+',')>0
return(stuff(@s,1,1,''))
end
go

create function fieldy(@fieldy nvarchar(100))
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
set @s=''
select @s=@s+','+B from b where a='fieldy' and charindex(','+cast(c as varchar(10))+',',','+@fieldy+',')>0
return(stuff(@s,1,1,''))
end
go

select id,dbo.fieldx(fieldx),dbo.fieldy(fieldy)
from a

drop function fieldx,fieldy
drop table a,b

[paoluo]

一个函数就可以搞定了。

[paoluo]

--建立测试环境
Create table a(id int,fieldX nvarchar(10),fieldY nvarchar(10))
Create table b(id int identity(1,1),a nvarchar(100),b nvarchar(100),c int)

--插入数据
Insert into a select 1,'1,2,3','2,3' union all select 2,'2','1,3'
Insert into b select 'fieldx','Xtest1',1
Union all select 'fieldx','Xtest2',2
Union all select 'fieldx','Xtest3',3
Union all select 'fieldy','Ytest1',1
Union all select 'fieldy','Ytest2',2
Union all select 'fieldy','Ytest3',3
Union all select 'fieldy','Ytest4',4
GO
--建立函数
Create Function Getfield(@field nvarchar(100),@Bit Bit)
Returns nvarchar(100)
AS
Begin
Declare @s nvarchar(100)
Set @s=''
If @bit=0
Select @s=@s+','+B from b where a='fieldX' and charindex(','+cast(c as varchar(10))+',',','+@field+',')>0
If @bit=1
Select @s=@s+','+B from b where a='fieldY' and charindex(','+cast(c as varchar(10))+',',','+@field+',')>0
Return(stuff(@s,1,1,''))
End
GO
--测试
Select id,dbo.Getfield(fieldx,0) As fieldX,dbo.Getfield(fieldy,1) As fieldY from a
--删除测试环境
Drop table a,b
Drop Function Getfield
--结果
/*
id fieldX fieldy
1 Xtest1,Xtest2,Xtest3 Ytest2,Ytest3
2 Xtest2 Ytest1,Ytest3
*/

[heguosheng]

mark