本文介绍了在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中按范围分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 00:08
查看更多