1
2
3
4
5
6
7
8
9
10
11
12
13
+----+------+-----+-------+---------+--------+---------------------+--------+
| id | name | age | class | money   | height | add_time            | gender |
+----+------+-----+-------+---------+--------+---------------------+--------+
|  1 | xxh  |  18 |     1 | 1000.00 |    180 | 2019-01-01 00:00:00 |      1 |
|  2 | xxh2 |  19 |     2 | 1500.00 |    178 | 2018-01-01 00:00:00 |      2 |
|  3 | xxh3 |  20 |     3 | 2000.00 |    181 | 2017-01-01 00:00:00 |      1 |
|  4 | xxh4 |  21 |     1 | 3000.00 |    179 | 2020-01-01 00:00:00 |      2 |
|  5 | xxh5 |  22 |     2 | 2500.00 |    176 | 2020-02-03 00:00:00 |      1 |
|  6 | xxh6 |  20 |     1 | 3500.00 |    180 | 2020-03-04 00:00:00 |      2 |
|  7 | 111  |  18 |     2 | 1999.00 |    170 | NULL                |   NULL |
|  8 | xxh7 |  19 |     8 | 1000.00 |    170 | NULL                |   NULL |
|  9 | xxh  |  18 |     1 | 1000.00 |    180 | 2019-01-01 00:00:00 |      1 |
+----+------+-----+-------+---------+--------+---------------------+--------+

统计money低于1000和高于1000的人数

1
2
3
4
5
6
select 
case when money <= 1000 then '低于1000' 
when 1000 < money then '高于1000' else 'X' end, count(*) 
from user group by 
case when money <= 1000 then '低于1000' 
when 1000 < money then '高于1000' else 'X' end;

简化

1
2
3
4
5
select 
case when money <= 1000 then '低于1000' 
when 1000 < money then '高于1000' 
else 'X' end as level, count(*) 
from user group by level;