假设我有一个带有数字列的表(让我们称之为“score”)。
我想生成一个计数表,显示分数在每个范围内出现的次数。
例如:
score range | number of occurrences ------------------------------------- 0-9 | 11 10-19 | 14 20-29 | 3 ... | ...
在这个示例中,有11行分数在0到9之间,14行分数在10到19之间,3行分数在20到29之间。
有什么简单的方法吗?你有什么建议吗?
假设我有一个带有数字列的表(让我们称之为“score”)。
我想生成一个计数表,显示分数在每个范围内出现的次数。
例如:
score range | number of occurrences ------------------------------------- 0-9 | 11 10-19 | 14 20-29 | 3 ... | ...
在这个示例中,有11行分数在0到9之间,14行分数在10到19之间,3行分数在20到29之间。
有什么简单的方法吗?你有什么建议吗?
当前回答
这将允许您不必指定范围,并且应该与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
没有测试过,但你懂的…
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
在我看来,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行。
select t.blah 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 blah
from scores) t
group by t.blah
如果在MySQL中,请确保使用'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