表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
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
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,'李四')
goif 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)
goif object_id(N'dbo.GatherStr',N'FN') is not null
drop function dbo.GatherStr
gocreate 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
goselect
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
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