在SQL中，如何在范围内“按”分组？

Question

在SQL中，如何在范围内“按”分组？

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

我想要生成一个计数表，它显示在每个范围内出现了多少次分数。

例如：

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

在本例中，有11行的分数在0到9之间，14行的分数在10到19之间，3行的分数在20-29之间。

有什么简单的方法来安排这件事吗？你有什么建议吗？

Answer 1

在SQLSERVER 2000上，投票最高的答案都不正确。也许他们使用的是不同的版本。以下是SQLSERVER 2000上这两种语言的正确版本。select&nbsp;t.range&nbsp;as&nbsp;[score&nbsp;range],&nbsp;count(*)&nbsp;as&nbsp;[number&nbsp;of&nbsp;occurences]from&nbsp;( &nbsp;&nbsp;select&nbsp;case&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;when&nbsp;score&nbsp;between&nbsp;0&nbsp;and&nbsp;9&nbsp;then&nbsp;'&nbsp;0-&nbsp;9' &nbsp;&nbsp;&nbsp;&nbsp;when&nbsp;score&nbsp;between&nbsp;10&nbsp;and&nbsp;19&nbsp;then&nbsp;'10-19' &nbsp;&nbsp;&nbsp;&nbsp;else&nbsp;'20-99'&nbsp;end&nbsp;as&nbsp;range&nbsp;&nbsp;from&nbsp;scores)&nbsp;tgroup&nbsp;by&nbsp;t.range或select&nbsp;t.range&nbsp;as&nbsp;[score&nbsp;range],&nbsp;count(*)&nbsp;as&nbsp;[number&nbsp;of&nbsp;occurences]from&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;user_id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;case&nbsp;when&nbsp;score&nbsp;>=&nbsp;0&nbsp;and&nbsp;score<&nbsp;10&nbsp;then&nbsp;'0-9' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when&nbsp;score&nbsp;>=&nbsp;10&nbsp;and&nbsp;score<&nbsp;20&nbsp;then&nbsp;'10-19' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else&nbsp;'20-99'&nbsp;end&nbsp;as&nbsp;range&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;scores)&nbsp;tgroup&nbsp;by&nbsp;t.range

Answer 2

另一种方法是将范围存储在表中，而不是将它们嵌入查询中。最后你会有一张桌子，叫它恒河，看起来是这样的：LowerLimit&nbsp; &nbsp;UpperLimit&nbsp; &nbsp;Range&nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '0-9'10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '10-19'20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 29&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '20-29'30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 39&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '30-39'一个类似于这样的查询：Select&nbsp; &nbsp;Range as [Score Range],&nbsp; &nbsp;Count(*) as [Number of Occurences]from&nbsp; &nbsp;Ranges r inner join Scores s on s.Score between r.LowerLimit and r.UpperLimitgroup by Range这确实意味着设置一个表，但是当所需的范围发生变化时，它将很容易维护。没有代码更改的必要！