select distinct
cno,count(sno) 总人数,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩
from sc
where sno is not NULL and grade is not null
group by cno
order by cno desc
查询结果如下,按第一列排序,10被排在了下面
9 1 54 54 54
8 4 86 0 51
7 1 70 70 70
6 1 58 58 58
5 1 58 58 58
4 2 85 62 73
3 1 53 53 53
2 6 96 46 74
10 1 65 65 65
1 6 86 58 77
------解决方案--------------------------------------------------------
你的cno字符型吧!
------解决方案--------------------------------------------------------
------解决方案--------------------------------------------------------
- SQL code
select distinctcno,count(sno) 总人数,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩from scwhere sno is not NULL and grade is not nullgroup by cnoorder by to_number(cno,'9999') desc