CREATE PROCEDURE SP_ClassGradeQuery
(TeachingYearId in char(2) ,classid in char(7))
as
begin
begin
select Tb_Student.stuid ,stuName ,sum(Tb_Course.CourseGrade)as 应获总学分
from Tb_Grade,Tb_Student ,Tb_Course
where Tb_Student.stuid = Tb_Grade.stuid and Tb_Grade.CourseId=Tb_Course.CourseId and tb_grade.classid =classid and tb_grade.TeachingYearId=TeachingYearId
group by Tb_Student.stuid ,stuName
end
select Tb_Student.stuid ,stuName ,sum(Tb_Course.CourseGrade)as 已学分
from Tb_Grade,Tb_Student ,Tb_Course
where Tb_Student.stuid = Tb_Grade.stuid and Tb_Grade.CourseId=Tb_Course.CourseId
and (CommonScore*0.1+MiddleScore*0.2+LastScore*0.7)>60 and tb_grade.classid =classid and tb_grade.TeachingYearId=TeachingYearId
group by Tb_Student.stuid ,stuName
end我想查询出的结果是:StuId,StuName,已获总学分,应获总学分,获取比例
这要怎么做呢?我只会放开写,请哪位帮我一下,先谢谢了
(TeachingYearId in char(2) ,classid in char(7))
as
begin
begin
select Tb_Student.stuid ,stuName ,sum(Tb_Course.CourseGrade)as 应获总学分
from Tb_Grade,Tb_Student ,Tb_Course
where Tb_Student.stuid = Tb_Grade.stuid and Tb_Grade.CourseId=Tb_Course.CourseId and tb_grade.classid =classid and tb_grade.TeachingYearId=TeachingYearId
group by Tb_Student.stuid ,stuName
end
select Tb_Student.stuid ,stuName ,sum(Tb_Course.CourseGrade)as 已学分
from Tb_Grade,Tb_Student ,Tb_Course
where Tb_Student.stuid = Tb_Grade.stuid and Tb_Grade.CourseId=Tb_Course.CourseId
and (CommonScore*0.1+MiddleScore*0.2+LastScore*0.7)>60 and tb_grade.classid =classid and tb_grade.TeachingYearId=TeachingYearId
group by Tb_Student.stuid ,stuName
end我想查询出的结果是:StuId,StuName,已获总学分,应获总学分,获取比例
这要怎么做呢?我只会放开写,请哪位帮我一下,先谢谢了
i_classid IN CHAR(7),
o_ref OUT SYS_REFCURSOR) AS
BEGIN
--返回游标
OPEN o_ref FOR
SELECT stuid,
stuName,
CG AS 已获学分,
totoalCG AS 应获总学分,
CG / totalCG AS 获取比例
FROM (SELECT Tb_Student.stuid,
stuName,
SUM(CASE
WHEN (CommonScore * 0.1 + MiddleScore * 0.2 +
LastScore * 0.7) > 60 THEN
Tb_Course.CourseGrade
ELSE
0
END) CG,
SUM(Tb_Course.CourseGrade) totoalCG
FROM Tb_Grade, Tb_Student, Tb_Course
WHERE Tb_Student.stuid = Tb_Grade.stuid
AND Tb_Grade.CourseId = Tb_Course.CourseId
AND tb_grade.classid = i_classid
AND tb_grade.TeachingYearId = i_TeachingYearId
GROUP BY Tb_Student.stuid, stuName);
END;
i_classid IN CHAR(7),
o_ref OUT SYS_REFCURSOR) AS
BEGIN
--返回游标
OPEN o_ref FOR
SELECT stuid,
stuName,
CG AS 已获学分,
totoalCG AS 应获总学分,
CG / totalCG AS 获取比例
FROM (SELECT Tb_Student.stuid,
stuName,
SUM(CASE
WHEN (CommonScore * 0.1 + MiddleScore * 0.2 +
LastScore * 0.7) > 60 THEN
Tb_Course.CourseGrade
ELSE
0
END) CG,
SUM(Tb_Course.CourseGrade) totoalCG
FROM Tb_Grade, Tb_Student, Tb_Course
WHERE Tb_Student.stuid = Tb_Grade.stuid
AND Tb_Grade.CourseId = Tb_Course.CourseId
AND tb_grade.classid = i_classid
AND tb_grade.TeachingYearId = i_TeachingYearId
GROUP BY Tb_Student.stuid, stuName);
END;
select a.stuid ,a.stuName,a.应获总学分,b.已学分,(100*b.已学分/a.应获总学分) as 获取比例
from (select Tb_Student.stuid ,stuName ,sum(Tb_Course.CourseGrade)as 应获总学分
from Tb_Grade,Tb_Student ,Tb_Course
where Tb_Student.stuid = Tb_Grade.stuid and Tb_Grade.CourseId=Tb_Course.CourseId and tb_grade.classid =classid and tb_grade.TeachingYearId=TeachingYearId
group by Tb_Student.stuid ,stuName) a,
(select Tb_Student.stuid ,stuName ,sum(Tb_Course.CourseGrade)as 已学分
from Tb_Grade,Tb_Student ,Tb_Course
where Tb_Student.stuid = Tb_Grade.stuid and Tb_Grade.CourseId=Tb_Course.CourseId
and (CommonScore*0.1+MiddleScore*0.2+LastScore*0.7)>60 and tb_grade.classid =classid and tb_grade.TeachingYearId=TeachingYearId
group by Tb_Student.stuid ,stuName ) b
where a.stuid=b.stuid
select a.stuid ,a.stuName,a.应获总学分,b.已学分,(100*b.已学分/a.应获总学分) as 获取比例
from (select Tb_Student.stuid ,stuName ,sum(Tb_Course.CourseGrade)as 应获总学分
from Tb_Grade,Tb_Student ,Tb_Course
where Tb_Student.stuid = Tb_Grade.stuid and Tb_Grade.CourseId=Tb_Course.CourseId and tb_grade.classid =classid and tb_grade.TeachingYearId=TeachingYearId
group by Tb_Student.stuid ,stuName) a,
(select Tb_Student.stuid ,stuName ,sum(Tb_Course.CourseGrade)as 已学分
from Tb_Grade,Tb_Student ,Tb_Course
where Tb_Student.stuid = Tb_Grade.stuid and Tb_Grade.CourseId=Tb_Course.CourseId
and (CommonScore*0.1+MiddleScore*0.2+LastScore*0.7)>60 and tb_grade.classid =classid and tb_grade.TeachingYearId=TeachingYearId
group by Tb_Student.stuid ,stuName ) b
where a.stuid=b.stuid