求个sql 写法?进来帮下忙吧,谢谢!现在有个表a 如下:
id userID value1
1 1 60
2 1 90
3 2 50
4 4 80
5 3 70
6 5 80
7 8 90
8 7 60
9 9 50
10 10 60
11 2 80
12 6 90现在我求出有各多少个用户的value1的和在50,60,70,80,90,100,110之间,要求这50,60,70,80,90,100,110几个区段显示满足条件的用户个数,如果没有用户数就显示0,大家帮看下应该如何写啊,谢谢!结果类似如下:区段 用户数
50 ?
60 ?
70 ?
80 ?
90 ?
..... ....
id userID value1
1 1 60
2 1 90
3 2 50
4 4 80
5 3 70
6 5 80
7 8 90
8 7 60
9 9 50
10 10 60
11 2 80
12 6 90现在我求出有各多少个用户的value1的和在50,60,70,80,90,100,110之间,要求这50,60,70,80,90,100,110几个区段显示满足条件的用户个数,如果没有用户数就显示0,大家帮看下应该如何写啊,谢谢!结果类似如下:区段 用户数
50 ?
60 ?
70 ?
80 ?
90 ?
..... ....
+------+--------+--------+
| id | userid | value1 |
+------+--------+--------+
| 1 | 1 | 60 |
| 2 | 1 | 90 |
| 3 | 2 | 50 |
| 4 | 4 | 80 |
| 5 | 3 | 70 |
| 6 | 5 | 80 |
| 7 | 8 | 90 |
| 8 | 7 | 60 |
| 9 | 9 | 50 |
| 10 | 10 | 60 |
| 11 | 2 | 80 |
| 12 | 6 | 90 |
+------+--------+--------+
12 rows in set (0.00 sec)mysql> select ELT(interval(value1,50,60,70,80,90,100,110), 50,60,70,80,90,100,110) as `区段`,count(*) as `用户数`
-> from t_woaitiyu
-> group by ELT(interval(value1,50,60,70,80,90,100,110), 50,60,70,80,90,100,
110);
+------+--------+
| 区段 | 用户数 |
+------+--------+
| 50 | 2 |
| 60 | 3 |
| 70 | 1 |
| 80 | 3 |
| 90 | 3 |
+------+--------+
5 rows in set (0.00 sec)mysql>
0) as `区段`,count(id) as `用户数`
-> from (
-> select value1,id from t_woaitiyu
-> union all
-> select 50,null
-> union all
-> select 60,null
-> union all
-> select 70,null
-> union all
-> select 80,null
-> union all
-> select 90,null
-> union all
-> select 100,null
-> union all
-> select 110,null
-> ) t
-> group by ELT(interval(value1,50,60,70,80,90,100,110), 50,60,70,80,90,100,
110)
-> order by ELT(interval(value1,50,60,70,80,90,100,110), 50,60,70,80,90,100,
110)+0;
+------+--------+
| 区段 | 用户数 |
+------+--------+
| 50 | 2 |
| 60 | 3 |
| 70 | 1 |
| 80 | 3 |
| 90 | 3 |
| 100 | 0 |
| 110 | 0 |
+------+--------+
7 rows in set (0.00 sec)mysql>
-> from (
-> select floor(value1/10)*10 as k,id from t_woaitiyu
-> union all
-> select 50,null
-> union all
-> select 60,null
-> union all
-> select 70,null
-> union all
-> select 80,null
-> union all
-> select 90,null
-> union all
-> select 100,null
-> union all
-> select 110,null
-> ) t
-> group by k;
+------+--------+
| 区段 | 用户数 |
+------+--------+
| 50 | 2 |
| 60 | 3 |
| 70 | 1 |
| 80 | 3 |
| 90 | 3 |
| 100 | 0 |
| 110 | 0 |
+------+--------+
7 rows in set (0.02 sec)mysql>