T-SQL综合应用

 

 

问题：

1.统计本次考试的缺考情况 。

2.提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过,比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分。提分后，统计学员的成绩和通过情况 。

3.提分后统计学员的通过率情况。

 

分析：

使用子查询统计缺考情况：

应到人数：SELECT count(*)  FROM stuInfo

实到人数：SELECT count(*) FROM stuMarks

提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过

提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（left join）；

    SELECT  stuName…FROM stuInfo left Join stuMarks …

    ON stuInfo.stuNo=stuMarks.stuNo

要求新加一列“是否通过（isPass）”，可采用CASE …END，如果笔试和机试都>60分，则通过。为了便于后续的通过率统计，通过则为1，没通过为0

SELECT … isPass=CASE

                  WHEN writtenExam>=60 and labExam>=60  THEN  1

                  ELSE  0

                END

FROM …

要求保存提取（查询）的结果，可以使用我们曾学习过的SELECT …INTO newTable语句，生成新表并保存数据；生成新表前，需要检测是否已存在newTable表

    IF EXISTS(SELECT * FROM sysobjects where name='newTable')

       DROP TABLE newTable

    SELECT …INTO newTable….

比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分：

定义2个变量：分别存放笔试和机试平均分，然后使用AVG( )函数从表中获取数据并赋值；

使用IF语句判断笔试还是机试偏低，决定对笔试还是机试提分；

使用WHILE循环给每个学员加分，缺考的除外，当最高分超过97分时退出循环；

因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新isPass（是否通过）列。

   UPDATE newTable

      SET isPass=CASE

               WHEN writtenExam>=60 and labExam>=60 THEN 1

               ELSE  0

              END

提分后，统计学员的成绩和通过情况：

1）使用别名实现中文字段名，即SELECT 姓名=stuName,学号=stuNo…

2）如果某个学员的成绩为NULL(空)，则替换为”缺考”，否则原样显示；

3）isPass列中的1替换为是，0替换为否；

 SELECT  姓名=stuName,学号=stuNo,

笔试成绩=CASE

              WHEN writtenExam IS NULL THEN '缺考'

              ELSE  convert(varchar(5),writtenExam)

           END

  ,机试成绩=CASE

             WHEN labExam IS NULL THEN '缺考'

             ELSE  convert(varchar(5),labExam)

          END

  ,是否通过=CASE

             WHEN isPass=1 THEN '是'

             ELSE  '否'

           END

 FROM newTable 

提分后统计学员的通过率情况：

1）通过人数：因为通过用1表示，没通过用0表示，所以isPass列的累加和即是通过人数；

2）通过率：同理，isPass列的平均值*100即是通过率；

 

/*--本次考试的原始数据--*/

--SELECT * FROM stuInfo

--SELECT * FROM stuMarks

 

/*--------------统计考试缺考情况----------------------*/

SELECT 应到人数=(SELECT count(*)  FROM stuInfo) ,   --应到人数为子查询表达式的别名

  实到人数=(SELECT count(*) FROM stuMarks)  ,

   缺考人数=((SELECT count(*) FROM stuInfo)-(SELECT count(*) FROM stuMarks))

/*----统计考试通过情况，并将结果存放在新表newTable中---*/

IF EXISTS(SELECT * FROM sysobjects

                                  WHERE name='newTable')

    DROP TABLE newTable

SELECT  stuName,stuInfo.stuNo,writtenExam ,labExam ,

   isPass=CASE

           WHEN writtenExam>=60 and labExam>=60 THEN 1

           ELSE 0

       END

    INTO newTable FROM stuInfo

       LEFT JOIN  stuMarks   

           ON stuInfo.stuNo=stuMarks.stuNo

--SELECT * FROM newTable --查看统计结果，可用于调试

 

/*-酌情加分：比较笔试和机试平均分，决定加哪门---*/

DECLARE @avgWritten numeric(4,1)

DECLARE @avgLab numeric(4,1)

SELECT @avgWritten=AVG(writtenExam) FROM newTable

      WHERE  writtenExam IS NOT NULL

SELECT @avgLab=AVG(labExam)FROM newTable

     WHERE  labExam IS NOT NULL

IF @avgWritten<@avgLab

  WHILE (1=1) --循环给笔试加分，最高分不能超过97分

    BEGIN 

      UPDATE newTable SET writtenExam=writtenExam+1

      IF (SELECT MAX(writtenExam) FROM newTable )>=97

         BREAK

    END

ELSE   …略… --循环给机试加分，最高分不能超过97分

--因为提分，所以需要更新isPass（是否通过）列的数据

UPDATE newTable

  SET isPass=CASE

        WHEN writtenExam>=60 and labExam>=60 THEN 1

        ELSE  0

     END

--SELECT * FROM newTable  --可用于调试

 

/*--------------显示考试最终通过情况----------------*/

SELECT 姓名=stuName,学号=stuNo

 ,笔试成绩=CASE

            WHEN writtenExam IS NULL THEN '缺考'

             ELSE  convert(varchar(5),writtenExam)

          END

 ,机试成绩=CASE

             WHEN labExam IS NULL THEN '缺考'

             ELSE  convert(varchar(5),labExam)

          END

 ,是否通过=CASE

             WHEN isPass=1 THEN '是'

             ELSE  '否'

           END

 FROM newTable

/*--显示通过率及通过人数--*/

SELECT 总人数=count(*) ,通过人数=SUM(isPass),

   通过率=(convert(varchar(5),AVG(isPass*100))+'%')  FROM newTable