SELECT MAX(score),MIN(score), AVG(score), SUM(CASE WHEN score>=60 AND score<70 THEN 1 ELSE 0 END), SUM(CASE WHEN score>=70 AND score<80 THEN 1 ELSE 0 END), SUM(CASE WHEN score>=80 THEN 1 ELSE 0 END) FROM record WHERE cno=xx
select cno,max(f1),min(f1),avg(f1),sum(if(f1<60,1,0)), sum(if(f1 between 60 and 80,1,0)) from tt group by cno
按一楼的方法即可。也可以用SUM(IF来代码。select max(score) as 最高分, min(score) as 最低分, avg(score) as 平均分, sum(if(score<60,1,0)) as 及格, sum(if(score>=60 and score<85,1,0)) as 良好, sum(if(score>=85,1,0)) as 优秀 FROM record WHERE cno=xx
MAX(score),MIN(score),
AVG(score),
SUM(CASE WHEN score>=60 AND score<70 THEN 1 ELSE 0 END),
SUM(CASE WHEN score>=70 AND score<80 THEN 1 ELSE 0 END),
SUM(CASE WHEN score>=80 THEN 1 ELSE 0 END)
FROM record
WHERE cno=xx
sum(if(f1 between 60 and 80,1,0))
from tt group by cno
min(score) as 最低分,
avg(score) as 平均分,
sum(if(score<60,1,0)) as 及格,
sum(if(score>=60 and score<85,1,0)) as 良好,
sum(if(score>=85,1,0)) as 优秀
FROM record
WHERE cno=xx