请问一下,这个Sp怎么写

[Lansie]

有3张表
课程表:course_id,course_name,lecturer_id(课程id,课程名,讲师id)
讲师表:lecturer_id,lecturer_name(讲师id,讲师名)
上课学生表:course_id,student_id(课程id,学生id)

现在要写一个sp,参数为course_id,要求返回course_name,讲师名和学生数目

该怎么写呢,谢谢了!

[chuifengde]

select course_name,min(lecturer_name) Lecturer_name,count(student_id) num from 课程表 a Inner Join 讲师表 b On a.Lecturer_id=b.Lecturer_id Inner Join 上课学生表 c On a.course_id=c.course_id Group by course_name

[baoshan]

create proc sp_test
@course_id int,
@coursename varchar(20) output,
@teachername varchar(20) output,
@stcount int output
as
select @coursename=a.course_name,@teachername=lecturer_name from 课程表 a inner join 讲师表 b on a.lecturer_id=b.lecturer_id where a.course_id=@course_id
select @stcount=count(1) from 上课学生表 where course_id=@course_id
return

[Lansie]

谢谢各位,看的有点头晕,消化一下先

[弘毅致远]

create table course(course_id int,course_name varchar(20),lecturer_id int)
create table lecturer(lecturer_id int,lecturer_name varchar(20))
create table stu_on_study(course_id int,student_id int)

insert into course select 1,'aa',11
union all select 2,'bb',22
union all select 3,'cc',33
union all select 4,'dd',44

insert into lecturer select 11,'zhangl'
union all select 22,'wangm'
union all select 33,'liul'
union all select 44,'lim'

insert into stu_on_study select 1,111
union all select 2,222
union all select 3,333
union all select 4,444

create proc test(
@course_id int,
@course_name varchar(20) output,
@lecturer_name varchar output,
@stu_num int output)
as
select @course_name=course_name,@lecturer_name=lecturer_name from course a inner join lecturer b on
a.lecturer_id = b.lecturer_id where course_id = @course_id
select @stu_num = count(*) from stu_on_study where course_id = @course_id
return

exec test 1,'','',''

The command(s) completed successfully.

[弘毅致远]

基本上就像上面这样子了.