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,已获总学分,应获总学分,获取比例
这要怎么做呢?我只会放开写,请哪位帮我一下,先谢谢了

解决方案 »

  1.   

    CREATE PROCEDURE SP_ClassGradeQuery(i_TeachingYearId IN CHAR(2),
                                        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;
      

  2.   

    CREATE PROCEDURE SP_ClassGradeQuery(i_TeachingYearId IN CHAR(2),
                                        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;
      

  3.   

    参考语句:
    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
      

  4.   

    最简单的方法,但是比较慢
    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