SQL语句将学生升降级.

原贴地址

http://topic.youkuaiyun.com/u/20071025/21/35c6f067-0a1f-44f8-af64-f68214a2b7af.html

--<!create test data
DECLARE @class     TABLE    (id INT,name VARCHAR(20),level INT)
DECLARE @student TABLE    (id INT,name VARCHAR(20),classID INT)
DECLARE @achieve TABLE    (id INT,studentid INT,grade INT)
INSERT @class SELECT 1,'一班',1
UNION ALL SELECT 2,'二班',2
UNION ALL SELECT 3 ,'三班',2
INSERT @student SELECT 1,'学生',1
UNION ALL SELECT 2,'学生',1
UNION ALL SELECT 3,'学生',1
UNION ALL SELECT 7,'学生',2
UNION ALL SELECT 8,'学生',2
UNION ALL SELECT 9,'学生',2
UNION ALL SELECT 4,'学生',3
UNION ALL SELECT 5,'学生',3
UNION ALL SELECT 6,'学生',3
INSERT @achieve SELECT 1,1,80
UNION ALL SELECT 2,      2,          81
UNION ALL SELECT 3,      3,          82
UNION ALL SELECT 4,      4,          83
UNION ALL SELECT 5,      5,          84
UNION ALL SELECT 6,      6,          85
UNION ALL SELECT 7,      7,          86
UNION ALL SELECT 8,      8,          87
UNION ALL SELECT 9,      9,          88
--end create test data!>

--<!view source data
SELECT * FROM @student
/*
id    name  classid
1    学生1    1
2    学生2    1
3    学生3    1
7    学生4    2
8    学生5    2
9    学生6    2
4    学生7    3
5    学生8    3
6    学生9    3
*/
--end view source data!>

--<!--update data
UPDATE x SET x.classid=
       
CASE WHEN x.classid=1 THEN
           
CASE (SELECT count(*) FROM @student s INNER JOIN @achieve a ON a.studentid=s.id WHERE s.classid=x.classid AND a.grade<y.grade) WHEN 1 THEN
               
2
           
WHEN 0 THEN
               
3
           
ELSE 1 END

       
ELSE
           
CASE WHEN y.grade=(SELECT MAX(grade) FROM @achieve a INNER JOIN @student s ON a.studentid=s.id WHERE s.classid=x.classid) THEN
               
1
           
ELSE x.classid END
       
END
   
FROM @student x INNER JOIN @achieve y ON x.id=y.studentid
--end update data!>

--<!view updated data
SELECT * FROM @student
/*
id     name   classid
1    学生1    3
2    学生2    2
3    学生3    1
7    学生4    2
8    学生5    2
9    学生6    1
4    学生7    3
5    学生8    3
6    学生9    1
*/
--end view updated data!>

--<!create test data
DECLARE @class     TABLE    (id INT,name VARCHAR(20),level INT)
DECLARE @student TABLE    (id INT,name VARCHAR(20),classID INT)
DECLARE @achieve TABLE    (id INT,studentid INT,grade INT)
INSERT @class SELECT 1,'一班',1
UNION ALL SELECT 2,'二班',2
UNION ALL SELECT 3 ,'三班',3
INSERT @student SELECT 1,'学生',1
UNION ALL SELECT 2,'学生',1
UNION ALL SELECT 3,'学生',1
UNION ALL SELECT 7,'学生',2
UNION ALL SELECT 8,'学生',2
UNION ALL SELECT 9,'学生',2
UNION ALL SELECT 4,'学生',3
UNION ALL SELECT 5,'学生',3
UNION ALL SELECT 6,'学生',3
INSERT @achieve SELECT 1,1,80
UNION ALL SELECT 2,      2,          81
UNION ALL SELECT 3,      3,          82
UNION ALL SELECT 4,      4,          83
UNION ALL SELECT 5,      5,          84
UNION ALL SELECT 6,      6,          85
UNION ALL SELECT 7,      7,          86
UNION ALL SELECT 8,      8,          87
UNION ALL SELECT 9,      9,          88
--end create test data!>


--<!--update data
UPDATE x SET x.classid=
       
--有几个同classid的student的成绩小于此行student的成绩
         CASE (SELECT count(*) FROM @student s INNER JOIN @achieve a ON a.studentid=s.id WHERE s.classid=x.classid AND a.grade<y.grade)
       
--当此行成绩为最同classid最小时.班级降级
        WHEN 0 THEN
           
ISNULL((SELECT id FROM @class WHERE level=c.level+1),x.classid)
       
--此行不是最低成绩
        ELSE
           
--有几个同classid的student的成绩大于此行的student成绩
            CASE (SELECT count(*) FROM @student s INNER JOIN @achieve a ON a.studentid=s.id WHERE s.classid=x.classid AND a.grade>y.grade)
           
--此行为最高成绩,班级升级
            WHEN 0 THEN
           
ISNULL((SELECT id FROM @class WHERE level=c.level-1),x.classid)
           
ELSE x.classid END
       
END
   
FROM @student x
--SELECT x.classid,c.level,(SELECT id FROM @class WHERE level=(c.level+1)),(SELECT count(*) FROM @student s INNER JOIN @achieve a ON a.studentid=s.id WHERE s.classid=x.classid AND a.grade<y.grade) FROM @student x
INNER JOIN @achieve y ON x.id=y.studentid
INNER JOIN @class c ON x.classid=c.id

SELECT * FROM @student-- s,@class c where c.id=s.classid 

 

最终需求和结果

 

[code=SQL]
--<!create test data
DECLARE @class   TABLE    (id INT,name VARCHAR(20),level INT,pos INT)
DECLARE @student TABLE    (id INT,name VARCHAR(20),classID INT)
DECLARE @achieve TABLE    (id INT,studentid INT,grade INT)
INSERT @class SELECT 1,      '一(1)班',                1,            1  
UNION ALL SELECT 2,          '二(1)班' ,               2 ,           1  
UNION ALL SELECT 3 ,         '二(2)班'  ,              2  ,          2  
UNION ALL SELECT 4  ,        '三(1)班'   ,             3   ,         1  
UNION ALL SELECT 5   ,       '三(2)班'    ,            3    ,        2  
UNION ALL SELECT 6    ,      '三(3)班'     ,           3     ,       3  
UNION ALL SELECT 7     ,     '三(4)班'      ,          3      ,      4 
INSERT @student SELECT 1,    '学生1',                 1 
UNION ALL SELECT 2,          '学生2' ,                1 
UNION ALL SELECT 3 ,         '学生3'  ,               1 
UNION ALL SELECT 4  ,        '学生4'   ,              2 
UNION ALL SELECT 5   ,       '学生5'    ,             2 
UNION ALL SELECT 6    ,      '学生6'     ,            2 
UNION ALL SELECT 7     ,     '学生7'      ,           3 
UNION ALL SELECT 8      ,    '学生8'       ,          3 
UNION ALL SELECT 9       ,   '学生9'        ,         3 
UNION ALL SELECT 10       ,  '学生10'        ,        4 
UNION ALL SELECT 11        , '学生11'         ,       4 
UNION ALL SELECT 12         ,'学生12'          ,      4 
UNION ALL SELECT 13,         '学生13'           ,     5 
UNION ALL SELECT 14 ,        '学生14'            ,    5 
UNION ALL SELECT 15  ,       '学生15'             ,   5 
UNION ALL SELECT 16   ,      '学生16'              ,  6 
UNION ALL SELECT 17    ,     '学生17'               , 6 
UNION ALL SELECT 18     ,    '学生18'                ,6 
UNION ALL SELECT 16      ,   '学生19'      ,          7
UNION ALL SELECT 17       ,  '学生20'       ,         7 
UNION ALL SELECT 18        , '学生21'        ,        7 

INSERT @achieve SELECT 1, 1,          80     
UNION ALL SELECT 2,       2 ,         81 
UNION ALL SELECT 3 ,      3  ,        82 
UNION ALL SELECT 4  ,     4   ,       80     
UNION ALL SELECT 5   ,    5    ,      81 
UNION ALL SELECT 6    ,   6     ,     82 
UNION ALL SELECT 7     ,  7      ,    80     
UNION ALL SELECT 8      , 8       ,   81 
UNION ALL SELECT 9       ,9        ,  82 
UNION ALL SELECT 10,      10        , 80     
UNION ALL SELECT 11 ,     11         ,81 
UNION ALL SELECT 12  ,    12,         82 
UNION ALL SELECT 13   ,   13 ,        80     
UNION ALL SELECT 14    ,  14  ,       81 
UNION ALL SELECT 15     , 15   ,      82 
UNION ALL SELECT 16      ,16    ,     80     
UNION ALL SELECT 17,      17     ,    81 
UNION ALL SELECT 18 ,     18      ,   82 
UNION ALL SELECT 19  ,    19       ,  80     
UNION ALL SELECT 20   ,   20        , 81 
UNION ALL SELECT 21    ,  21         ,82
--end create test data>
--<!--view source data
SELECT * FROM @student
/*
1 学生1 1
2 学生2 1
3 学生3 1
4 学生4 2
5 学生5 2
6 学生6 2
7 学生7 3
8 学生8 3
9 学生9 3
10 学生10 4
11 学生11 4
12 学生12 4
13 学生13 5
14 学生14 5
15 学生15 5
16 学生16 6
17 学生17 6
18 学生18 6
16 学生19 7
17 学生20 7
18 学生21 7
*/
--end view source data>

--<!--update data
UPDATE x SET x.classid=
        --有几个同classid的student的成绩小于此行student的成绩
         CASE (SELECT count(*) FROM @student s INNER JOIN @achieve a ON a.studentid=s.id WHERE s.classid=x.classid AND a.grade<y.grade)
        --当此行成绩为最同classid最小时.班级降级
        WHEN 0 THEN
            ISNULL((SELECT id FROM @class WHERE level=c.level+1 AND pos=c.pos*2-1),x.classid)
 WHEN 1 THEN
            ISNULL((SELECT id FROM @class WHERE level=c.level+1 AND pos=c.pos*2),x.classid)
        --此行不是最低二个成绩
        ELSE
            --有几个同classid的student的成绩大于此行的student成绩
            CASE (SELECT count(*) FROM @student s INNER JOIN @achieve a ON a.studentid=s.id WHERE s.classid=x.classid AND a.grade>y.grade)
            --此行为最高成绩,班级升级
            WHEN 0 THEN
             ISNULL((SELECT id FROM @class WHERE level=c.level-1 AND pos=CASE WHEN c.pos%2=1 THEN (c.pos+1)/2 ELSE c.pos/2 END),x.classid)
            ELSE x.classid END
        END
    FROM @student x
INNER JOIN @achieve y
 ON x.id=y.studentid
INNER JOIN @class c
 ON x.classid=c.id
--end update data>

--<!--view updated data
SELECT * FROM @student
/*
1 学生1 2
2 学生2 3
3 学生3 1
4 学生4 4
5 学生5 5
6 学生6 1
7 学生7 6
8 学生8 7
9 学生9 1
10 学生10 4
11 学生11 4
12 学生12 2
13 学生13 5
14 学生14 5
15 学生15 2
16 学生16 6
17 学生17 6
18 学生18 3
16 学生19 7
17 学生20 7
18 学生21 3
*/
--end view updated data>


[/code]

     软件工程上机实验要求      利用一种高级语言或数据库程序设计语言,依照所学的软件工程理论设计一个小型软件。要求:   1.大作业在教学17周结束前完成后提交。   2.设计过程原则上要求分组进行,每组一个题目(各组自定)。每组最多不超过3人,组内不同的学生可分工不同,内容不能完全雷同。   3.有详细、完整的文档资料。包括以下内容:      可行性研究报告;     需求规格说明书;     设计说明书(由于编写的是小型软件,故只写概要设计说明书);      用户操作手册;     测试计划;      测试分析报告;      软件开发总结报告。   4.有系统设计代码(其中代码注释不少于代码的30%),系统运行说明。   5.软件工程设计题目示例:        题目一 “教务管理系统之子系统——系内课程安排”   1.系统简介    每学期的期中,学院教务处分别向各个系发出下学期的教学计划,包   括课程名、课时、班级类别(本科、专科、高职)、号等;系教学主管人员根据教学任务和要求给出各课程的相关限制(如:任课教师职称、和数、最高周学时数等);任课教师自报本人授课计划,经所在教研室协调确认,将教学计划上交系主管教学计划的主任,批准后上报学院教务处,最终有教务处给出下学期全系教师的教学任务书。   假设上述排课过程全部为人工操作,现要求改造为能利用计算机实现的自动处理过程。   1. 限定条件   (1)每位教师的主讲门数不超过2门/学期:讲师以下职称的教师不能承担系定主课的主讲任务。   (2)系级干部的主讲课时不能超过4学时/周。   (3)本学期出现严重教学事故的教师不能承担下学期的主讲任务。   (4)本系统的输入项至少应包含3个:教务处布置的教学计划、系教师自报的讲课计划和系定的有关讲课限制条件。   (5)本系统的输出项至少应包含2个:教务处最终下达的全系教师教学任务书和系各教学一学期的课程表(可不包含上课地点)。       题目二、“学校教材订购系统”   1、 系统简介   本系统可细化为两个子系统:销售系统和采购系统   销售系统的工作过程为:首先由教师或学生提交购书单,经教材发行人员审核是有效购书单后,开发票、登记并返给教师或学生领书单,教师或学生即可去书库领书。   采购系统的主要工作过程为:若是脱销教材,则登记缺书,发缺书单给书库采购人员;一旦新书入库后,即发进书通知给教材发行人员   以上的功能要求在计算机上实现。   2、 技术要求和限制条件   (1) 当书库中的各种书籍数量发生变化(包括领书和进书时),都应修改相关的书库记录,如库存表或进/出库表。   (2) 在实现上述销售和采购的工作过程时,需考虑有关单据的合法性验证   (3) 系统的外部项至少包含三个:教师、学生和教材工作人员。   (4) 系统的相关数据存储至少包含6个:购书表、库存表、缺书登记表、待购教材表、进/出库表。       题目三、“机票预订系统”   1、系统简介   航空公司为给旅客乘机提供方便,需开发一机票预定系统。各旅行社把预定机票的旅客信息(姓名、性别、工作单位、身份证号码、旅行时间、旅行目的地等)输入到该系统,系统为旅客安排航。当旅客交付了预定金后,系统印出取票通知和帐单给旅客,旅客在飞机起飞的前一天凭取票通知和帐单交款取票,系统核对无误即印出机票给旅客。此外航空公司为随时掌握各航向飞机的乘载情况,需定期进行查询统计,以便适当调整。   2、 技术要求及限定条件   (1)在分析系统功能时要考虑有关证件的合法性验证(如身份证、取票通知、交款发票等)。   (2)对于本系统还应补充以下功能:    (1) 旅客延误了取票时间的处理    (2机取消后的处理    (3) 旅客临时更改机票次的处理   (3) 系统的外部项至少包含三个:旅客、旅行社和航空公司。       题目四:“学校内部工资管理系统”   1、 系统简介   假设学校共有教职工约1000人,10个行政部门和8个系部。每个月20日前各部门(包括系、部)要将出勤情况上报人事处,23日前人事处将出勤工资、奖金及扣款清单送财务处。财务处于每月月底将教职工的工资表做好并将数据送银行。每月初(3日前)将工资条发给各单位。若有员工调入、调出、校内调动、离退休等数据变化,则由人事处通知相关部门和财务处。   2、 技术要求及限定条件   (1) 本系统的数据存储至少包含:工资表、部门汇总表、扣税款表、银行发放表   (2) 除人事处、财务处外,其他只能部门和系部名称可简化,如系1,系2..等   (3) 工资、奖金、扣款细节可由学生自定       题目五、“实验室设备管理系统”   1、系统简介   每学年要对实验室设备使用情况进行统计、更新,其中:   (1) 对于已彻底损坏的作报废处理,同时详细记录有关信息。   (2) 对于有严重问题(故障)的要即使修理,并记录修理日期、设备名、修理厂家、修理费、责任人等。   (3) 对于急需但又缺少的设备需以“申请表”的形式送交上级领导请求批准购买。新设备购入后要立即进行设备登记(包括类别、设备名、型号、规格、单价、数量、购置日期、生产厂家、购买人等),同时更新申请表的内容。   (4) 随时对现有设备及其修理、报废情况进行统计、查询,要求能够按类别和时间段(某日期之前)查询。   2、技术要求及限定条件   (1) 所有工作由专门人员负责完成,其他人不得任意使用。   (2) 每件设备在作入库登记时均由系统按类别自动顺序编号,形成设备号;设备报废时要及时修改相应的设备记录,且有领导认可。   (3) 本系统的数据存储至少应包含:设备记录、修理记录、报废记录、购买申请。   (4) 本系统的输入项至少包含:新设备信息、修理信息、申请购买信息、报废信息、具体查询统计要求。   本系统的输出项至少包含:设备购买申请表、修理/报废注销/设备资金统计表。       题目六、分房管理系统   某大学拟开发一个计算机分房管理系统,要求系统具有分房、调房、退房和咨询统计等功能。房产科把用户申请表输入系统后,系统首先检查申请表是否合理,对不合法申请表拒绝接受;对合法表根据类型进行处理。   分房申请,根据申请者情况(年龄、工龄、职称、职务、家庭人口等)计算其分数,高于阀值的进行排队。分房时,读空房文件,把好房优先分给排在分房队列前面的人,并将房屋信息与申请者一起写入住房文件中。   若为退房申请,从住房文件中删除有关信息,并将房屋信息计入空房文件。   若是调房申请,根据申请者情况计算分数,并进行以上退房和分房处理。   系统可进行房屋信息维护(输入、删除、排序),以及各种住房信息统计、查询、打印等。       题目七 基金管理系统   为实现政务公开,某省拟采用网上自然基金申报和审批,需开发一套包括基金申报(使用说明、文档提交、申报情况查询)、基金信息公告(基金公告、基金新闻、最新立项)、网上审批与鉴定(专家定量网上审批、项目鉴定)、基金项目管理(发布申报计划、批复申请、查询申请项目等)与推荐优秀基金项目等功能的办公网站。   要求具有基金申报管理(申报文档提交,申报情况查询等),信息公告维护(增加,撤除和修改等),审批流程管理(管理员可指定审批流程,对需执行审批的专家显示审批流程,进行审批提示),项目鉴定管理(管理员可指定鉴定流程,对需执行鉴定的专家显示审批流程,进行鉴定提示)等功能。       题目八 汽车俱乐部管理系统   为实现汽车俱乐部行业信息的规范化管理,某软件开发商要求开发一汽车俱乐部管理系统,面向俱乐部工作人员进行会员信息的管理。主要功能包括:用户(俱乐部工作人员)身份验证和用户权限组织;提醒服务(提醒各种该执行的车务活动);会员管理(会员信息、会员费收缴记录录入,查询,修改);车务管理(新车上牌代办,保险代办,验证代办,落户代办等);救援服务(呼救记录,救援情况记录以及救援车辆管理);旅行服务(旅行信息记录、修改、查询);统计报表、图表(所有当日、当月、当年向会员提供的各种服务信息及其收费的详细情况列表及汇总);系统管理(设置提醒提前日期,用户管理等);俱乐部管理(俱乐部部门、工作人员、救援车辆管理);系统帮助。       题目八、学生学籍管理系统   功能说明   本学生学籍管理系统分为学生信息管理、学生成绩管理、考试管理和用户管理四个功能模块。   (1)学生信息管理模块主要是对学生信息(如学号、姓名、性别、家庭住址等)进行管理。本模块又分为三个子模块:   ◇学生信息录入   ◇学生信息查询   ◇学生信息修改/删除   其中,学生信息查询不需登录即可使用,信息录入和修改/删除模块则需要先登录后才能使用。   (2学生成绩管理模块主要是对学生成绩进行管理。本模块又分为五个子模块:   ◇学生成绩录入   ◇学生成绩查询   ◇学生成绩修改/删除   ◇计算总分与排名   ◇目标分管理   其中,学生成绩查询不需登录即可使用,其他模块则需要先登录后才能使用。   (3)考试管理模块主要对考试进行如下管理:   ◇添加新考试   ◇对已有的考试信息进行修改/删除   ◇对考试科目进行添加/删除   本模块需要先登录后才能使用。   (4)用户管理模块主要对使用本系统的用户进行如下管理:   ◇添加新用户   ◇对已有的用户进行更改用户名、密码和权限(管理员或普通用户)等操作   ◇删除用户   本模块只有管理员才能使用,普通用户不能进入。 《软件工程》实验设计报告                题  目:                院 (系):                年 级:                专 业:                姓 名:                学 号:                教 师:王 军                学 期:2011-2012第二学期                
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值