13_项目_成绩查询

网站开发技术项目成绩查询list.asp!--#includevirtual=head.asp--%action=IfSession(yhlx)=学生Thenaction=list_xsh.aspIfSession(yhlx)=领导Thenaction=list_hz.aspIfactionThenResponse.Redirect(action)%list.asp(cont.)formname=fmethod=postonSubmit=returncheck();选择专业：selectname=zhyoption数学/optionoption物理/optionoption化学/option/selectbrinputtype=radioname=actcheckedonClick=f.zhy.disabled=false;明细inputtype=radioname=actonClick=f.zhy.disabled=true;汇总brinputtype=submitvalue=提交/formlist.asp(cont.)scriptlanguage=javascriptfunctioncheck(){if(f.act[0].checked){f.action=list_zhy.asp;returntrue;}if(f.act[1].checked){f.action=list_hz.asp;returntrue;}alert(请选择操作！);returnfalse;}/script成绩查询（学生0201）课号课程名称成绩学分001数学752002物理560003化学922总课程：3总平均：74.3总学分：4list_xsh.asp%IfSession(yhlx)学生ThenResponse.Redirect(logoff.asp)%!--#includevirtual=conn.asp--%sql=selectchj.*,kch.mchfromchj,kchwhereyhm='__&Session(user)&'__&andchj-1andchj.xkkh=kch.xkkhSetrs=conn.Execute(sql)Ifrs.EofThenResponse.Write无记录！Response.EndEndIf%list_xsh.asp(cont.)%Response.Write课号课程名称成绩学分DoWhileNotrs.EofResponse.Writers(xkkh)Response.Writers(mch)Response.Writers(chj)Response.Writers(xf)rs.MoveNextLooprs.Close%list_xsh.asp(cont.)%sql=selectcount(*)asnum,avg(chj)asavgchj,__sum(xf)assumxffromchjwhereyhm='__&Session(user)&'&andchj-1Setrs=conn.Execute(sql)Response.Write总课程数：&rs(num)Response.Write总平均分：&Round(rs(avgchj),1)Response.Write总学分：&rs(sumxf)rs.Closeconn.Close%成绩查询（数学专业）学号总课程数平均分总学分0201374.340304564.680305372.06学生总数3总课程数5总平均70.3总学分18list_zhy.asp!--#includevirtual=head.asp--%IfSession(yhlx)管理员Then__Response.Redirect(logoff.asp)zhy=Request.Form(zhy)Ifzhy=ThenResponse.Redirect(logoff.asp)%!--#includevirtual=conn.asp--GroupBy•selectchj.yhmfromchj,yhxxwherezhy='数学'andchj-1andchj.yhm=yhxx.yhm–020102010201–03040304030403040304–030503050305•selectchj.yhmfromchj,yhxxwherezhy='数学'andchj-1andchj.yhm=yhxx.yhmgroupbychj.yhm–020103040305list_zhy.asp(cont.)%sql=selectchj.yhm,count(*)asnum,avg(chj)asavgchj,__sum(xf)assumxffromchj,yhxx&__wherezhy='&zhy&'&__andchj-1andchj.yhm=yhxx.yhm&__groupbychj.yhmSetrs=conn.Execute(sql)Ifrs.EofThenResponse.Write无记录！Response.EndEndIf%list_zhy.asp(cont.)%xsh=0pjf=0xf=0Response.Write学号总课程数平均分总学分DoWhileNotrs.EofResponse.Writers(yhm)&rs(num)Response.WriteRound(rs(avgchj),1)&rs(sumxf)xsh=xsh+1pjf=pjf+rs(avgchj)xf=xf+rs(sumxf)rs.MoveNextLooprs.Closepjf=Round(pjf/xsh,1)%总课程数的查询•selectcount(distinctxkkh)asnumfromchj,yhxxwherezhy='数学'andchj-1andchj.yhm=yhxx.yhm•selectcount(TempXkkh.xkkh)asnumfrom[selectdistinctxkkhfromchj,yhxxwherezhy='数学'andchj-1andchj.yhm=yhxx.yhm].asTempXkkhlist_zhy.asp(cont.)%sql=selectcount(TempXkkh.xkkh)asnumfrom__[selectdistinctxkkhfromchj,yhxx__&wherezhy='&zhy&'&andchj-1__&andchj.yhm=yhxx.yhm].asTempXkkhSetrs=conn.Execute(sql)Response.Write学生总数：&xshResponse.Write总课程数：&rs(num)Response.Write总平均分：&pjfResponse.Write总学分：&xfrs.Closeconn.Close%成绩查询（汇总）专业学生数总课程数平均分总学分数学3570.318物理3469.513化学3465.112专业总数3学生总数9总课程数5总平均68.3总学分43汇总成绩查询各专业•学生数、平均分、总学分–selectcount(*)asnum,avg(chj)asavgchj,sum(xf)assumxffromchj,yhxxwherezhy='数学'andchj-1andchj.yhm=yhxx.yhmgroupbychj.yhm•课程数–selectcount(TempXkkh.xkkh)asnumfrom[selectdistinctxkkhfromchj,yhxxwherezhy='数学'andchj-1andchj.yhm=yhxx.yhm].asTempXkkh汇总成绩查询汇总•专业总数•学生总数–selectcount(yhm)asnumfrom[selectdistinctchj.yhmfromchjwherechj-1].asTempXkkh•课程总数–selectcount(xkkh)asnumfrom[selectdistinctxkkhfromchjwherechj-1].asTempXkkh•总学分–selectsum(xf)assumxffromchjwherechj-1各专业记录selectchj.*fromchj,yhxxwherezhy='数学'andchj-1andchj.yhm=yhxx.yhmorderbychj.yhmyhmxkkhchjxf0201001752020100256002010039220304001851list_hz.asp!--#includevirtual=head.asp--%IfSession(yhlx)管理员And__Session(yhlx)领导Then__Response.Redirect(logoff.asp)%!--#includevirtual=conn.asp--list_hz.asp(cont.)%tmp=Split(数学-物理-化学,-)Response.Writecentertableborder='1'Response.Write学生数总课程数平均分总学分zxsh=0zkh=-zpj=0zxf=0%list_hz.asp(cont.)%Fori=0ToUBound(tmp)xsh=0kh=-pjzhy=0pjxsh=0xf=0yhm=sql=selectchj.*fromchj,yhxxwherezhy='&tmp(i)&'__andchj-1andchj.yhm=yhxx.yhmorderbychj.yhmSetrs=conn.Execute(sql)%list_hz.asp(cont.)n=0DoWhileNotrs.Eoftmpyhmyhmnxshpjxshpjzhytmpyhm=rs(yhm)0000n=n+10201020101750IftmpyhmyhmThen02011131xsh=xsh+102012223pjzhy=pjzhy+pjxsh/n03040304028574.3pjxsh=0yhm=tmpyhmn=0EndIfpjxsh=pjxsh+Int(rs(chj))IfInStr(kh,-&rs(xkkh)&-)=0Thenkh=kh&rs(xkkh)&-IfInStr(zkh,-&rs(xkkh)&-)=0Thenzkh=zkh&rs(xkkh)&-xf=xf+Int(rs(xf))rs.MoveNextLooplist_hz.asp(cont.)%pjzhy=pjzhy+pjxsh/(n+1)pjzhy=Round(pjzhy/xsh,1)Response.Writetrtd&tmp(i)&/tdResponse.Writetd&xsh&/tdResponse.Writetd&Int(Len(kh)/4)&/tdResponse.Writetd&pjzhy&/tdResponse.Writetd&xf&/td/trzxsh=zxsh+xshzxf=zxf+xfzpj=zpj+pjzhyNext%list_hz.asp(cont.)%Response.Write专业总数：&iResponse.Write学生总数：&zxshResponse.Write总课程数：&Int(Len(zkh)/4)Response.Write总平均分：&Round(zpj/i,1)Response.Write总学分：&zxf%