--drop table a create table A(id int, studentname varchar(20)) insert into A select 1,'sa' insert into A select 2,'sb' insert into A select 3,'sc' insert into A select 4,'sd' --drop table b create table b(id int,subject varchar(20)) insert into b select 1,'math' insert into b select 2,'englist' insert into b select 3,'computing' insert into b select 4,'history' --drop table c create table c(studentid int,subjectid int) insert into c select 1,1 insert into c select 1,2 insert into c select 2,4 insert into c select 3,2 insert into c select 3,3 insert into c select 4,1 --drop table d create table d(id int, teachername varchar(20)) insert into d select 1,'ta' insert into d select 2,'tb' insert into d select 3,'tc' insert into d select 4,'td' --drop table e create table e(studentid int, teacherid int) insert into e select 1,1 insert into e select 1,2 insert into e select 2,2 insert into e select 3,3 insert into e select 3,4 insert into e select 4,1 select a.studentname,dbo.f_getteachername(id) as stuteacher,dbo.f_getsubject(id)as stusubject from a /* ------------------------------------------- sa ta,tb math,englist sb tb history sc tc,td englist,computing sd ta math */--需要以下两个函数:----------------------返回学生的老师 create function f_getteachername(@studentid int) returns varchar(8000) as begin declare @s varchar(8000) set @s='' select @s=@s+teachername+',' from e inner join d on d.id=e.teacherid and e.studentid=@studentid if len(@s)>0 set @s=left(@s,len(@s)-1) return @s end--返回学生的学习科目 create function f_getsubject(@studentid int) returns varchar(8000) as begin declare @s varchar(8000) set @s='' select @s=@s+subject+',' from c inner join b on b.id=c.subjectid and c.studentid=@studentid if len(@s)>0 set @s=left(@s,len(@s)-1) return @s end
--drop table a create table A(id int, studentname varchar(20)) insert into A select 1,'sa' insert into A select 2,'sb' insert into A select 3,'sc' insert into A select 4,'sd' --drop table b create table b(id int,subject varchar(20)) insert into b select 1,'math' insert into b select 2,'englist' insert into b select 3,'computing' insert into b select 4,'history' --drop table c create table c(studentid int,subjectid int) insert into c select 1,1 insert into c select 1,2 insert into c select 2,4 insert into c select 3,2 insert into c select 3,3 insert into c select 4,1 --drop table d create table d(id int, teachername varchar(20)) insert into d select 1,'ta' insert into d select 2,'tb' insert into d select 3,'tc' insert into d select 4,'td' --drop table e create table e(studentid int, teacherid int) insert into e select 1,1 insert into e select 1,2 insert into e select 2,2 insert into e select 3,3 insert into e select 3,4 insert into e select 4,1--创建合并函数 create function f_name(@studentid int,@type int) returns varchar(1000) as begin declare @s varchar(1000) if(@type=1) select @s=isnull(@s+',','')+teachername from e,d where id=teacherid and studentid=@studentid else select @s=isnull(@s+',','')+subject from c,b where id=subjectid and studentid=@studentid return @s end go--调用: select studentname,teachername=dbo.f_name(id,1),subject=dbo.f_name(id,2) from a
参考一下:
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
--drop table a
create table A(id int, studentname varchar(20))
insert into A select 1,'sa'
insert into A select 2,'sb'
insert into A select 3,'sc'
insert into A select 4,'sd'
--drop table b
create table b(id int,subject varchar(20))
insert into b select 1,'math'
insert into b select 2,'englist'
insert into b select 3,'computing'
insert into b select 4,'history'
--drop table c
create table c(studentid int,subjectid int)
insert into c select 1,1
insert into c select 1,2
insert into c select 2,4
insert into c select 3,2
insert into c select 3,3
insert into c select 4,1
--drop table d
create table d(id int, teachername varchar(20))
insert into d select 1,'ta'
insert into d select 2,'tb'
insert into d select 3,'tc'
insert into d select 4,'td'
--drop table e
create table e(studentid int, teacherid int)
insert into e select 1,1
insert into e select 1,2
insert into e select 2,2
insert into e select 3,3
insert into e select 3,4
insert into e select 4,1
select a.studentname,dbo.f_getteachername(id) as stuteacher,dbo.f_getsubject(id)as stusubject from a
/*
-------------------------------------------
sa ta,tb math,englist
sb tb history
sc tc,td englist,computing
sd ta math
*/--需要以下两个函数:----------------------返回学生的老师
create function f_getteachername(@studentid int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+teachername+',' from e inner join d on d.id=e.teacherid and e.studentid=@studentid
if len(@s)>0 set @s=left(@s,len(@s)-1)
return @s
end--返回学生的学习科目
create function f_getsubject(@studentid int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+subject+',' from c inner join b on b.id=c.subjectid and c.studentid=@studentid
if len(@s)>0 set @s=left(@s,len(@s)-1)
return @s
end
--drop table a
create table A(id int, studentname varchar(20))
insert into A select 1,'sa'
insert into A select 2,'sb'
insert into A select 3,'sc'
insert into A select 4,'sd'
--drop table b
create table b(id int,subject varchar(20))
insert into b select 1,'math'
insert into b select 2,'englist'
insert into b select 3,'computing'
insert into b select 4,'history'
--drop table c
create table c(studentid int,subjectid int)
insert into c select 1,1
insert into c select 1,2
insert into c select 2,4
insert into c select 3,2
insert into c select 3,3
insert into c select 4,1
--drop table d
create table d(id int, teachername varchar(20))
insert into d select 1,'ta'
insert into d select 2,'tb'
insert into d select 3,'tc'
insert into d select 4,'td'
--drop table e
create table e(studentid int, teacherid int)
insert into e select 1,1
insert into e select 1,2
insert into e select 2,2
insert into e select 3,3
insert into e select 3,4
insert into e select 4,1--创建合并函数
create function f_name(@studentid int,@type int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
if(@type=1)
select @s=isnull(@s+',','')+teachername from e,d where id=teacherid and studentid=@studentid
else
select @s=isnull(@s+',','')+subject from c,b where id=subjectid and studentid=@studentid
return @s
end
go--调用:
select studentname,teachername=dbo.f_name(id,1),subject=dbo.f_name(id,2) from a