在网上看到的。。data.txt内数据如下:1,tonny,18
1,wendy,30
1,james,25
1,simon,22
1,flynn,35
1,nancy,42
1,olivia,51
1,linda,20
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 怎么处理? 谢谢 。。
1,wendy,30
1,james,25
1,simon,22
1,flynn,35
1,nancy,42
1,olivia,51
1,linda,20
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 怎么处理? 谢谢 。。
+------+--------+------+
| 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>
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
ELT(CEILING(age/10)-1,'18-20','21-30','31-40','41-50','> 50')