求教一个sql问题
有一张StudentAchievement表
StudentID CourseID Achievement(成绩)
统计并显示每门课程的课程编、课程总分和课程平均分,并满足课程平均分80分以上的
我编了这样一条语句,
select sum(Achievement), avg(Achievement) as avgScore,courseid from StudentAchievement where avgScore> 80 group by courseid
但显示avgScore无效,请教有什么好方法?
[解决办法]
select sum(Achievement), avg(Achievement) as avgScore,courseid from StudentAchievement group by courseid having(avg(Achievement))> 80
[解决办法]
select CourseID , sum(Achievement) 课程总分, avg(Achievement) 课程平均分
from StudentAchievement
group by CourseID
having (avg(Achievement)) > = 80
[解决办法]
select sum(Achievement), avg(Achievement) as avgScore,courseid from StudentAchievement group by courseid having avg(Achievement)> 80
[解决办法]
where 后面不能使用聚合函数:sum、avg、max、min、count,需要使用having 代替where
注意:having 必须放到sql语句的最后面