create table A(id int, Name nvarchar(10)) insert A select 1, '张三' union all select 2, '李四' union all select 3, '王五'create table B(id int, Course nvarchar(10)) insert B select 1, '语文' union all select 2, '数学' union all select 3, '英语'create table C(AID int, BID int) insert C select 1, 1 union all select 1, 2 union all select 2, 1 union all select 2, 3 union all select 3, 2 union all select 3, 3--1 create function fun1(@AID int) returns nvarchar(500) as begin declare @re nvarchar(500) set @re=''
select @re=@re+B.Course+',' from B inner join C on B.id=C.BID and AID=@AID select @re=left(@re, len(@re)-1) return @re endselect Name, dbo.fun1(id) as Course from A --result Name Course ---------- ------------------ 张三 语文,数学 李四 语文,英语 王五 数学,英语(3 row(s) affected) --2 create function fun2(@BID int) returns nvarchar(500) as begin declare @re nvarchar(500) set @re=''
select @re=@re+A.Name+',' from A inner join C on A.id=C.AID and BID=@BID
select @re=left(@re, len(@re)-1) return @re endselect Course, dbo.fun2(id) as Name from B --result Course Name ---------- ---------------- 语文 张三,李四 数学 张三,王五 英语 李四,王五(3 row(s) affected)
insert A select 1, '张三'
union all select 2, '李四'
union all select 3, '王五'create table B(id int, Course nvarchar(10))
insert B select 1, '语文'
union all select 2, '数学'
union all select 3, '英语'create table C(AID int, BID int)
insert C select 1, 1
union all select 1, 2
union all select 2, 1
union all select 2, 3
union all select 3, 2
union all select 3, 3--1
create function fun1(@AID int)
returns nvarchar(500)
as
begin
declare @re nvarchar(500)
set @re=''
select @re=@re+B.Course+',' from B inner join C on B.id=C.BID and AID=@AID select @re=left(@re, len(@re)-1) return @re
endselect Name, dbo.fun1(id) as Course from A
--result
Name Course
---------- ------------------
张三 语文,数学
李四 语文,英语
王五 数学,英语(3 row(s) affected)
--2
create function fun2(@BID int)
returns nvarchar(500)
as
begin
declare @re nvarchar(500)
set @re=''
select @re=@re+A.Name+',' from A inner join C on A.id=C.AID and BID=@BID
select @re=left(@re, len(@re)-1) return @re
endselect Course, dbo.fun2(id) as Name from B
--result
Course Name
---------- ----------------
语文 张三,李四
数学 张三,王五
英语 李四,王五(3 row(s) affected)