SQL语句将学生升降级.

原贴地址

http://topic.csdn.net/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]