假设我有一个带有数字列的表(让我们称之为“score”)。

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

例如:

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

在这个示例中,有11行分数在0到9之间,14行分数在10到19之间,3行分数在20到29之间。

有什么简单的方法吗?你有什么建议吗?


当前回答

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

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)


其他回答

也许你问的是如何让这样的事情继续下去……

当然,您将为查询调用全表扫描,如果包含需要统计(聚合)的分数的表很大,您可能想要一个性能更好的解决方案,您可以创建一个辅助表并使用规则,例如关于插入—您可能会研究它。

不过,并不是所有的RDBMS引擎都有规则!

在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

这将允许您不必指定范围,并且应该与SQL server无关。数学增值!

SELECT CONCAT(range,'-',range+9), COUNT(range)
FROM (
  SELECT 
    score - (score % 10) as range
  FROM scores
)

我会用一种稍微不同的方式来做,这样它就不必定义每种情况:

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

没有测试过,但你懂的…

在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