create table u(
id int,
name varchar(8),
age int
);
mysql> select * from u;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | tonny | 18 |
| 1 | wendy | 30 |
| 1 | james | 25 |
| 1 | simon | 22 |
| 1 | flynn | 35 |
| 1 | nancy | 42 |
| 1 | olivia | 51 |
| 1 | linda | 20 |
+------+--------+------+
8 rows in set (0.00 sec)
要求得到效果如下:
统计出不同年龄段的人数:
年龄段 人数
18-20 2
21-30 3
31-40 1
41-50 1
> 50 1
mysql> select elt(interval(age,18,21,31,41,51),
-> '18-20',
-> '21-30',
-> '31-40',
-> '41-50',
-> '> 50'
-> ) as `年龄段`
-> ,count(*) as `人数`
-> from u
-> group by elt(interval(age,18,21,31,41,51),
-> '18-20',
-> '21-30',
-> '31-40',
-> '41-50',
-> '> 50'
-> );
+--------+------+
| 年龄段 | 人数 |
+--------+------+
| 18-20 | 2 |
| 21-30 | 3 |
| 31-40 | 1 |
| 41-50 | 1 |
| > 50 | 1 |
+--------+------+
5 rows in set (0.03 sec)
mysql>