高级查询实例

stuInfo表

 

stuMark表

 

use stuDBgoselect * from stuInfoselect * 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 newTableselect stuName, stuInfo.stuNo,writtenExam,LabExam,ispass=casewhen writtenExam>60 and LabExam>60 then '1'else '0'endinto newTablefrom stuInfo left join stuMark on stuInfo.stuNo=stuMark.stuNoselect * from newTable/*------酌情加分，比较笔试平均分和机试平均分，哪科平均分低就给哪科加分------*/declare @avgwritten numeric(4,1), @avgLab numeric(4,1)--定义变量存放笔试和机试平均分select @avgwritten=AVG(writtenExam) from newTable where writtenExam is not nullselect @avgLab=AVG(labExam) from newTable where labExam is not nullif @avgwritten<@avgLabwhile(1=1)beginupdate newTable set writtenExam=writtenExam+1if(select MAX(writtenExam) from newTable)>=97breakendelsewhile(1=1)beginupdate newTable set labExam=labExam+1if(select MAX(labExam) from newTable)>=97breakend--因为提分，所以需要更新isPass（是否通过）列的数据update newTableset isPass=casewhen writtenExam>=60 and labExam>=60 then '1'else '0'endselect * from newTable /*----显示考试最终情况-----*/select 姓名=stuName,学号=stuNo,笔试成绩=casewhen writtenExam IS NULL then '缺考'else CONVERT(varchar(5),writtenExam)end,机试成绩=casewhen labExam IS NULL then '缺考'else CONVERT(varchar(5),labExam) end,是否通过=casewhen isPass=1 then '是'else '否' endfrom newTable