在 SQL 中，如何在范围内“分组”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/232387/
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
In SQL, how can you "group by" in ranges?
提问by Hugh
Suppose I have a table with a numeric column (lets call it "score").
假设我有一个带有数字列的表（我们称之为“分数”）。
I'd like to generate a table of counts, that shows how many times scores appeared in each range.
我想生成一个计数表，显示每个范围内分数出现的次数。
For example:
例如：
score range | number of occurrences ------------------------------------- 0-9 | 11 10-19 | 14 20-29 | 3 ... | ...
In this example there were 11 rows with scores in the range of 0 to 9, 14 rows with scores in the range of 10 to 19, and 3 rows with scores in the range 20-29.
在此示例中，有 11 行的分数在 0 到 9 的范围内，14 行的分数在 10 到 19 的范围内，还有 3 行的分数在 20 到 29 的范围内。
Is there an easy way to set this up? What do you recommend?
有没有简单的方法来设置它？你有什么建议吗？
采纳答案by Ron Tuffin
Neither of the highest voted answers are correct on SQLServer 2000. Perhaps they were using a different version.
在 SQLServer 2000 上，投票最高的答案都不是正确的。也许他们使用的是不同的版本。
Here are the correct versions of both of them on SQLServer 2000.
以下是它们在 SQLServer 2000 上的正确版本。
select t.range as [score range], count(*) as [number of occurences]
from (
select case
when score between 0 and 9 then ' 0- 9'
when score between 10 and 19 then '10-19'
else '20-99' end as range
from scores) t
group by t.range
or
或者
select t.range as [score range], count(*) as [number of occurences]
from (
select user_id,
case when score >= 0 and score< 10 then '0-9'
when score >= 10 and score< 20 then '10-19'
else '20-99' end as range
from scores) t
group by t.range
回答by Walter Mitty
An alternative approach would involve storing the ranges in a table, instead of embedding them in the query. You would end up with a table, call it Ranges, that looks like this:
另一种方法是将范围存储在表中，而不是将它们嵌入到查询中。您最终会得到一个表，称为范围，如下所示：
LowerLimit UpperLimit Range
0 9 '0-9'
10 19 '10-19'
20 29 '20-29'
30 39 '30-39'
And a query that looks like this:
和一个看起来像这样的查询：
Select
Range as [Score Range],
Count(*) as [Number of Occurences]
from
Ranges r inner join Scores s on s.Score between r.LowerLimit and r.UpperLimit
group by Range
This does mean setting up a table, but it would be easy to maintain when the desired ranges change. No code changes necessary!
这确实意味着设置一个表，但是当所需的范围发生变化时，它会很容易维护。无需更改代码！
回答by Ken Paul
I see answers here that won't work in SQL Server's syntax. I would use:
我在这里看到的答案在 SQL Server 的语法中不起作用。我会用：
select t.range as [score range], count(*) as [number of occurences]
from (
select case
when score between 0 and 9 then ' 0-9 '
when score between 10 and 19 then '10-19'
when score between 20 and 29 then '20-29'
...
else '90-99' end as range
from scores) t
group by t.range
EDIT: see comments
编辑：见评论
回答by mhawke
In postgres (where ||is the string concatenation operator):
在 postgres 中（||字符串连接运算符在哪里）：
select (score/10)*10 || '-' || (score/10)*10+9 as scorerange, count(*)
from scores
group by score/10
order by 1
gives:
给出：
scorerange | count
------------+-------
0-9 | 11
10-19 | 14
20-29 | 3
30-39 | 2
回答by Timothy Walters
James Curran's answer was the most concise in my opinion, but the output wasn't correct. For SQL Server the simplest statement is as follows:
James Curran 的回答在我看来是最简洁的，但输出不正确。对于 SQL Server，最简单的语句如下：
SELECT
[score range] = CAST((Score/10)*10 AS VARCHAR) + ' - ' + CAST((Score/10)*10+9 AS VARCHAR),
[number of occurrences] = COUNT(*)
FROM #Scores
GROUP BY Score/10
ORDER BY Score/10
This assumes a #Scores temporary table I used to test it, I just populated 100 rows with random number between 0 and 99.
这假设有一个我用来测试它的#Scores 临时表，我只是用 0 到 99 之间的随机数填充了 100 行。
回答by trevorgrayson
This will allow you to not have to specify ranges, and should be SQL server agnostic. Math FTW!
这将允许您不必指定范围，并且应该与 SQL 服务器无关。数学FTW！
SELECT CONCAT(range,'-',range+9), COUNT(range)
FROM (
SELECT
score - (score % 10) as range
FROM scores
)
回答by tvanfosson
create table scores (
user_id int,
score int
)
select t.range as [score range], count(*) as [number of occurences]
from (
select user_id,
case when score >= 0 and score < 10 then '0-9'
case when score >= 10 and score < 20 then '10-19'
...
else '90-99' as range
from scores) t
group by t.range
回答by James Curran
select cast(score/10 as varchar) + '-' + cast(score/10+9 as varchar),
count(*)
from scores
group by score/10
回答by JoshNaro
I would do this a little differently so that it scales without having to define every case:
我会做一些不同的事情，这样它就可以扩展而不必定义每个案例：
select t.range as [score range], count(*) as [number of occurences]
from (
select FLOOR(score/10) as range
from scores) t
group by t.range
Not tested, but you get the idea...
没有经过测试，但你明白了......
回答by Aheho
declare @RangeWidth int
set @RangeWidth = 10
select
Floor(Score/@RangeWidth) as LowerBound,
Floor(Score/@RangeWidth)+@RangeWidth as UpperBound,
Count(*)
From
ScoreTable
group by
Floor(Score/@RangeWidth)
