SQL 疑难语句请教

Question

--查询属于level为1的class(即一班）的学生的成绩 
select grade
from Class,Achieve
where Class.id=Achieve.id and level=1

Answer 1

select a.id , a.name , a.classid , b.grade 
from Student a , Achieve b , Class c 
where a.classid = c.id and a.id = b.id and c.level = 1

Answer 2

create table Class (id int,name varchar(10),level int)
insert into Class values(1 ,         '一班',                1) 
insert into Class values(2 ,         '二班',                2) 
insert into Class values(3 ,         '三班',                3) 
create table Student (id int,name varchar(10),classId int)
insert into Student values(1,          '学生1',              1 )
insert into Student values(2,          '学生2',              1 )
insert into Student values(3,          '学生3',              1 )
insert into Student values(7,          '学生4',              2 )
insert into Student values(8,          '学生5',              2 )
insert into Student values(9,          '学生6',              2 )
insert into Student values(4,          '学生7',              3 )
insert into Student values(5,          '学生8',              3 )
insert into Student values(6,          '学生9',              3 ) 
create table Achieve (id int,studentId int,  grade int)
insert into Achieve values(1,      1,          80)     
insert into Achieve values(2,      2,          81) 
insert into Achieve values(3,      3,          82 )
insert into Achieve values(4,      4,          83 )
insert into Achieve values(5,      5,          84 )
insert into Achieve values(6,      6,          85 )
insert into Achieve values(7,      7,          86 )
insert into Achieve values(8,      8,          87 )
insert into Achieve values(9,      9,          88 )
go
select a.id , a.name , c.name , b.grade 
from Student a , Achieve b , Class c 
where a.classid = c.id and a.id = b.id and c.level = 1drop table class,Student,Achieve/*
id          name       name       grade       
----------- ---------- ---------- ----------- 
1           学生1        一班         80
2           学生2        一班         81
3           学生3        一班         82（所影响的行数为 3 行）
*/

Answer 3

将最低的两个学生与layer为2(即二班，三班）里成绩最高的学生的所在班级互换 就是实现 把学生1的班级换成二班，学生2换3班 
学生6换1班，学生9换1班 这个还没看明白,请详细说说.

Answer 4

就是实现如下的学生1 与学生2 在一班里成绩最低，所以要将他们降入layer为2的班暂时按class的ID顺序降学生1分数最低，二班为layer里ID最靠前的，所以学生1降二班，二班的学生6在二班里分数最高所以学生6进一班学生2与学生9基于同样的道理互调最后要实现成如下的效果：
id         name              classId 1          学生1              2 
2          学生2              3 
3          学生3              1 
7          学生4              2 
8          学生5              2 
9          学生6              1 
4          学生7              3 
5          学生8              3 
6          学生9              1 

Answer 5

--<!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!>

Answer 6

以上用一条语句完成,代码比较烦琐,借助临时表,可将语句简化
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',1
UNION ALL SELECT 2,'学生2',1 
UNION ALL SELECT 3,'学生3',1 
UNION ALL SELECT 7,'学生4',2 
UNION ALL SELECT 8,'学生5',2 
UNION ALL SELECT 9,'学生6',2 
UNION ALL SELECT 4,'学生7',3 
UNION ALL SELECT 5,'学生8',3 
UNION ALL SELECT 6,'学生9',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 
SELECT s.id,name,classid,a.grade INTO # FROM @student s INNER JOIN @achieve a ON a.studentid=s.id
UPDATE x SET x.classid=
CASE WHEN x.classid=1 THEN
CASE (SELECT count(*) FROM # WHERE classid=x.classid AND grade<y.grade) WHEN 1 THEN
2
WHEN 0 THEN
3
ELSE 1 END ELSE
CASE WHEN y.grade=(SELECT MAX(grade) FROM # WHERE classid=x.classid) THEN
1
ELSE x.classid END
END
FROM @student x INNER JOIN @achieve y ON x.id=y.studentid
DROP TABLE #

Answer 7

谢谢楼上的！不过实际的问题远比我给出的那个例子复杂1: 班级不是只有3个，而是有N个，无法硬编码2：层次不是只两层，而是3层,甚至更多(将来要考虑）实际上 每个班的结构应该如下id         name             level      pos
1          一(1)班                1            12          二(1)班                2            1
3          二(2)班                2            24          三(1)班                3            1
5          三(2)班                3            2
6          三(3)班                3            3
7          三(4)班                3            4
学生调换班级是基于pos的 如 每个班级与低层的(pos*2-1) (pos*2)班级调换，
如二（1）班 pos为1 所以和他关联的是pos为1，2的  三(1)班, 三(2)班
二(2)班 pos为2  所以和他关联的是pos为3，4的  三(3)班, 三(4)班上面的数据 我要实现这样的效果把一(1)班分数最低的两个分别放到二(1)班，二(2)班
二(1)班，二(2)班各自班里分数最高的1个（合起两个）放入一(1)班，同时把二(1)班分数最低的两个分别放到三(1)班，三(2)班
三(1)班，三(2)班各自班里分数最高的1个（合起两个）放入二(1)班，二（2）班类似。。
我估计一条SQL语句是写不了的存储过程也是很难写 -_-大家帮忙，谢谢！

Answer 8

一样的,你看到没有, 我的语句中没用到class表.
class表中不是有level吗?
再多连一次,在update的case when的嵌套中再多嵌一次对level的检测即可. 所以不需要给定值.        CASE WHEN x.classid=1 THEN
            CASE (SELECT count(*) FROM # WHERE classid=x.classid AND grade<y.grade) WHEN 1 THEN
                2
            WHEN 0 THEN
                3
            ELSE 1 END这里第一层的 CASE WHEN x.classid=1 THEN 就会被替换为对 level的比较,当然不是直接写level值.
道理是一样的, 将最低成绩行的 classid 替换为 level比自己原classid所在的level低一级的classid,将 最高成绩行替换为 level 比自己原classid所在的level高一级的classid . 当然, 比自己高,或比自己低的level不存在的话,不做替换. 同样,如果不是最低成绩,也是不是最高成绩,也不替换.多嵌一两次case when, 多连次表就解决了.因为你最初的要求指定了将classid=1的换为2和3,所以我才写了定值.

Answer 9

select a.studentid,b.name
from achieve a,student b
where a.studentid=b.id and b.classid in (select id from class where level=1)对于互换，请再说明规则，倒数第一与二与三班
哪个班交换，倒数第二呢？

Answer 10

谢谢fcuandy 的回复能不能写完整的明天我试试

Answer 11

语句实现了, 每班向下一级班级置换一人.
以下的例子. 
classid=1的班级,将最低成绩的学生放到class2,最高成绩的人无从可放
class2最低成绩的人放到class3,最高成绩的人放到class1
class3最低成绩的人放到(无从可放),最高成绩的人放到class2要实现每次替换两人,将语句中的 when 0 改为 when 1即可.
要实现不定人数,按level遵循某种算法,将语句中 when 0 改为 when fn( (select level from @class where id=x.classid) ) 即可
fn代表你的某种算法, select出的值为算法中的参数--<!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.idSELECT * FROM @student-- s,@class c where c.id=s.classid

Answer 12

把一(1)班分数最低的两个分别放到二(1)班，二(2)班 
二(1)班，二(2)班各自班里分数最高的1个（合起两个）放入一(1)班， 按你这个要求, class 表中信息不足.
一(1)班中最低两个, 最低的放在二(1)还是二(2),次最低放在二(2)还是二(1)
如果给class表加列, 对 level相同的行再进行分级, 那么写法会简单.如不给class表加列,那么只能随机取一条先放在某个班级, 然后将另一条放在未放记录的班级. 
就这一点设置,就会很大程度上加大语句的复杂程度.

Answer 13

刚才打了半天,一不小心按错健了,不知道csdn设置了什么快健,不按f5,页面也会跳...白打了
如果不加列, 那么是随机插了一条,在update批没完成前,不能确定哪条已插入了,所以一条语句不能完成.如果不加列,但利用class表本身的信息,比如id列来确定level相同的两行的顺序,那么是可以的.比如 classid=1的最低的两个学生 的class.level=1
那么他们将被放到 class.level=2 的班级中, 有两个行 classid=2和classid=3
可以将 最低学生放在 MAX( rows{class.level=2}.classid }
即,class.level=2的行中classid最大的那个班级中, 即其classid被置为3
然后将 次最低学生放在MIN( rows{class.level=2}.classid }
即,class.level=2的行中classid最小的那个班级中,即其classid被置为2晚了,要睡了,先不写了.

Answer 14

我说了啊， 看9楼
实际上 每个班的结构应该如下 id         name             level      pos 
1          一(1)班                1            1 2          二(1)班                2            1 
3          二(2)班                2            2 4          三(1)班                3            1 
5          三(2)班                3            2 
6          三(3)班                3            3 
7          三(4)班                3            4 学生调换班级是基于pos的 如 每个班级与低层的(pos*2-1) (pos*2)班级调换， 
如二（1）班 pos为1 所以和他关联的是pos为1，2的  三(1)班, 三(2)班 
二(2)班 pos为2  所以和他关联的是pos为3，4的  三(3)班, 三(4)班 分数最低的放level+1中 (pos*2-1)位置的，另外一个放 pos*2

Answer 15

Class 
id         name             level          pos  
1          一(1)班                1            1  2          二(1)班                2            1  
3          二(2)班                2            2  4          三(1)班                3            1  
5          三(2)班                3            2  
6          三(3)班                3            3  
7          三(4)班                3            4  Student 结构如下 
id         name              classId 
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 
Achieve,结构如下 
id   studentId   grade    
1       1          80     
2       2          81 
3       3          82 
4       4          80     
5       5          81 
6       6          82 
7       7          80     
8       8          81 
9       9          82 
10      10          80     
11      11          81 
12      12          82 
13      13          80     
14      14          81 
15      15          82 
16      16          80     
17      17          81 
18      18          82 
19      19          80     
20      20          81 
21      21          82 

Answer 16

--<!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>

Answer 17

如果用临时表,并且想一次完成的话,那么都不快.因为你是从每个班级取 2 个学生, 那么插临时表时,实际上是内连后, 设内连后数据集为 tb 再自连,每类(以classid分类)最高一个,最低二个
自连的写法
select * from tb a where 1>(select count(id) from tb where a.classid=classid and id>a.id) --每类取最高一条,若有重复值会多条,若id唯一则排除这种情况
select * from tb a where id=(select max(id) from tb wehre a.classid=classid) --同上
select * from tb a where not exists(select 1 from tb where a.classid=classid and id>a.id) --同上
select * from tb a inner join (select max(id) mi from tb group by classid) b on a.id=mi --同上
select * from tb a where id in(select top 1 id from tb where a.classid=classid order by id desc) --同上/*如果取每个班最高成级的学生,这五种写法都可以.
如果取每个班最低的两个学生,那么 第1,5种方法都可以,只是稍作修改*/select * from tb a where 2>(select count(id) from tb where a.classid=classid and id<a.id)
select * from tb a where id in(select top 2 id from tb where a.classid=classid order by id)这种语句的效率普遍都是不高的. 如果只是取一条最大(或最小)可以使用max聚合,或 in ( top 1)的写法,这两种效率稍高一点.
那么你要将修改的记录插入临时表,实际上插入的是从上面五个语句中任选一句, 再从下面两个语句中任选一句,进行 union.,插入临时表.不要忘了,这个 tb 是 class与achieve联表的结果, 示例语句中 id 的比较,其实是 grade之间的比较.