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,自己没做出来希望大家帮我看看 谢谢
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,自己没做出来希望大家帮我看看 谢谢
解决方案 »
- BOM死循环 超级难!!!!! 欢迎大仙
- 一个很复杂的sql报表麻烦各位高人看看!
- 达人进来帮忙看下,多谢
- 急!!oracle中函数包编译不能完成?报锁库!!
- 用oracle操作sqlserver数据库的数据,oracle是否有sqlserver中的 OpenDataSource吗???????
- 查询的问题
- oracle 有关for的疑问
- 信誉值超过120,散分
- Oracel 9i OEM 启动问题
- 安装Oracle817后,windows2000的FTP不能用了,如何解决?
- 请大家帮我把幕后黑手揪出来
- Net Configuration Assistant的快捷方式被删了,要怎么样才能运行Net Configuration Assistant?
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;这个试试吧
用这个试试·······?
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对于问题二我想了下 有点瞎搞 呵呵
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 91select 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 64select 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) ;
平均分的那个..不是很实际,刚好会考到平均分的,如果对平均分取整可能性还稍微大点
你应该学习下,这是个很重要的关键字
任何一本教材里应该都会有说明
换成下面的
(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);
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 结贴
我也是刚学oracle的,所以碰上这种题目,自己也试一试,当是做练习了