在mysql中按范围分组 [英] group by range in mysql

问题描述

 表：
 new_table 
 user_number | diff 
 2 | 0 
 1 | 28 
 2 | 32 
 1 | 40 
 1 | 53 
 1 | 59 
 1 | 101 
 1 | 105 
 2 | 108 
 2 | 129 
 2 | 130 
 1 | 144 
 
 
 |（结果）
 v 
 
范围|用户数量
 0-20 | 2 
 21-41 | 3 
 42-62 | 1 
 63-83 | 2 
 84-104 | 1 
 105-135 | 0 
 136-156 | 3 
 
 
选择t.range作为[range]，count（*）as [用户数量] 
 from（
 select case b $ b when diff在0到20之间，则'0-20'
当差异在21和41之间时，则'21-41'
当差异在42和62之间时，则'42-62'
当差异在63如果差额在84和104之间，那么'83-83'
，然后'105-105'之间的差额在'105-135'之后'84-104'
 
其他'136-156' 
以range范围结束
 from new_table）t 
 group by t.diff 
 
错误：
 
您的SQL语法错误（*）作为[用户数量] 
来自（
）选择案例
当'在第1行
  

解决方案

Mysql作为关键字的分隔符使用反引号`，而不是方括号（如sql server） p>

Table:   
new_table                                                    
user_number  | diff                  
     2       |  0                      
     1       |  28  
     2       |  32  
     1       |  40  
     1       |  53  
     1       |  59  
     1       |  101  
     1       |  105  
     2       |  108  
     2       |  129  
     2       |  130    
     1       |  144  


            |(result)
            v

range  | number of users  
0-20   |  2  
21-41  |  3  
42-62  |  1  
63-83  |  2  
84-104 |  1  
105-135|  0  
136-156|  3


select t.range as [range], count(*) as [number of users]  
from (  
  select case    
    when diff between 0 and 20 then ' 0-20'  
    when diff between 21 and 41 then ' 21-41'  
    when diff between 42 and 62 then ' 42-62'  
    when diff between 63 and 83 then ' 63-83'  
    when diff between 84 and 104 then ' 84-104'  
    when diff between 105 and 135 then ' 105-135'  
    else '136-156'   
     end as range  
  from new_table) t  
group by t.diff  

Error:

You have an error in your SQL syntax, near '[range], count(*) as [number of users]  
from (  
  select case  
    when' at line 1  

解决方案

Mysql as a delimiter for keywords uses backtick sign " ` ", not square brackets (like sql server)

这篇关于在mysql中按范围分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！