sql语句 - 编程论坛

#2

cnfarer2010-05-11 14:45

试试吧！（有的语句可能要调整后才能执行）
1. select s.name,b.classname,max(c.score) from class b inner jion student s on b.classid=s.classid  inner join score c on s.studentid=c.studentid group by s.name,b.classid
2. select s.name,b.classname,max(c.score),c.subject from class b inner jion student s on b.classid=s.classid  inner join score c on s.studentid=c.studentid group by s.name,c.subject
3. select s.name,b.classname,min(c.score), c.subject from class b inner jion student s on b.classid=s.classid  inner join score c on s.studentid=c.studentid group by s.name,c.subject

#3

sky_yang_sky2010-05-11 16:35

不行,你没看我的题目吧

#4

aei1352010-05-12 09:07

1.select class.classname,x.studentname,max(tscore) mscore
from (select student.classid,student.studentid,student.studentname,sum(score) tscore from score inner join student on score.studentid=student.studentid
group by student.classid,student.studentid,student.studentname) x
inner join class on x.classid=class.classid
group by class.classname,x.studentname
2.select student.studentname,class.classname,y.score,y.subject
from student inner join class on student.classid=class.classid
inner join
(select score.scoreid,score.studentid,score.subject,score.score from score inner join
(select scoreid,subject,max(score.score) mscore from score
group by scoreid,subject,mscore) x on score.scoreid=x.scoreid and score.score=x.mscore) y
on student.studentid=y.studentid
3.select student.studentname,class.classname,y.score,y.subject
from student inner join class on student.classid=class.classid
inner join
(select score.scoreid,score.studentid,score.subject,score.score from score inner join
(select scoreid,subject,min(score.score) mscore from score
group by scoreid,subject,mscore) x on score.scoreid=x.scoreid and score.score=x.mscore) y
on student.studentid=y.studentid

#5

autobot2010-05-12 09:51

第一，经典，学习的好例子；第二绝对是高手，佩服了。

#6

aina2010-05-17 21:02

厉害啊！我的目标啊

#7

autobot2010-05-19 11:10

我回去建了一个库试了一下，有个问题，四楼的语句执行出来的是所有人的成绩分组，必需手动找每班出现的第一个人才是楼主要求的数据，能不能一上来就显示四行数据，就是最后要求的结果呢，谢谢四楼的前辈。

#8

aei1352010-05-19 18:06

哪个语句啊?

#9

autobot2011-03-23 15:21

回复 8楼 aei135

三个都是呀。