在SQL中,如何在范围内"分组"？

假设我有一个带有数字列的表(让我们称之为"得分").

我想生成一个计数表,显示每个范围内出现的分数.

例如:

score range  | number of occurrences
-------------------------------------
   0-9       |        11
  10-19      |        14
  20-29      |         3
   ...       |       ...

在这个例子中,有11行,得分在0到9的范围内,14行,得分在10到19的范围内,3行得分在20-29的范围内.

有没有简单的方法来设置它？您有什么推荐的吗？

1> Ron Tuffin..：

---

在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

要么

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

---

@ZoHas它有点像黑客,但这有效:按len(t.range)排序,t.范围

2> Walter Mitty..：

---

另一种方法是将范围存储在表中,而不是将它们嵌入查询中.你最终得到一个表,称之为Ranges,看起来像这样:

LowerLimit   UpperLimit   Range 
0              9          '0-9'
10            19          '10-19'
20            29          '20-29'
30            39          '30-39'

一个看起来像这样的查询:

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

这意味着设置一个表,但是当所需的范围改变时,它很容易维护.无需更改代码!

3> Ken Paul..：

---

我在这里看到的答案在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

编辑:见评论

---

你是对的,谢谢你的纠正.显然,当您将变量放在关键字"case"之后时,您只能执行完全匹配,而不能执行表达式.我从回答问题中学到了同样的东西.:-)

4> mhawke..：

---

在postgres中(||字符串连接运算符在哪里):

select (score/10)*10 || '-' || (score/10)*10+9 as scorerange, count(*)
from scores
group by score/10
order by 1

得到:

 scorerange | count 
------------+-------
 0-9        |    11
 10-19      |    14
 20-29      |     3
 30-39      |     2

5> Timothy Walt..：

---

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

这假定我用来测试它的#Scores临时表,我只用0到99之间的随机数填充了100行.

6> 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

7> James Curran..：

---

select cast(score/10 as varchar) + '-' + cast(score/10+9 as varchar), 
       count(*)
from scores
group by score/10