select语句的优化问题

[sntest]

对于某门课的成绩的表table1，因可能不同班级有同姓名学生，以class和sname作为主键，以下可创建一个表：
create table table1(class varchar(10),sname varchar(10),grade int,primary key(class,sname))
insert into table1(class,sname,grade) values('c1','s1',90)
insert into table1(class,sname,grade) values('c1','s2',88)
insert into table1(class,sname,grade) values('c1','s3',91)
insert into table1(class,sname,grade) values('c2','s1',78)
insert into table1(class,sname,grade) values('c2','s2',89)
insert into table1(class,sname,grade) values('c2','s3',87)
insert into table1(class,sname,grade) values('c2','s4',79)
insert into table1(class,sname,grade) values('c2','s5',89)
问题是选出每个班级、该班考最高分的学生、最高分，以下语句可行，但感觉太长太难看了，
select a.class,a.sname,a.grade from table1 as a inner join (select b.class,max(b.grade) as maxgrade from table1 as b group by b.class) as c on a.grade=c.maxgrade
谁能帮我优化一下，最简单明了的给分。

[pt1314917]

select * from table1 a where not exists(select 1 from table1 where class=a.class and 
grade>a.grade)

[pt1314917]

--或者:
select * from t a where grade in (select max(grade) from t where class =a.class )
--或者:
select * from t a where (select count(distinct grade) from t where class =a.class and grade>=a.grade)=1

[pt1314917]

把t改为表名```

[dawugui]

create table table1(class varchar(10),sname varchar(10),grade int,primary key(class,sname)) 
insert into table1(class,sname,grade) values('c1','s1',90) 
insert into table1(class,sname,grade) values('c1','s2',88) 
insert into table1(class,sname,grade) values('c1','s3',91) 
insert into table1(class,sname,grade) values('c2','s1',78) 
insert into table1(class,sname,grade) values('c2','s2',89) 
insert into table1(class,sname,grade) values('c2','s3',87) 
insert into table1(class,sname,grade) values('c2','s4',79) 
insert into table1(class,sname,grade) values('c2','s5',89) 
go

--如果没有同分情况
--select t.* from table1 t where grade = (select (grade) from table1 where class = t.class)

--如果有同分情况
select m.* from table1 m,
((select class , max(grade) grade from table1 group by class)) n
where m.class = n.class and m.grade = n.grade

drop table table1

/*
class      sname      grade       
---------- ---------- ----------- 
c1         s3         91
c2         s2         89
c2         s5         89
（所影响的行数为 3 行）
*/

[-狙击手-]

create table table1(class varchar(10),sname varchar(10),grade int,primary key(class,sname)) 
insert into table1(class,sname,grade) values('c1','s1',90) 
insert into table1(class,sname,grade) values('c1','s2',88) 
insert into table1(class,sname,grade) values('c1','s3',91) 
insert into table1(class,sname,grade) values('c2','s1',78) 
insert into table1(class,sname,grade) values('c2','s2',89) 
insert into table1(class,sname,grade) values('c2','s3',87) 
insert into table1(class,sname,grade) values('c2','s4',79) 
insert into table1(class,sname,grade) values('c2','s5',89) 
go 
select a.class,a.sname,a.grade 
from table1 as a 
inner join 
    (select b.class,max(b.grade) as maxgrade 
     from table1 as b 
     group by b.class) as c on a.grade=c.maxgrade 

---


select * 
from table1 a
where grade in (select max(b.grade) as maxgrade 
     from table1 as b 
     group by b.class)

select *
from table1 a
where not exists(select 1 from table1 where class = a.class and grade > a.grade)
drop table table1

[nzperfect]

sql语句简单,并不代表效率高
sql语句复要,并不代表效率低

楼主的效率是最高的,不用改成楼上他们的. 呵呵

[nzperfect]

不要恨我拆台 ^ ^

[-狙击手-]

sql语句简单,并不代表效率高
sql语句复要,并不代表效率低

楼主的效率是最高的,不用改成楼上他们的. 呵呵

---

从查询计划 来看 2优于1 ，3优 2
--------------------------------1
select a.class,a.sname,a.grade
from table1 as a
inner join
(select b.class,max(b.grade) as maxgrade
from table1 as b
group by b.class) as c on a.grade=c.maxgrade

--------------------------------2


select *
from table1 a
where grade in (select max(b.grade) as maxgrade
from table1 as b
group by b.class)
-----------------------3
select *
from table1 a
where not exists(select 1 from table1 where class = a.class and grade > a.grade)
drop table table1

[nzperfect]

执行计划只是一个参考,我认为对io的逻辑读写才是决定性能的关键.(个人意见)

配合对io的读写来看:

1优于2优于3

--------------------------------1
select a.class,a.sname,a.grade
from table1 as a
inner join
(select b.class,max(b.grade) as maxgrade
from table1 as b
group by b.class) as c on a.grade=c.maxgrade

--------------------------------2


select *
from table1 a
where grade in (select max(b.grade) as maxgrade
from table1 as b
group by b.class)
-----------------------3
select *
from table1 a
where not exists(select 1 from table1 where class = a.class and grade > a.grade)
drop table table1

[sntest]

2是错的，加以下一条就能发现错误
insert into table1(class,sname,grade) values('c1','s4',89)