学生成绩数据库设计四解答问题的存储过程

问题1解答

 1 /*通过输入学生的学号或者姓名（两者方式均可），显示该生的所有成绩记录的结果集*/
 2 
 3 CREATE PROCEDURE SelectScores
 4 (
 5     @Args nvarchar(30) /*查询参数：学号或姓名*/
 6 ) as
 7 SELECT Student.StuNO '学号',
 8         Student.StuName '姓名',
 9         Convert(varchar(30),SchoolYear.SyStartYear)
10         +'学年--'
11         +Convert(varchar(30),SchoolYear.SyEndYear)+'学年' as '学年',
12         Course.CourseName '课程',
13         Score.ScoreValue '成绩'
14 FROM Student
15 LEFT JOIN Score ON Score.StuNO=Student.StuNO 
16 LEFT JOIN SchoolYear ON SchoolYear.SyID=Score.SyID
17 LEFT JOIN Course ON Course.CourseID=Score.CourseID
18 where Student.StuNO=@Args
19       or Student.StuName=@Args

问题2解答

 1 /*通过输入学生的学号和某门课程的名称，通过输出参数返回该生在该门课程考试中的名次*/
 2 
 3 /*条件：学号，课程名称*/
 4 /*输出：学号，学生姓名，课程名称，学年，名次*/
 5 
 6 /*排序根据：学年，课程，成绩*/
 7 /*排序一：row_number() over(partition by Score.SyID,Score.CourseID order by Score.ScoreValue desc) 
 8             分组排序，不显示并列名次
 9     排序二：rank() over(partition by Score.SyID,Score.CourseID order by Score.ScoreValue desc)
10             分组排序，显示并列名称
11 */
12 ALTER PROCEDURE SelectCjPaiming
13 (
14     @StuNO NVARCHAR(12),/*学号*/
15     @CourseName nvarchar(30) /*课程名称*/
16 ) as
17 SELECT T1.ScoreID AS '成绩ID',
18         Student.StuNO AS '学号',
19         Student.StuName AS '姓名',
20         CONVERT(VARCHAR(4),SchoolYear.SyStartYear)+'--'+CONVERT(varchar(4),SchoolYear.SyEndYear)+'学年' as '学年',
21         Course.CourseName as '课程',
22         Score.ScoreValue as '成绩',
23         T1.CJMc as '名次'
24 FROM
25 (
26     SELECT  Score.ScoreID,--成绩ID
27             rank() over(partition by Score.SyID,Score.CourseID order by Score.ScoreValue desc) as CJMc
28     FROM Score
29 ) T1
30 LEFT JOIN Score ON Score.ScoreID=T1.ScoreID
31 LEFT JOIN Student ON Student.StuNO=Score.StuNO
32 LEFT JOIN Course ON Course.CourseID=Score.CourseID
33 LEFT JOIN SchoolYear ON SchoolYear.SyID=Score.SyID
34 where Student.StuNO=@StuNO
35         and Course.CourseName=@CourseName

问题3解答

  1 /*完成审核学生学籍状况的功能。
  2 即在每个学年，检查每个学生的所有不及格课程的门数，
  3 对该生就读情况进行重新设定，
  4 并列出不为正常在读状态的所有学生信息。
  5     1  如果不及格课程的数量超出其所有已修课程数量的一半时，
  6         当“状态”值为1，将其修改为2，表示退学试读，
  7         当“状态”值为2时，将其修改为3，表示退学。
  8     2  如果不及格课程的数量不再超出其所有已修课程数量的一半，
  9         而该生处于退学试读状态时，即“状态”值为2时，将其修改为1，表示该生重新设置为“正常在读”的状态。
 10 */
 11 
 12 /*解答说明：
 13 1 需要输入的参数是学年
 14 2 第一层需要统计的信息是：
 15     课程总数：
 16     不及格课程数：
 17     不及格课程数的百分比：
 18     学生编号：
 19 */
 20 /*更改学籍状态的存储过程如下：*/
 21 CREATE PROCEDURE UpdateStuState
 22 (
 23     @SyStartYear int,/*学年开始年份*/
 24     @SyEndYear int,/*学年结束年份*/
 25     @strOutPut nvarchar(200) output /*输出参数*/
 26 ) as
 27 BEGIN
 28     DECLARE @SyID INT /*学年ID*/
 29     set @strOutPut=''
 30     SELECT @SyID=SyID FROM SchoolYear WHERE SyStartYear=@SyStartYear AND SyEndYear=@SyEndYear
 31     if(@SyID is null)
 32         begin
 33             set @strOutPut='未找到学年信息，参数输入不正确!'
 34             return;
 35         end
 36 
 37     /*临时表：#TempNoPass，表示统计的结果*/
 38     if   object_id('tempdb..#TempNoPass')   is   not   null 
 39               BEGIN
 40                 drop   table   #TempNoPass
 41               END
 42     /*1 创建该临时表*/
 43     CREATE TABLE #TempNoPass
 44     (
 45         StuNO NVARCHAR(12),/*学号*/
 46         CourseRegCount int,/*当前学年总注册数量*/
 47         CourseNoPassCount int, /*不及格数量*/
 48         ScoreValueCount    int,/*有成绩的课程数量*/
 49         ProNoPass decimal(5,3) default(0), /*不及格率，用小数表示*/
 50         Remark nvarchar(50) /*说明*/
 51     )
 52 
 53     /*2 向临时表中加入初始统计数据*/
 54 
 55     INSERT INTO #TempNoPass
 56     (
 57         StuNO,/*学号*/
 58         CourseRegCount,/*当前学年总注册数量*/
 59         CourseNoPassCount, /*不及格数量*/
 60         ScoreValueCount,/*有成绩的课程数量*/
 61         ProNoPass, /*不及格率，用小数表示*/
 62         Remark/*说明*/
 63     )
 64     SELECT A.StuNO,/*学号*/
 65            B.CourseRegCount,/*当前学年总注册数量*/
 66            C.CourseNoPassCount, /*不及格数量*/
 67            D.ScoreValueCount,    /*有成绩的课程数量*/
 68            0 as ProNoPass,
 69            '初始统计' as Remark
 70     FROM Student A
 71     LEFT JOIN
 72             (
 73                 /*统计注册的总课程数量*/
 74                 SELECT StuNO,COUNT(1) as  CourseRegCount
 75                 FROM CourseRegist B 
 76                 WHERE SyID=@SyID
 77                 group by StuNO
 78             ) B ON A.StuNO=B.StuNO
 79     LEFT JOIN 
 80             (
 81                 /*统计不及格的课程数量*/
 82                 SELECT Student.StuNO, COUNT(1) AS CourseNoPassCount
 83                 FROM Student
 84                 LEFT JOIN Score ON Score.StuNO=Student.StuNO 
 85                 LEFT JOIN SchoolYear ON SchoolYear.SyID=Score.SyID
 86                 LEFT JOIN Course ON Course.CourseID=Score.CourseID
 87                 where Score.SyID=@SyID
 88                       and Score.ScoreValue<Course.CoursePassScore /*当前成绩<及格分数判定为不及格*/
 89                 group by Student.StuNO
 90             ) C ON C.StuNO=A.StuNO
 91     LEFT JOIN 
 92             (
 93                 /*有成绩的课程数量*/
 94                 SELECT Student.StuNO, COUNT(1) AS ScoreValueCount
 95                 FROM Student
 96                 LEFT JOIN Score ON Score.StuNO=Student.StuNO 
 97                 LEFT JOIN SchoolYear ON SchoolYear.SyID=Score.SyID
 98                 LEFT JOIN Course ON Course.CourseID=Score.CourseID
 99                 where Score.SyID=@SyID
100                 group by Student.StuNO
101             ) D ON D.StuNO=A.StuNO
102         
103     /*3 对临时表初始数据进行处理，统计出不及格率及说明信息*/    
104     /*去掉未注册课程的数据*/
105     DELETE #TempNoPass WHERE CourseRegCount IS NULL
106     /*把已注册课程，但没有任何成绩记录*/
107     UPDATE #TempNoPass 
108             SET Remark='已注册课程，但没有成绩记录',
109                 ProNoPass=1 
110             WHERE ScoreValueCount IS NULL or ScoreValueCount=0
111     /*统计不及格率*/
112     UPDATE #TempNoPass 
113             SET Remark='统计不及格率',
114                 /*不及格率=(不及格课程数+(注册的总课程数-有成绩的课程数))/注册的总课程数*/
115                 ProNoPass=(      CONVERT(decimal,ISNULL(CourseNoPassCount,0))
116                             +(CONVERT(decimal,CourseRegCount)-CONVERT(decimal,ScoreValueCount))
117                           )/CONVERT(decimal,CourseRegCount)    
118             WHERE ScoreValueCount IS NOT NULL 
119     /*去掉不合格率为0的*/
120     DELETE #TempNoPass WHERE ProNoPass=0
121     /*更改说明*/
122     Update #TempNoPass set Remark=convert(varchar(2),(CourseRegCount-ScoreValueCount))+'门课程无成绩记录' where ScoreValueCount<CourseRegCount
123 
124     /*4 根据统计的结果，审核学籍状态。其中4.1和4.2顺序不可颠倒*/
125     /****4.1 不及格率大于等于0.5，状态为2改为3***/
126     UPDATE Student SET StuState=3
127     WHERE StuState=2
128         AND EXISTS
129             (
130                 SELECT 1 FROM #TempNoPass T
131                 WHERE T.ProNoPass>=0.5
132                     AND T.StuNO=Student.StuNO
133             )
134     /****4.2 不及格率大于等于0.5，状态为1改为2***/
135     UPDATE Student SET StuState=2
136     WHERE StuState=1
137         AND EXISTS
138             (
139                 SELECT 1 FROM #TempNoPass T
140                 WHERE T.ProNoPass>=0.5
141                     AND T.StuNO=Student.StuNO
142             )
143 
144     /****4.3 不及格率小于0.5，状态为2改为1***/
145     UPDATE Student SET StuState=2
146     WHERE StuState=1
147         AND EXISTS
148             (
149                 SELECT 1 FROM #TempNoPass T
150                 WHERE T.ProNoPass<0.5
151                     AND T.StuNO=Student.StuNO
152             )
153     SELECT * FROM #TempNoPass
154     drop table #TempNoPass
155 END

问题4解答

  1 /*
  2 原需求:
  3 通过输入班级号和课程号，
  4 列出某班级某课程的平均成绩、最高成绩、最低成绩、及格人数、不及格人数、及格率等。
  5 如果该课程的及格率低于50%，则统一为每一位学生该门课程的考试成绩增加10分，
  6 对于90分以上的学生，则增加5分。
  7 对于增加成绩后出现超出100分的学生则按100分来处理。
  8 成绩调整后，需要再次列出新的成绩汇总情况
  9 */
 10 
 11 /*1 需要查询出需要统计的基础数据
 12 查询结果：    学年
 13             班级
 14             课程
 15             学生
 16             成绩
 17 然后对上述查询出的数据进行统计
 18 */
 19 
 20 /*该存储过程只完成统计部分，对于调整数据不做处理*/
 21 CREATE PROCEDURE StatScoreDetail
 22 (
 23     @SyStartYear int=2012,/*学年开始年份*/
 24     @SyEndYear int=2013,/*学年结束年份*/
 25     @strOutPut nvarchar(200) output/*输出参数*/
 26 ) as
 27 BEGIN
 28     DECLARE @SyID INT /*学年ID*/
 29     select @SyID=SyID FROM SchoolYear WHERE SyStartYear=@SyStartYear AND SyEndYear=@SyEndYear
 30     if (@SyID is null)
 31         begin
 32             set @strOutPut='未找到学年信息，参数输入不正确!'
 33             return;
 34         end
 35     /*1 #TempBasicData查询初始数据，作为统计的基础
 36         #TempResultData作为最终结果的临时表数据
 37     */
 38     if   object_id('tempdb..#TempBasicData')   is   not   null 
 39           BEGIN
 40             drop   table   #TempBasicData
 41           END
 42     
 43     if   object_id('tempdb..#TempResultData')   is   not   null 
 44           BEGIN
 45             drop   table   #TempResultData
 46           END
 47     SELECT A.SyID,/*学年ID*/
 48         B.GcID,/*班级ID*/
 49         A.CourseID,/*课程ID*/
 50         A.StuNO,/*学号*/
 51         A.ScoreValue, /*分数*/
 52         C.CoursePassScore,/*及格分数*/
 53         CASE
 54         WHEN A.ScoreValue>=C.CoursePassScore THEN 1
 55         ELSE 0
 56         END AS PassFlag /*是否及格的标志。0 不及格；1 及格*/
 57         INTO #TempBasicData
 58     FROM Score A
 59     LEFT JOIN GradeClassStu B ON B.StuNO=A.StuNO AND B.SyID=A.SyID
 60     LEFT JOIN Course C ON C.CourseID=A.CourseID
 61     where A.SyID=@SyID
 62     
 63     /*2 对初始数据进行统计*/
 64     SELECT S1.SyID,/*学年ID*/
 65             S1.GcID,/*班级ID*/
 66             S1.CourseID,/*课程ID*/
 67             S1.AvgScoreValue,/*平均成绩*/
 68             S1.MaxScoreValue,    /*最高成绩*/
 69             S1.MinScoreValue,    /*最低成绩*/
 70             S1.AllStuCount, /*该门课程参加考试的总人数*/
 71             ISNULL(S2.PassCount,0) AS PassCount,/*及格人数*/
 72             ISNULL(S3.NoPassCount,0) AS NoPassCount,/*不及格人数*/
 73             ISNULL(S2.PassCount,0)/CONVERT(decimal,S1.AllStuCount) AS ProPass /*及格率*/
 74     INTO #TempResultData
 75     FROM 
 76     (
 77         /*2 S1表：对已查询到的初始数据*/
 78         SELECT T.SyID,/*学年ID*/
 79             T.GcID,/*班级ID*/
 80             T.CourseID,/*课程ID*/
 81             AVG(T.ScoreValue) AS AvgScoreValue, /*平均成绩*/
 82             MAX(T.ScoreValue) AS MaxScoreValue,    /*最高成绩*/
 83             MIN(T.ScoreValue) AS MinScoreValue,    /*最低成绩*/
 84             COUNT(1) AS AllStuCount /*该门课程参加考试的总人数*/
 85             FROM #TempBasicData T
 86             GROUP BY T.SyID,T.GcID,T.CourseID
 87     ) S1
 88     LEFT JOIN
 89     (
 90         /*3 S2:统计及格人数*/
 91         SELECT T.SyID,T.GcID,T.CourseID,T.PassFlag,COUNT(1) as PassCount 
 92         FROM #TempBasicData T 
 93         where T.PassFlag=1
 94         GROUP BY T.SyID,T.GcID,T.CourseID,T.PassFlag
 95     ) S2 ON S2.SyID=S1.SyID AND S2.GcID=S1.GcID AND S2.CourseID=S1.CourseID
 96     LEFT JOIN
 97     (
 98         /*3 S3:统计不及格人数*/
 99         SELECT T.SyID,T.GcID,T.CourseID,T.PassFlag,COUNT(1) as NoPassCount 
100         FROM #TempBasicData T 
101         where T.PassFlag=0
102         GROUP BY T.SyID,T.GcID,T.CourseID,T.PassFlag
103     ) S3 ON S3.SyID=S1.SyID AND S3.GcID=S1.GcID AND S3.CourseID=S1.CourseID
104     
105     /*3 把最终的统计结果转换为可以显示到界面的列表*/
106     SELECT CONVERT(VARCHAR(4),A.SyStartYear)+'--'+CONVERT(varchar(4),A.SyEndYear)+'学年' as '学年',
107             B.GcName AS '班级',
108             C.CourseName AS '课程',
109             T.AvgScoreValue  as '平均成绩',/*平均成绩*/
110             T.MaxScoreValue  as '最高成绩',    /*最高成绩*/
111             T.MinScoreValue  as '最低成绩',    /*最低成绩*/
112             T.AllStuCount  as '考试人数', /*该门课程参加考试的总人数*/
113             T.PassCount  as '及格人数',/*及格人数*/
114             T.NoPassCount  as '不及格人数',/*不及格人数*/
115             cast(round(T.ProPass*100,2)   as   numeric(5,2)) as '及格率(%)'            
116     FROM #TempResultData T
117     LEFT JOIN SchoolYear A ON A.SyID=T.SyID
118     LEFT JOIN GradeClass B ON B.GcID=T.GcID
119     LEFT JOIN Course C ON C.CourseID=T.CourseID
120     order by T.SyID asc,/*学年ID*/
121             T.GcID  asc,/*班级ID*/
122             T.CourseID asc/*课程ID*/
123     
124     drop table #TempBasicData
125     drop table #TempResultData
126 END

posted on 2014-11-28 15:42 孤星无泪阅读(489) 评论(0) 收藏举报

刷新页面返回顶部

孤星无泪

学生成绩数据库设计四解答问题的存储过程

导航

公告

孤星无泪

学生成绩数据库设计 四 解答问题的存储过程

导航

公告

学生成绩数据库设计四解答问题的存储过程