代码
create proc select_course
@pn varchar(15),@sn int,@result int output
as
begin
if(select count(*) from scores where sno=@sn)>(select slimits from subjects where sno=@sn)
set @result=0
else
if exists(select grade from scores where sno=@sn and pno=@pn) set @result=1
else
if(select count(*) from scores where pno=@pn and sno in (select sno from subjects where verifyflag=1))>=3
set @result=2
else
begin
insert into scores(pno,sno) values(@pn,@sn)
set @result=3
end
end
GOcreate view s_total
AS
select Gives.TERM,Gives.sno,Subjects.SNAME,Teachers.TNAME,
Departments.DNAME,Subjects.verifyflag,COUNT(*) as pn
from Teachers INNER JOIN
Gives on Teachers.TNO=Gives.TNO inner JOIN
Subjects on Gives.SNO=Subjects.SNO inner join
Departments on Teachers.dno=Departments.dno inner join
Scores ON Gives.SNO=Scores.SNO and
Subjects.verifyflag<>0
group by Gives.TERM,Gives.sno,Subjects.SNAME,Teachers.TNAME,
Departments.DNAME,Subjects.verifyflag
CREATE VIEW noselected
AS
SELECT Gives.TERM,Gives.SNO,Subjects.SNAME,Teachers.TNAME,
Departments.DNAME,0 AS pn
FROM Gives INNER JOIN Subjects ON Gives.SNO=Subjects.SNO
INNER JOIN Teachers ON Gives.TNO=Teachers.TNO
INNER JOIN Departments ON Teachers.DNO=Departments.DNO
WHERE (Subjects.verifyflag=1) AND (Gives.SNO NOT IN (SELECT sno FROM s_total))
create proc select_course
@pn varchar(15),@sn int,@result int output
as
begin
if(select count(*) from scores where sno=@sn)>(select slimits from subjects where sno=@sn)
set @result=0
else
if exists(select grade from scores where sno=@sn and pno=@pn) set @result=1
else
if(select count(*) from scores where pno=@pn and sno in (select sno from subjects where verifyflag=1))>=3
set @result=2
else
begin
insert into scores(pno,sno) values(@pn,@sn)
set @result=3
end
end
GOcreate view s_total
AS
select Gives.TERM,Gives.sno,Subjects.SNAME,Teachers.TNAME,
Departments.DNAME,Subjects.verifyflag,COUNT(*) as pn
from Teachers INNER JOIN
Gives on Teachers.TNO=Gives.TNO inner JOIN
Subjects on Gives.SNO=Subjects.SNO inner join
Departments on Teachers.dno=Departments.dno inner join
Scores ON Gives.SNO=Scores.SNO and
Subjects.verifyflag<>0
group by Gives.TERM,Gives.sno,Subjects.SNAME,Teachers.TNAME,
Departments.DNAME,Subjects.verifyflag
CREATE VIEW noselected
AS
SELECT Gives.TERM,Gives.SNO,Subjects.SNAME,Teachers.TNAME,
Departments.DNAME,0 AS pn
FROM Gives INNER JOIN Subjects ON Gives.SNO=Subjects.SNO
INNER JOIN Teachers ON Gives.TNO=Teachers.TNO
INNER JOIN Departments ON Teachers.DNO=Departments.DNO
WHERE (Subjects.verifyflag=1) AND (Gives.SNO NOT IN (SELECT sno FROM s_total))
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货