求一个SELECT语句！

[seaman0220]

有如下两张表：

表A：
字段名：a1、a2、a3、a4
值： 1 1 a b
2 2 c d

表B：
字段名：b1、b2
值： 1 m
1 n
2 j
2 k

表A的a2和表B的b1是关联的。

现在我想取出表A的所有字段值(其中表A的a2字段取出的是b2)

这样的SELECT的语句应该怎么样写啊？

我想实现的效果为：
字段名：a1 a2 a3 a4
值: 1 m,n a b
2 j,k c d

[Andy__Huang]

--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[b2]+',' from tb where a1=@a
return (left(@s,len(@s)-1))
end


select a1,a2=dbo.fn_b(a2),a3,a4 from ta

[xluzhong]

create function f_geta2(@a nvarchar(10))
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+[b2] from 表b where a1=@a
return (stuff(@s,1,1,''))
end


select distinct a1,dbo.f_geta2(a2) as a2,a3,a4 from 表a

[lxzm1001]

select a.a1,b.b2,a.a3,a.a4 from a inner join b on b.b1 = a.a2
这个不对吧

[lxzm1001]

create table table1
(al int,
a2 int,
a3 varchar(10),
a4 varchar(10))
insert into table1 select 1,1,'a','b'
union all select 2,2,'c','d'
create table table2
(
b1 int,b2 varchar(10))
insert into table2 select 1,'m'
union all select 1,'n'
union all select 2,'j'
union all select 2,'k'

[lishengyu]

create table table1
(a1 int,
a2 int,
a3 varchar(10),
a4 varchar(10))
insert into table1 select 1,1,'a','b'
union all select 2,2,'c','d'

create table table2
(
b1 int,b2 varchar(10))
insert into table2 select 1,'m'
union all select 1,'n'
union all select 2,'j'
union all select 2,'k'

create table #b (c1 int ,c2 varchar(7999))
insert into #b select * from table2 order by b1
declare @y varchar(100)
declare @x varchar(7999)
update #b set @x=case when @y=c1 then @x+','+c2 else c2 end, @y=c1,c2=@x
select c1,max(c2)c2 into #c from #b group by c1
select a.a1,b.c2,a.a3,a.a4 from table1 a join #c b on a.a2=b.c1
drop table #b,#c,table1,table2



a1 a2 a3 a4
----------- ------------------------------ ---------- ----------
1 m,n a b
2 j,k c d

（所影响的行数为 2 行）

[seaman0220]

怎么都没人回复啊？

[harryCom]

select a.a1,b.b2,a.a3,a.a4 from a inner join b on b.b1 = a.a2

[Andy__Huang]

這個要用函數處理