56,678
社区成员
发帖
与我相关
我的任务
分享
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)
select age,count(*) from (
select age,case when age between 10 and 20 then 1
when age between 21 and 30 then 2
when age between 31 and 40 then 3
end age1
from u
)T
group by age,age1
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)
mysql>