假设我有一个带有数字列的表(让我们称之为“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之间。
有什么简单的方法吗?你有什么建议吗?
当前回答
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的语法中行不通。我会用:
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'
when score between 20 and 29 then '20-29'
...
else '90-99' end as range
from scores) t
group by t.range
编辑:见评论
这将允许您不必指定范围,并且应该与SQL server无关。数学增值!
SELECT CONCAT(range,'-',range+9), COUNT(range)
FROM (
SELECT
score - (score % 10) as range
FROM scores
)
我在这里是因为我有类似的问题,但我发现简短的答案是错误的,一个连续的“情况下”是太多的工作,看到任何重复在我的代码伤害我的眼睛。这就是解
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)
在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
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