• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

select语句的优化问题

sntest 2008-03-05 03:17:10
对于某门课的成绩的表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
谁能帮我优化一下,最简单明了的给分。
...全文
85 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
sntest 2008-03-05
2是错的,加以下一条就能发现错误
insert into table1(class,sname,grade) values('c1','s4',89)
回复
nzperfect 2008-03-05
执行计划只是一个参考,我认为对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
回复
-狙击手- 2008-03-05
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 2008-03-05
不要恨我拆台 ^ ^
回复
nzperfect 2008-03-05

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

楼主的效率是最高的,不用改成楼上他们的. 呵呵
回复
-狙击手- 2008-03-05


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

回复
dawugui 2008-03-05
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 行)
*/
回复
pt1314917 2008-03-05
把t改为表名```
回复
pt1314917 2008-03-05

--或者:
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 2008-03-05

select * from table1 a where not exists(select 1 from table1 where class=a.class and
grade>a.grade)
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-05 03:17
社区公告
暂无公告