SELECT 课程名,[不及格率] = SUM(CASE WHEN 成绩 BETWEEN '0' AND '59' THEN 1 ELSE 0 END)/count(*) +'%', [及格率] = SUM(CASE WHEN 成绩 BETWEEN '60' AND '79' THEN 1 ELSE 0 END)/count(*)+'%', [良好率] = SUM(CASE WHEN 成绩 BETWEEN '80' AND '89' THEN 1 ELSE 0 END)/count(*)+'%',[优秀率] = SUM(CASE WHEN 成绩 BETWEEN '90' AND '99' THEN 1 ELSE 0 END)/count(*)+'%' FROM 表A
group by 课程名
即可.
如要加课程名, 需用group by
需要转换一下SELECT 课程名,[不及格率] =
cast(SUM(CASE WHEN 成绩 BETWEEN '0' AND '59' THEN 1 ELSE 0 END)/count(*) as varchar(4) +'%',
[及格率] = cast(SUM(CASE WHEN 成绩 BETWEEN '60' AND '79' THEN 1 ELSE 0 END)/count(*) as varchar(4)+'%',
[良好率] = cast(SUM(CASE WHEN 成绩 BETWEEN '80' AND '89' THEN 1 ELSE 0 END)/count(*) as varchar(4))+'%',
[优秀率] = cast(SUM(CASE WHEN 成绩 BETWEEN '90' AND '99' THEN 1 ELSE 0 END)/count(*) as varchar(4))+'%'
FROM 表A