学生表Student（sno，sname，ssex，sage）
课程表Lesoon（lno，lname，lcredit）
选课表SC（sno，cno，grade）
这是我开始写的：
if (exists (select * from sys.objects where name = 'proc_stu'))
drop proc proc_stu
go
create proc proc_stu(@sname varchar(8) ='张%')
as
select Student.sno,sname,lname,grade
from Student,SC,Lesson
where Student.sno=SC.sno and SC.lno=Lesson.lno and
sname=@sname
go
由于存在有学生没有选课，所以上面的代码就有问题，参数为没有选课的学生的姓名时，查询结果中就什么都没有。
理想的结果应该是：无论该学生选没选课，结果至少要能有学号和姓名显示，课程名和成绩有就显示，没有就为空
if (exists (select * from sys.objects where name = 'proc_stu'))
drop proc proc_stu
go
create proc proc_stu(@sname varchar(8) ='张%')
as
select Student.sno,sname,isnull(lname,'') as lname,isnull(grade,0) as grade
from Student left join SC on Student.sno=SC.sno
left join Lesson on SC.lno=Lesson.lno and
where SC.sname=@sname
go
drop proc proc_stu
go
create proc proc_stu(@sname varchar(8) ='张%')
as
select Student.sno,sname,isnull(lname,'') as lname,isnull(grade,0) as grade
from Student left join SC on Student.sno=SC.sno
left join Lesson on SC.lno=Lesson.lno and
where SC.sname=@sname
go
温馨提示：答案为网友推荐，仅供参考
第1个回答 2014-11-06
if (exists (select * from sys.objects where name = 'proc_stu'))
drop proc proc_stu
go
create proc proc_stu(@sname varchar(8) ='张%')
as
select Student.sno,sname,lname,grade
from Student left join SC on Student.sno=SC.sno
left join Lesson on SC.lno=Lesson.lno
where sname like @sname
go
drop proc proc_stu
go
create proc proc_stu(@sname varchar(8) ='张%')
as
select Student.sno,sname,lname,grade
from Student left join SC on Student.sno=SC.sno
left join Lesson on SC.lno=Lesson.lno
where sname like @sname
go
