对于某门课的成绩的表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
谁能帮我优化一下,最简单明了的给分。
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
谁能帮我优化一下,最简单明了的给分。
select * from table1 a where not exists(select 1 from table1 where class=a.class and
grade>a.grade)
--或者:
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
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.gradedrop table table1/*
class sname grade
---------- ---------- -----------
c1 s3 91
c2 s2 89
c2 s5 89
(所影响的行数为 3 行)
*/
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
sql语句简单,并不代表效率高
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
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
insert into table1(class,sname,grade) values('c1','s4',89)