另一种方法是将范围存储在表中，而不是将它们嵌入到查询中。你最终会得到一个表，命名为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

这确实意味着要建立一个表，但是当所需的范围发生变化时，维护这个表是很容易的。不需要更改代码!

在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

下面是如何在T-SQL中做到这一点:

DECLARE @traunch INT = 1000;

SELECT 
    CONCAT
    ( 
      FORMAT((score / @traunch) * @traunch, '###,000,000') 
      , ' - ' , 
      FORMAT((score / @traunch) * @traunch + @traunch - 1, '###,000,000') 
    ) as [Range]
  , FORMAT(MIN(score), 'N0') as [Min]
  , FORMAT(AVG(score), 'N0') as [Avg]
  , FORMAT(MAX(score), 'N0') as [Max]
  , FORMAT(COUNT(score), 'N0') as [Count]
  , FORMAT(SUM(score), 'N0') as [Sum]
FROM scores
GROUP BY score / @traunch
ORDER BY score / @traunch

因为要排序的列(Range)是一个字符串，所以使用字符串/单词排序而不是数值排序。

只要字符串有0来填充数字长度，排序在语义上仍然是正确的:

SELECT t.range AS ScoreRange,
       COUNT(*) AS NumberOfOccurrences
  FROM (SELECT CASE
                    WHEN score BETWEEN 0 AND 9 THEN '00-09'
                    WHEN score BETWEEN 10 AND 19 THEN '10-19'
                    ELSE '20-99'
               END AS Range
          FROM Scores) t
 GROUP BY t.Range

如果范围是混合的，简单地填充一个额外的0:

SELECT t.range AS ScoreRange,
       COUNT(*) AS NumberOfOccurrences
  FROM (SELECT CASE
                    WHEN score BETWEEN 0 AND 9 THEN '000-009'
                    WHEN score BETWEEN 10 AND 19 THEN '010-019'
                    WHEN score BETWEEN 20 AND 99 THEN '020-099'
                    ELSE '100-999'
               END AS Range
          FROM Scores) t
 GROUP BY t.Range

我在这里是因为我有类似的问题，但我发现简短的答案是错误的，一个连续的“情况下”是太多的工作，看到任何重复在我的代码伤害我的眼睛。这就是解

SELECT --MIN(score), MAX(score),
    [score range] = CAST(ROUND(score-5,-1)AS VARCHAR) + ' - ' + CAST((ROUND(score-5,-1)+10)AS VARCHAR),
    [number of occurrences] = COUNT(*)
FROM order
GROUP BY  CAST(ROUND(score-5,-1)AS VARCHAR) + ' - ' + CAST((ROUND(score-5,-1)+10)AS VARCHAR)
ORDER BY MIN(score)

在SQL Server 2000上，投票最多的答案都不正确。也许他们用的是另一个版本。

下面是在SQL Server 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 occurrences]
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

select t.range as score, count(*) as Count 
from (
      select UserId,
         case when isnull(score ,0) >= 0 and isnull(score ,0)< 5 then '0-5'
                when isnull(score ,0) >= 5 and isnull(score ,0)< 10 then '5-10'
                when isnull(score ,0) >= 10 and isnull(score ,0)< 15 then '10-15'
                when isnull(score ,0) >= 15 and isnull(score ,0)< 20 then '15-20'               
         else ' 20+' end as range
         ,case when isnull(score ,0) >= 0 and isnull(score ,0)< 5 then 1
                when isnull(score ,0) >= 5 and isnull(score ,0)< 10 then 2
                when isnull(score ,0) >= 10 and isnull(score ,0)< 15 then 3
                when isnull(score ,0) >= 15 and isnull(score ,0)< 20 then 4             
         else 5  end as pd
     from score table
     ) t

group by t.range,pd order by pd