【已解决】查询语句求教

kkstart_001

有表如下：Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,comment '用户名字'
  `amount` int(11) DEFAULT NULL, comment '每次充值额'
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
)   充值总额小于1000的为vip1级用户，小于5000的为vip2级用户，小于10000的为vip3级用户，求各级用户的数量，想不出来整做啊。。

龙雪刚

1. mysql> select * from user;
   
2. +----+------+--------+---------------------+
   
3. | id | name | amount | time                |
   
4. +----+------+--------+---------------------+
   
5. |  1 | aa   |     50 | 2012-05-10 08:48:37 |
   
6. |  2 | bb   |     50 | 2012-05-10 08:48:41 |
   
7. |  3 | aa   |     50 | 2012-05-10 08:48:46 |
   
8. |  4 | aa   |    500 | 2012-05-10 08:48:50 |
   
9. |  5 | bb   |   1000 | 2012-05-10 08:48:56 |
   
10. |  6 | cc   |   1000 | 2012-05-10 08:49:00 |
    
11. |  7 | cc   |   4000 | 2012-05-10 08:49:04 |
    
12. |  8 | cc   |   4000 | 2012-05-10 08:49:08 |
    
13. |  9 | aa   |    200 | 2012-05-10 08:49:19 |
    
14. | 10 | dd   |    200 | 2012-05-10 08:49:26 |
    
15. | 11 | ee   |   2000 | 2012-05-10 08:49:31 |
    
16. | 12 | ee   |   1000 | 2012-05-10 08:49:34 |
    
17. | 13 | ff   |   1000 | 2012-05-10 08:49:38 |
    
18. | 14 | ff   |   2000 | 2012-05-10 08:49:41 |
    
19. | 15 | ff   |   3000 | 2012-05-10 08:49:44 |
    
20. | 16 | gg   |  10000 | 2012-05-10 08:49:53 |
    
21. +----+------+--------+---------------------+
    
22. 16 rows in set (0.00 sec)
    
23. 
    
24. mysql>  select name,group_concat(amount),sum(amount) as sum,case when sum(amount) <5000 then case when sum(amount) < 1000 then "vip1" else "vip2" end else "vip3" end as level from user group by name;
    
25. +------+----------------------+-------+-------+
    
26. | name | group_concat(amount) | sum   | level |
    
27. +------+----------------------+-------+-------+
    
28. | aa   | 50,50,500,200        |   800 | vip1  |
    
29. | bb   | 50,1000              |  1050 | vip2  |
    
30. | cc   | 1000,4000,4000       |  9000 | vip3  |
    
31. | dd   | 200                  |   200 | vip1  |
    
32. | ee   | 2000,1000            |  3000 | vip2  |
    
33. | ff   | 1000,2000,3000       |  6000 | vip3  |
    
34. | gg   | 10000                | 10000 | vip3  |
    
35. +------+----------------------+-------+-------+
    
36. 7 rows in set (0.00 sec)
    
37. 
    
38. mysql> select level,count(*) from ( select name,case when sum(amount) <5000 then case when sum(amount) < 1000 then "vip1" else "vip2" end else "vip3" end as level from user group by name) tmp group by level;
    
39. +-------+----------+
    
40. | level | count(*) |
    
41. +-------+----------+
    
42. | vip1  |        2 |
    
43. | vip2  |        2 |
    
44. | vip3  |        3 |
    
45. +-------+----------+
    
46. 3 rows in set (0.00 sec)

复制代码

kkstart_001

感谢2楼大哥~