牛人们，进来帮我写条查询语句吧。。很简单的啊

vsfigo 2009-07-06 03:32:25

select * from student (学生表)

SNO SNAME SSEX SBIRTHDAY CLASS
108 曾华男 1977-9-1 95033
105 匡明男 1975-10-2 95031
107 王丽女 1976-1-23 95033
101 李军男 1976-2-20 95033
109 王芳女 1975-2-10 95031
103 陆君男 1974-6-3 95031

select * from score (成绩表)

SNO CNO DEGREE
103 3-245 86.0
105 3-245 75.0
109 3-245 68.0
103 3-105 92.0
105 3-105 88.0
109 3-105 76.0
101 3-105 64.0
107 3-105 91.0
108 3-105 78.0
101 6-166 85.0
107 6-166 79.0
108 6-166 81.0

请问怎么求不同班级中最高分的学生的详细信息（SNO SNAME SSEX SBIRTHDAY MAX(degree)）
最好能把不同班级中最高分，最低分，平均分的学生详细信息都查询出来
真的想了好久没想出来。无语了

请大家帮我看看把这是我自己想的查询SQL，自己没做出来。。。希望大家帮我看看谢谢

...全文

68 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

vsfigo 2009-07-07

打赏
举报

select s.*, ss.degree
from score ss, student s
where ss.sno=s.sno
and ss.degree in (select max(sc.degree) from score sc,student s where sc.sno=s.sno group by s.class)

select s.*,sc.degree
from score sc, student s
where sc.sno=s.sno
and sc.degree in (select min(sc.degree) from score sc,student s where sc.sno=s.sno group by s.class)

3Q 结贴。。。

小灰狼W 2009-07-07

打赏
举报

11楼的说得对，没考虑周全，应该加上关于class的判定
我也是刚学oracle的，所以碰上这种题目，自己也试一试，当是做练习了

asdf_ndsc 2009-07-06

打赏
举报

9 楼的少了考虑 class

b.degree in (select max(b.degree)
换成下面的
(b.degree,a.class) in (select a.class,max(b.degree)

sql 调整后：
select sno,a.sname,a.sex,a.birthday,a.class,b.degree "max(degree)" from test_a a right join test_b b using(sno)
where (b.degree,a.class) in (select a.class,max(b.degree) from test_b b left join test_a a using(sno) group by a.class);

asdf_ndsc 2009-07-06

打赏
举报

9 楼太热心了。

小灰狼W 2009-07-06

打赏
举报

对不同班级进行分组，应该用group by
你应该学习下，这是个很重要的关键字
任何一本教材里应该都会有说明

小灰狼W 2009-07-06

打赏
举报

 create table test_a(sno number primary key,sname varchar2(20),sex varchar2(10),birthday date,class number);

 insert into test_a values(110,'游龙','男',date'2009-6-25',95031);

 insert into test_a values(108,'曾华','男',date'1977-9-1',95033);

 insert into test_a values(105,'匡明','男',date'1975-10-2',95031);

 insert into test_a values(107,'王丽','女',date'1976-1-23',95033);

 insert into test_a values(101,'李军','男',date'1976-2-20',95033);

 insert into test_a values(109,'王芳','女',date'1975-2-10',95031);

 insert into test_a values(103,'陆军','男',date'1974-6-3',95031);

 

 create table test_b(sno number,c_no varchar(20),degree real,constraint pk_test_b primary key(sno,c_no));

 insert into test_b values(103,'3-245',86);

 insert into test_b values(105,'3-245',75.0);

 insert into test_b values(109,'3-245',68);

 insert into test_b values(103,'3-105',92);

 insert into test_b values(105,'3-105',88);

 insert into test_b values(109,'3-105',76);

 insert into test_b values(101,'3-105',64);

 insert into test_b values(107,'3-105',91);

 insert into test_b values(108,'3-105',78);

 insert into test_b values(101,'6-166',85);

 insert into test_b values(107,'6-166',79);

 insert into test_b values(108,'6-166',81);



select sno,a.sname,a.sex,a.birthday,a.class,b.degree "max(degree)" from  test_a a right join test_b b using(sno)

  where b.degree  in (select max(b.degree) from test_b b left join test_a a using(sno) group by a.class);



SNO	SNAME	SEX	BIRTHDAY	CLASS	max(degree)

103	陆军	男	1974-6-3	95031	92

107	王丽	女	1976-1-23	95033	91



select sno,a.sname,a.sex,a.birthday,a.class,b.degree "min(degree)" from  test_a a right join test_b b using(sno)

  where b.degree  in (select min(b.degree) from test_b b left join test_a a using(sno) group by a.class)  ;



SNO	SNAME	SEX	BIRTHDAY	CLASS	min(degree)

109	王芳	女	1975-2-10	95031	68

101	李军	男	1976-2-20	95033	64



select sno,a.sname,a.sex,a.birthday,a.class,b.degree "avg(degree)" from  test_a a right join test_b b using(sno)

  where b.degree  in (select avg(b.degree) from test_b b left join test_a a using(sno) group by a.class) ;

平均分的那个..不是很实际，刚好会考到平均分的，如果对平均分取整可能性还稍微大点

vsfigo 2009-07-06

打赏
举报

跟科目没关系，我又不是求每个科目的最XXX分，我只通过sno(学生编号)查询某个人的最XXX分就OK了(不管他是什么科目)

下面我自己写了个语句，但是有点问题，望大家帮我改改。

SQL1：

select
distinct st.sno,st.sname,st.ssex,st.sbirthday,st.class, mx.degree --学生的详细信息和最高分
from
score sc, --成绩表
student st,--学生表
(
select s.sno, sc.degree from score sc, student s
where degree = (select max(sc.degree) from score sc,student s where s.class='95033' and sc.sno=s.sno) and sc.sno=s.sno
union
select s.sno, sc.degree from score sc, student s
where degree = (select max(sc.degree) from score sc,student s where s.class='95031' and sc.sno=s.sno) and sc.sno=s.sno
) mx
where sc.sno=st.sno and sc.sno=mx.sno and st.sno=mx.sno

这是结果，也是我想要的结果。就是问题一：不同班级中最高分的学生的详细信息（SNO SNAME SSEX SBIRTHDAY MAX(degree)）

SNO SNAME SSEX SBIRTHDAY CLASS DEGREE
103 陆君男 1974-6-3 95031 92
107 王丽女 1976-1-23 95033 91

但是问题又来了。。。
我怎么把下面的查询语句的结果(集合),去代替SQL1中union的两句话的 s.class='95033' s.class='95031' ？？

select distinct s1.class from student s1, student s2 where s1.class=s2.class;

查询结果：
CLASS
95031
95033

对于问题二我想了下有点瞎搞呵呵。。。

zengxing520 2009-07-06

打赏
举报

select st.class,max(sc.degree),min(sc.degree),avg(sc.degree) from student st,score sc where st.sno=sc.sno group by st.class;
用这个试试·······？

mingyue19850801 2009-07-06

打赏
举报

SELECT MAX(sc.degree),
MIN(sc.degree),
AVG(sc.degree),
MAX(sc.sno),
MAX(st.name),
MAX(st.sex),
MAX(st.birthday)
FROM score sc,
student st
WHERE st.sno = sc.sno
GROUP BY sc.cno;这个试试吧

inthirties 2009-07-06

打赏
举报

select a.* from student a, (select t1.CLASS, t2.CNO, max(t2.score) c1, min(t2.score) c2, avg(t2.score) c3 from student t1, score t2 group by t1.CLASS, t2.CNO) b, score c where a.SNO=b.SNO and b.CNO = c.CNO and (c.score = b.c1 or c.score=b.c2 or c.score=c3)

试试这个，没有时间测试了，要去忙去了，比较复杂，等回来了仔细试试，找出个简化一点的。