在 SQL 中，如何“分组依据"?在范围内? [英] In SQL, how can you "group by" in ranges?

问题描述

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

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

例如:

<前>分数范围|出现次数-------------------------------------0-9 |1110-19 |1420-29 |3... |...

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

有没有简单的方法来设置它?你有什么推荐?

解决方案

在 SQL Server 2000 上投票最多的答案都不是正确的.也许他们使用的是不同的版本.

以下是它们在 SQL Server 2000 上的正确版本.

选择t.range作为[得分范围]，count(*)作为[出现次数]从 (选择案例当得分介于 0 和 9 之间时，则为0-9"当分数在 10 到 19 之间时，则为10-19"否则 '20-99' 以范围结束从分数) t按 t.range 分组

或

选择t.range为[得分范围]，count(*)为[出现次数]从 (选择用户 ID，得分＞＝0且得分＜0的情况10 然后'0-9'当分数＞＝10并且分数＜20 然后是10-19"否则 '20-99' 以范围结束从分数) t按 t.range 分组

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.

Is there an easy way to set this up? What do you recommend?

解决方案

Neither of the highest voted answers are correct on SQL Server 2000. Perhaps they were using a different version.

Here are the correct versions of both of them on 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

这篇关于在 SQL 中，如何“分组依据"?在范围内?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！