有一个学生表,有id和分数两列。
低于60分为不及格
低于75分为及格
低于85分为良
高于85分为优
数据初始化。
![](//img.blog.itpub.net/blog/attachment/201401/17/29254281_1389973300pIdI.png?x-oss-process=style/bb)
低于60分为不及格
低于75分为及格
低于85分为良
高于85分为优
数据初始化。
create table student
(
student_id number,
grade number
);
insert into student select rownum,rownum+50 from dual connect by level<50;
commit;
使用Sign函数统计一个班的学生,优、良、及格和不及格的人数。(
student_id number,
grade number
);
insert into student select rownum,rownum+50 from dual connect by level<50;
commit;
SQL> select * from student;
STUDENT_ID GRADE
---------- ----------
1 51
2 52
3 53
4 54
5 55
6 56
7 57
8 58
9 59
10 60
11 61
12 62
13 63
14 64
15 65
16 66
17 67
18 68
19 69
20 70
21 71
22 72
23 73
24 74
25 75
26 76
27 77
28 78
29 79
30 80
31 81
32 82
33 83
34 84
35 85
36 86
37 87
38 88
39 89
40 90
41 91
42 92
43 93
44 94
45 95
46 96
47 97
48 98
49 99
已选择49行
STUDENT_ID GRADE
---------- ----------
1 51
2 52
3 53
4 54
5 55
6 56
7 57
8 58
9 59
10 60
11 61
12 62
13 63
14 64
15 65
16 66
17 67
18 68
19 69
20 70
21 71
22 72
23 73
24 74
25 75
26 76
27 77
28 78
29 79
30 80
31 81
32 82
33 83
34 84
35 85
36 86
37 87
38 88
39 89
40 90
41 91
42 92
43 93
44 94
45 95
46 96
47 97
48 98
49 99
已选择49行
select student_level,count(*)
from
(
select grade,decode(student_level,'优',1,'良',2,'及格',3,4) sort,student_level
from
(
select grade,
case
when sign(grade-60)=-1 then '不及格'
when sign(grade-75)=-1 then '及格'
when sign(grade-85)=-1 then '良'
when sign(grade-100)=-1 then '优'
end student_level
from student
)
)
group by student_level,sort
order by sort;
from
(
select grade,decode(student_level,'优',1,'良',2,'及格',3,4) sort,student_level
from
(
select grade,
case
when sign(grade-60)=-1 then '不及格'
when sign(grade-75)=-1 then '及格'
when sign(grade-85)=-1 then '良'
when sign(grade-100)=-1 then '优'
end student_level
from student
)
)
group by student_level,sort
order by sort;
![](http://img.blog.itpub.net/blog/attachment/201401/17/29254281_1389973300pIdI.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1072376/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1072376/