stuInfo表 stuMark表 use stuDB go select * from stuInfo select * from stuMark /*----统计缺考人数----*/ select 应到人数=(select COUNT(*) from stuInfo), 实到人数=(select COUNT(*) from stuMark), 缺考人数=((select COUNT(*) from stuInfo)-(select COUNT(*) from stuMark)) /*-----统计考试通过情况,并将统计结果存放在新表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 stuMark on stuInfo.stuNo=stuMark.stuNo select * from newTable /*------酌情加分,比较笔试平均分和机试平均分,哪科平均分低就给哪科加分------*/ declare @avgwritten numeric(4,1), @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) begin update newTable set writtenExam=writtenExam+1 if(select MAX(writtenExam) from newTable)>=97 break end else while(1=1) begin update newTable set labExam=labExam+1 if(select MAX(labExam) from newTable)>=97 break end --因为提分,所以需要更新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