要求: 查出班(class)里所有学生成绩(source)均大于60的获奖(rewarde=1)班级create table test_table
(
sn int,
class int,
source int,
reward int
);insert into test_table values (1, 1, 60, 1);
insert into test_table values (2, 1, 60, 1);
insert into test_table values (3, 1, 60, 1);
insert into test_table values (4, 1, 60, 1);
insert into test_table values (5, 4, 60, 1);
insert into test_table values (6, 4, 45, 1);
insert into test_table values (7, 4, 60, 1);
insert into test_table values (8, 4, 60, 1);
insert into test_table values (9, 4, 60, 1);select sn 学号, class 班级, source 成绩, reward 是否获奖 from test_table;
学号 班级 成绩 是否获奖
----- ----- ----- --------
1 1 60 1
2 1 60 1
3 1 60 1
4 1 60 1
5 4 60 1
6 4 45 1
7 4 60 1
8 4 60 1
9 4 60 1==================================================
我写的语句如下:
select distinct class from test_table a where rewarde = 1 and not exists (select 1 from test_table b where a.class=b.class and source < 60);但是效率太慢了,不知道怎么优化好
(
sn int,
class int,
source int,
reward int
);insert into test_table values (1, 1, 60, 1);
insert into test_table values (2, 1, 60, 1);
insert into test_table values (3, 1, 60, 1);
insert into test_table values (4, 1, 60, 1);
insert into test_table values (5, 4, 60, 1);
insert into test_table values (6, 4, 45, 1);
insert into test_table values (7, 4, 60, 1);
insert into test_table values (8, 4, 60, 1);
insert into test_table values (9, 4, 60, 1);select sn 学号, class 班级, source 成绩, reward 是否获奖 from test_table;
学号 班级 成绩 是否获奖
----- ----- ----- --------
1 1 60 1
2 1 60 1
3 1 60 1
4 1 60 1
5 4 60 1
6 4 45 1
7 4 60 1
8 4 60 1
9 4 60 1==================================================
我写的语句如下:
select distinct class from test_table a where rewarde = 1 and not exists (select 1 from test_table b where a.class=b.class and source < 60);但是效率太慢了,不知道怎么优化好
SELECT DISTINCT CLASS
FROM TEST_TABLE
WHERE REWARD = 1
AND SOURCE >= 60;你写的这种情况可以把DISTINCT去掉:
select class from test_table a where rewarde = 1 and not exists (select 1 from test_table b where a.class=b.class and source < 60);
minus
select class from test_table where source < 60;
from test_table
where reward = 1
and class not in
(
select class
from test_table
where source < 60
);
FROM TEST_TABLE
WHERE REWARD = 1
AND SOURCE >= 60;
我觉得这么写很好,干吗要用NOT EXISTS呢
FROM test_table a
WHERE reward = 1
GROUP BY CLASS
HAVING MIN(SOURCE)>=60