考试成绩统计与分析-优快云博客

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><shapetype id="_x0000_t75" coordsize="21600,21600" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="_x0000_s1027" style="WIDTH: 310.4pt; HEIGHT: 176.65pt; mso-position-horizontal-relative: char; mso-position-vertical-relative: line" fillcolor="#0c9" type="#_x0000_t75"><imagedata o:title="" src="file:///C:/DOCUME~1/admin/LOCALS~1/Temp/msohtml1/01/clip_image001.gif"></imagedata><?xml:namespace prefix = w ns = "urn:schemas-microsoft-com:office:word" /><wrap type="none"></wrap><anchorlock></anchorlock></shape>

问题：

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

T-SQL综合应用