sql 两张表之间的查询

[低调的感觉]

表A：
A_ID A_Name
1 张三
2 李四


表B：
B_ID A_ID B_Name B_UsageExperience
1 1 Java 18
2 1 MySql 10
3 2 C# 20
4 2 MsSql 15
5 1 Oracle 6

我想根据这两张表得到一下结果：
T_Result：
Name Skill
张三 Java/18,MySql/10,Oracle/6
李四 C#/20,MsSql/15

[hao1hao2hao3]

这两天好多这样的帖子

参考：

http://topic.csdn.net/u/20100821/11/8b1362ea-a8e2-453f-aace-a1711f9a662b.html?42625

[hyde100]

if object_id(N'dbo.A',N'U') is not null
	drop table dbo.A
go
create table A
(
	A_ID int,
	A_Name nvarchar(50)
)

insert into A(A_ID,A_Name) values(1,'张三') 
insert into A(A_ID,A_Name) values(2,'李四')
go

if object_id(N'dbo.B',N'U') is not null
	drop table dbo.B
go
create table B
(
	B_ID int, 
	A_ID int, 
	B_Name nvarchar(50),
	B_UsageExperience int	
)

insert into B(B_ID,A_ID,B_Name,B_UsageExperience) values(1,1,'Java',18)
insert into B(B_ID,A_ID,B_Name,B_UsageExperience) values(2, 1,'MySql',10)
insert into B(B_ID,A_ID,B_Name,B_UsageExperience) values(3, 2,'C#',20)
insert into B(B_ID,A_ID,B_Name,B_UsageExperience) values(4, 2,'MsSql',15)
insert into B(B_ID,A_ID,B_Name,B_UsageExperience) values(5, 1,'Oracle',6)
go

if object_id(N'dbo.GatherStr',N'FN') is not null
	drop function dbo.GatherStr
go

create function GatherStr(@iAid int)
returns nvarchar(4000)
as
begin
	declare @str nvarchar(4000)
	set @str = ''

	select @str = @str + ',' + ltrim(B_Name) + '/' + ltrim(B_UsageExperience)
	from B
	where A_ID = @iAid

	return stuff(@str,1,1,'')
end
go

select
	A.A_Name,
	dbo.GatherStr(A.A_ID)
from A,B
where A.A_ID = B.B_ID
group by A.A_ID,
	A.A_Name

[cbgn]

create table A
(A_ID int  
,A_Name varchar(20))

create table B
(
B_ID int ,
A_ID int ,
B_Name varchar(20),
 B_UsageExperience int )

insert A values(1, '张三' )
insert A values(2, '李四' )

insert b values(1 ,1 ,'Java' ,18 )
insert b values(2, 1, 'MySql' ,10 )
insert b values(3, 2, 'C#', 20 )
insert b values(4, 2 ,'MsSql' ,15 )
insert b values(5,1, 'Oracle', 6 )


select fname,stuff(fvalue,len(fvalue),1,'') from (
select newA.A_Name+' ' as fname,(
select b.B_Name+'/',convert(varchar(10),b.B_UsageExperience )+','
from a join b on a.A_ID=b.A_ID where a.A_ID =newA.A_ID for xml path('')
)as fvalue
from a as newA group by newA.A_ID,newA.A_Name
)
t