2016-04-26 04:51

# SQL 查询问题 group by

student(sno,sname,,sdept)
course(cno,cname,ccredit)

//查询CS系成绩最高的学生的学号、姓名以及成绩

select student.sno,sname,grade from student,sc where student.sno = sc .sno
and grade=(select max(grade) from sc where sc.sno = sno and sdept = 'cs')

//查询每个系的最高成绩
select sdept,max(grade) from sc,student where sc.sno=student.sno group by sdept;这样写把每个系的最高分列出来了，当我想把sno列出来，所以加上了sc.sno，下面的就什么结果也没有，是错的。
--select sdept,sc.sno,max(grade) from sc,student where sc.sno=student.sno group by sdept,sc.sno

select x.sno,sdept,grade from student x,sc where x.sno = sc.sno and grade =
(select max(grade) from sc,student where sc.sno=student.sno and sdept=x.sdept) //这个结果又是对的

• 点赞
• 写回答
• 关注问题
• 收藏
• 邀请回答

#### 2条回答默认 最新

• danielinbiti 2016-04-26 05:37
已采纳
`````` select student.sno,sname,grade from student,sc where student.sno = sc .sno
and grade=(select max(grade) from sc where sc.sno = sno and sdept = 'cs')
这句不对的原因在于，下面子句中，左边的sdept不存在，在sc表中没有
(select max(grade) from sc where sc.sno = sno and sdept = 'cs')
``````
点赞 打赏 评论
• Robot-S 2016-04-26 05:02

---所有书籍价格的统计
select sum(price)总价,avg(price)均价,max(price)最高价,min(price)最低价
from titles

---统计where条件的记录