sql 代码
create
table
u(
id
int
,
name
varchar
(8),
age
int
);
Load
Data InFile
'C:/data.txt'
Into
Table
`u` Fields Terminated
By
','
;
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
*
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)
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)