年级人数:34 考试人数:31 最低分:68 及格率:100%
总分:2523 最高分:95 平均分:81.39分段 0-9 10-19 20-29 30-39 40-49 50-59 60-69 70-79 80-89 90-100
人数 0 0 0 0 0 0 1 13 11 6
比例 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 3.2% 41.9% 35.5% 19.4%
如图所示,这样成绩怎么统计
总分:2523 最高分:95 平均分:81.39分段 0-9 10-19 20-29 30-39 40-49 50-59 60-69 70-79 80-89 90-100
人数 0 0 0 0 0 0 1 13 11 6
比例 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 3.2% 41.9% 35.5% 19.4%
如图所示,这样成绩怎么统计
sum(case when 分数<9 then 1 else 0 end)*1.0 as [0-9],
sum(case when 分数 between 10 and 19 then 1 else 0 end) as [10-19],
sum(case when 分数 between 20 and 29 then 1 else 0 end) as [20-29],
--............
from tb
union all
select 人数 as '分段',
sum(case when 分数<9 then 1 else 0 end)*1.0/count(1) as [0-9],
sum(case when 分数 between 10 and 19 then 1 else 0 end)*1.0/count(1) as [10-19],
sum(case when 分数 between 20 and 29 then 1 else 0 end)*1.0/count(1) as [20-29],
--............
from tb
sum(case when 分数<9 then 1 else 0 end)*1.0 as [0-9],
sum(case when 分数 between 10 and 19 then 1 else 0 end) as [10-19],
sum(case when 分数 between 20 and 29 then 1 else 0 end) as [20-29],
--............
from tb
union all
select 人数 as '比例',
sum(case when 分数<9 then 1 else 0 end)*1.0/count(1) as [0-9],
sum(case when 分数 between 10 and 19 then 1 else 0 end)*1.0/count(1) as [10-19],
sum(case when 分数 between 20 and 29 then 1 else 0 end)*1.0/count(1) as [20-29],
--............
from tb
select 人数 as '分段',
sum(case when 分数<9 then 1 else 0 end)*1.0 as [0-9],
sum(case when 分数 between 10 and 19 then 1 else 0 end) as [10-19],
sum(case when 分数 between 20 and 29 then 1 else 0 end) as [20-29],
--............
from tb
union all
select 比例 as '分段',
sum(case when 分数<9 then 1 else 0 end)*1.0/count(1) as [0-9],
sum(case when 分数 between 10 and 19 then 1 else 0 end)*1.0/count(1) as [10-19],
sum(case when 分数 between 20 and 29 then 1 else 0 end)*1.0/count(1) as [20-29],
--............
from tb
总分:2523 最高分:95 平均分:81.39分段 0-9 10-19 20-29 30-39 40-49 50-59 60-69 70-79 80-89 90-100
人数 0 0 0 0 0 0 1 13 11 6
比例 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 3.2% 41.9% 35.5% 19.4%上面的这些数据我想用一个SQL语句一一查询出来,可以实现吗
考试人数 count 弄个字段区别 然后统计它其他的都差不多
declare @cnt numeric(3,1)
select
'0-9'=sum(case when 成绩 between 0 and 9 then 1 else 0 end),
'10-19'=sum(case when 成绩 between 10 and 19 then 1 else 0 end),
'20-29'=sum(case when 成绩 between 20 and 29 then 1 else 0 end),
'30-39'=sum(case when 成绩 between 30 and 39 then 1 else 0 end),
'40-49'=sum(case when 成绩 between 40 and 49 then 1 else 0 end),
'50-59'=sum(case when 成绩 between 50 and 59 then 1 else 0 end),
'60-69'=sum(case when 成绩 between 60 and 69 then 1 else 0 end),
'70-79'=sum(case when 成绩 between 70 and 79 then 1 else 0 end),
'80-89'=sum(case when 成绩 between 80 and 89 then 1 else 0 end),
'90-100'=sum(case when 成绩 between 90 and 100 then 1 else 0 end)
from table_name
union
select
'0-9'=cast((cast((sum(case when 成绩 between 0 and 9 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'10-19'=cast((cast((sum(case when 成绩 between 10 and 19 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'20-29'=cast((cast((sum(case when 成绩 between 20 and 29 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'30-39'=cast((cast((sum(case when 成绩 between 30 and 39 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'40-49'=cast((cast((sum(case when 成绩 between 40 and 49 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'50-59'=cast((cast((sum(case when 成绩 between 50 and 59 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'60-69'=cast((cast((sum(case when 成绩 between 60 and 69 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'70-79'=cast((cast((sum(case when 成绩 between 70 and 79 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'80-89'=cast((cast((sum(case when 成绩 between 80 and 89 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'90-100'=cast((cast((sum(case when 成绩 between 90 and 100 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar)
from table_name
[/code]
declare @cnt numeric(3,1)
select
'0-9'=sum(case when 成绩 between 0 and 9 then 1 else 0 end),
'10-19'=sum(case when 成绩 between 10 and 19 then 1 else 0 end),
'20-29'=sum(case when 成绩 between 20 and 29 then 1 else 0 end),
'30-39'=sum(case when 成绩 between 30 and 39 then 1 else 0 end),
'40-49'=sum(case when 成绩 between 40 and 49 then 1 else 0 end),
'50-59'=sum(case when 成绩 between 50 and 59 then 1 else 0 end),
'60-69'=sum(case when 成绩 between 60 and 69 then 1 else 0 end),
'70-79'=sum(case when 成绩 between 70 and 79 then 1 else 0 end),
'80-89'=sum(case when 成绩 between 80 and 89 then 1 else 0 end),
'90-100'=sum(case when 成绩 between 90 and 100 then 1 else 0 end)
from table_name
union
select
'0-9'=cast((cast((sum(case when 成绩 between 0 and 9 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'10-19'=cast((cast((sum(case when 成绩 between 10 and 19 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'20-29'=cast((cast((sum(case when 成绩 between 20 and 29 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'30-39'=cast((cast((sum(case when 成绩 between 30 and 39 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'40-49'=cast((cast((sum(case when 成绩 between 40 and 49 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'50-59'=cast((cast((sum(case when 成绩 between 50 and 59 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'60-69'=cast((cast((sum(case when 成绩 between 60 and 69 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'70-79'=cast((cast((sum(case when 成绩 between 70 and 79 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'80-89'=cast((cast((sum(case when 成绩 between 80 and 89 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'90-100'=cast((cast((sum(case when 成绩 between 90 and 100 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar)
from table_name
[/code]
select
'0-9'=sum(case when 成绩 between 0 and 9 then 1 else 0 end),
'10-19'=sum(case when 成绩 between 10 and 19 then 1 else 0 end),
'20-29'=sum(case when 成绩 between 20 and 29 then 1 else 0 end),
'30-39'=sum(case when 成绩 between 30 and 39 then 1 else 0 end),
'40-49'=sum(case when 成绩 between 40 and 49 then 1 else 0 end),
'50-59'=sum(case when 成绩 between 50 and 59 then 1 else 0 end),
'60-69'=sum(case when 成绩 between 60 and 69 then 1 else 0 end),
'70-79'=sum(case when 成绩 between 70 and 79 then 1 else 0 end),
'80-89'=sum(case when 成绩 between 80 and 89 then 1 else 0 end),
'90-100'=sum(case when 成绩 between 90 and 100 then 1 else 0 end)
from table_name
union
select
'0-9'=cast((cast((sum(case when 成绩 between 0 and 9 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'10-19'=cast((cast((sum(case when 成绩 between 10 and 19 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'20-29'=cast((cast((sum(case when 成绩 between 20 and 29 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'30-39'=cast((cast((sum(case when 成绩 between 30 and 39 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'40-49'=cast((cast((sum(case when 成绩 between 40 and 49 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'50-59'=cast((cast((sum(case when 成绩 between 50 and 59 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'60-69'=cast((cast((sum(case when 成绩 between 60 and 69 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'70-79'=cast((cast((sum(case when 成绩 between 70 and 79 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'80-89'=cast((cast((sum(case when 成绩 between 80 and 89 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar),
'90-100'=cast((cast((sum(case when 成绩 between 90 and 100 then 1 else 0 end)/31)*100 as @cnt)+'%') as varchar)
from table_name