select
分数%10 as 分数段,
count(*) 人数,
count(*)/cast((select count*(*) from 表名) as numeric(10,2)) as 比例
from
表名
group by
分数%10
分数%10 as 分数段,
count(*) 人数,
count(*)/cast((select count*(*) from 表名) as numeric(10,2)) as 比例
from
表名
group by
分数%10
case (case 分数 when 100 then 99 else 分数 end)/10
when 0 then '0~10'
when 1 then '10~20'
when 2 then '20~30'
when 3 then '30~40'
when 4 then '40~50'
when 5 then '50~60'
when 6 then '60~70'
when 7 then '70~80'
when 8 then '80~90'
when 9 then '90~'
end as 分数段,
count(*) 人数,
count(*)/cast((select count(*) from 表名) as numeric(10,2)) as 比例
from
表名
group by
(case 分数 when 100 then 99 else 分数 end)/10
SELECT COUNT(*) * 100.0 /
(SELECT COUNT(*)
FROM 成绩表) AS 比例
FROM 成绩表
WHERE (N BETWEEN 90 AND 100)
(case 分数 when 100 then 99 else 分数 end)/10 应改成 cast((case 分数 when 100 then 99 else 分数 end)/10 as int)
SELECT @total=COUNT(*) FROM test
SELECT @a90=COUNT(*) FROM test WHERE (N BETWEEN 90 AND 100)
SELECT @a80=COUNT(*) FROM test WHERE (N BETWEEN 80 AND 89)
SELECT @a70=COUNT(*) FROM test WHERE (N BETWEEN 70 AND 79)
SELECT @a60=COUNT(*) FROM test WHERE (N BETWEEN 60 AND 69)
SELECT @a50=COUNT(*) FROM test WHERE (N BETWEEN 50 AND 59)
SELECT @a40=COUNT(*) FROM test WHERE (N BETWEEN 40 AND 49)
SELECT @a30=COUNT(*) FROM test WHERE (N BETWEEN 30 AND 39)
SELECT @a20=COUNT(*) FROM test WHERE (N BETWEEN 20 AND 29)
SELECT @a10=COUNT(*) FROM test WHERE (N BETWEEN 10 AND 19)
SELECT @a0=COUNT(*) FROM test WHERE (N BETWEEN 0 AND 9)SELECT 课程名称,班级名称,考试时间,教师,
@a90 AS '成绩大于90的人数',
(@a90 * 100.0 / @total) AS '所占比例',
@a80 AS '成绩介于80-90的人数',
(@a80 * 100.0 / @total) AS '所占比例',
@a70 AS '成绩介于70-80的人数',
(@a70 * 100.0 / @total) AS '所占比例',
@a60 AS '成绩介于60-70的人数',
(@a60 * 100.0 / @total) AS '所占比例',
@a50 AS '成绩介于50-60的人数',
(@a50 * 100.0 / @total) AS '所占比例',
@a40 AS '成绩介于40-50的人数',
(@a40 * 100.0 / @total) AS '所占比例',
@a30 AS '成绩介于30-40的人数',
(@a30 * 100.0 / @total) AS '所占比例',
@a20 AS '成绩介于20-30的人数',
(@a20 * 100.0 / @total) AS '所占比例',
@a10 AS '成绩介于10-20的人数',
(@a10 * 100.0 / @total) AS '所占比例',
@a0 AS '成绩介于0-10的人数',
(@a0 * 100.0 / @total) AS '所占比例'格式的细节你就自己搞定吧