T-SQL综合应用
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><?xml:namespace prefix = w ns = "urn:schemas-microsoft-com:office:word" />
问题：
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
