MySQL 在mysql中按范围分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6687534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
group by range in mysql
提问by Compuser7
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
采纳答案by zerkms
Mysql as a delimiter for keywords uses backtick sign " ` ", not square brackets (like sql server)
Mysql作为关键字的分隔符使用反引号“`”，而不是方括号（如sql server）
回答by Josh
Here is general code to group by range since doing a case statement gets pretty cumbersome.
这是按范围分组的通用代码，因为执行 case 语句非常麻烦。
The function 'floor' can be used to find the bottom of the range (not 'round' as Bohemian used), and add the amount (19 in the example below) to find the top of the range. Remember to not overlap the bottom and top of the ranges!
函数“floor”可用于查找范围的底部（不是 Bohemian 使用的“圆形”），并添加数量（在下面的示例中为 19）以查找范围的顶部。记住不要重叠范围的底部和顶部！
mysql> create table new_table (user_number int, diff int);
Query OK, 0 rows affected (0.14 sec)
mysql> insert into new_table values (2, 0), (1, 28), (2, 32), (1, 40), (1, 53),
(1, 59), (1, 101), (1, 105), (2, 108), (2, 129), (2, 130), (1, 144);
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select concat(21*floor(diff/21), '-', 21*floor(diff/21) + 20) as `range`,
count(*) as `number of users` from new_table group by 1 order by diff;
+---------+-----------------+
| range | number of users |
+---------+-----------------+
| 0-20 | 1 |
| 21-41 | 3 |
| 42-62 | 2 |
| 84-104 | 1 |
| 105-125 | 2 |
| 126-146 | 3 |
+---------+-----------------+
6 rows in set (0.01 sec)
回答by Bohemian
Here's a solution that will work for any magnitude of diff:
这是一个适用于任何大小差异的解决方案：
select
concat(21 * round(diff / 21), '-', 21 * round(diff / 21) + 20) as `range`,
count(*) as `number of users`
from new_table
group by 1
order by diff;
Here's some testable code and its output:
这是一些可测试的代码及其输出：
create table new_table (user_number int, diff int);
insert into new_table values (2, 0), (1, 28), (2, 32), (1, 40), (1, 53), (1, 59), (1, 101), (1, 105), (2, 108), (2, 129), (2, 130), (1, 144);
-- run query, output is:
+---------+-----------------+
| range | number of users |
+---------+-----------------+
| 0-20 | 1 |
| 21-41 | 1 |
| 42-62 | 2 |
| 63-83 | 2 |
| 105-125 | 3 |
| 126-146 | 2 |
| 147-167 | 1 |
+---------+-----------------+
回答by nobody
If you have regular ranges, a quicker solution would be to group with the help of div function.
如果您有常规范围，更快的解决方案是在 div 函数的帮助下进行分组。
For instance:
例如：
select diff div 20 as range, sum(user_number)
from new_table
group by diff div 20;
Ranges are represented as single digits in that case and you have to know what they mean: 0 = 0-19, 1 = 20-39, 2 = 40-59,...
在这种情况下，范围表示为个位数，您必须知道它们的含义：0 = 0-19, 1 = 20-39, 2 = 40-59,...
If you need different ranges use different divider or maybe subtract some number from diff. For instance "(diff - 1) div 10" gives you ranges 1-10, 11-20, 21-30,...
如果您需要不同的范围，请使用不同的分频器，或者从 diff 中减去一些数字。例如 "(diff - 1) div 10" 给你范围 1-10, 11-20, 21-30, ...
回答by jpereira
range is a mysql keyword. You should "scape" it using ′ :
range 是一个 mysql 关键字。您应该使用 ' 来“转义”它：
select t.`range` as [`range`], ...
回答by lesleyb
You might want to check Are square brackets valid in an SQL query?
您可能想检查方括号在 SQL 查询中是否有效？
I suspect that '[' and ']' are used in Microsoft's SQL but not mysql.
我怀疑 '[' 和 ']' 用于微软的 SQL 而不是 mysql。
回答by a1ex07
One obvious mistake : Mysql uses backticks(
一个明显的错误：Mysql 使用反引号(
`
), not [](as sqlserver) . Change t.range as [range], count(*) as [number of users]to
)，而不是[]（作为 sqlserver）。更改t.range as [range], count(*) as [number of users]为
t.range as `range`, count(*) as `number of users`
回答by Itay Moav -Malimovka
select
case
when diff between 0 and 20 then ' 0-20'
when diff between 0 and 20 then ' 21-41'
when diff between 0 and 20 then ' 42-62'
when diff between 0 and 20 then ' 63-83'
when diff between 0 and 20 then ' 84-104'
when diff between 0 and 20 then ' 105-135'
else '136-156'
end; as 'range',
count(*) as 'number of users'
from new_table
group by range
