统计各分数区间数据
现在要统计:<50、50-60、60-70、70-80、80-90、90-100、>=100分数区间的人数;
利用 INTERVAL 划出7个区间;
再利用 elt 函数将7个区间分别返回一个列名,如下SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT elt(INTERVAL(score, 0, 50, 60, 70, 80, 90, 100), '<50', '50-60', '60-70', '70-80', '80-90', '90-100', '>=100') as score_level, count(name) as counts
-> FROM class
-> GROUP BY elt(INTERVAL(score, 0, 50, 60, 70, 80, 90, 100), '<50', '50-60', '60-70', '70-80', '80-90', '90-100', '>=100');
score_level |
counts |
50-60 |
1 |
60-70 |
3 |
70-80 |
1 |
80-90 |
2 |
90-100 |
1 |
<50 |
1 |
>=100 |
1 |
7 rows in set (0.00 sec)