mysql数据库有一张学生成绩表grade,数据结构及数据如下:
现在的需求是:分数score升序排列,根据grade表总数的20%,30%,30%,10%,10%分A、B、C、D、E5个区间,
取出A、B、C、D、E5个区间中分数的最小值和最大值。
请问sql应该如何写??请大佬们不吝赐教,非常感激。谢谢!
现在的需求是:分数score升序排列,根据grade表总数的20%,30%,30%,10%,10%分A、B、C、D、E5个区间,
取出A、B、C、D、E5个区间中分数的最小值和最大值。
请问sql应该如何写??请大佬们不吝赐教,非常感激。谢谢!
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | zs1 | 86 |
| 2 | zs2 | 85 |
| 3 | zs3 | 84 |
| 4 | zs4 | 83 |
| 5 | zs5 | 82 |
| 6 | zs6 | 81 |
| 7 | zs7 | 80 |
| 8 | zs8 | 79 |
| 9 | zs9 | 95 |
| 10 | zs10 | 77 |
+------+------+-------+
10 rows in set (0.00 sec)mysql> select
-> CASE
-> when rp<=0.2 then 'A'
-> when rp<=0.5 then 'B'
-> when rp<=0.8 then 'C'
-> when rp<=0.9 then 'D'
-> else 'E'
-> end as level,
-> min(score) as minScore,max(score) as maxScore
-> from (
-> select score,
-> (select count(*) from grade where score<=g.score)/(select count(*) from grade) as rp
-> from grade g
-> ) v
-> group by CASE
-> when rp<=0.2 then 'A'
-> when rp<=0.5 then 'B'
-> when rp<=0.8 then 'C'
-> when rp<=0.9 then 'D'
-> else 'E'
-> end ;
+-------+----------+----------+
| level | minScore | maxScore |
+-------+----------+----------+
| A | 77 | 79 |
| B | 80 | 82 |
| C | 83 | 85 |
| D | 86 | 86 |
| E | 95 | 95 |
+-------+----------+----------+
5 rows in set (0.03 sec)mysql>