成绩分组统计问题
我有全市的成绩库，我想统计每个学校每个科目的优秀率、及格率和低分率，如科目为语文、数学、英语、物理、化学等，优秀率为90-100分，及格率为60-89分，低分率为0-29
[解决办法]
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [xm] varchar(100), [xb] varchar(100), [xxmc] varchar(100), [yw] int, [sx] int, [wy] int, [zf] int);
insert #temp
select '张三','男','一中','60','80','59','68' union all
select '李四','女','一中','56','99','56','68' union all
select '王五','男','十六中','88','91','98','68'
--SQL:
select
[xxmc],
[语文优秀率]=LTRIM(CAST(100.0*COUNT(CASE WHEN yw BETWEEN 90 AND 100 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[语文及格率]=LTRIM(CAST(100.0*COUNT(CASE WHEN yw BETWEEN 60 AND 89 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[语文低分率]=LTRIM(CAST(100.0*COUNT(CASE WHEN yw BETWEEN 0 AND 59 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[数学优秀率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [sx] BETWEEN 90 AND 100 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[数学及格率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [sx] BETWEEN 60 AND 89 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[数学低分率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [sx] BETWEEN 0 AND 59 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[英语优秀率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [wy] BETWEEN 90 AND 100 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[英语及格率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [wy] BETWEEN 60 AND 89 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[英语低分率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [wy] BETWEEN 0 AND 59 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[物理优秀率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [zf] BETWEEN 90 AND 100 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[物理及格率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [zf] BETWEEN 60 AND 89 THEN 1 END)/COUNT(1) AS FLOAT)) + '%',
[物理低分率]=LTRIM(CAST(100.0*COUNT(CASE WHEN [zf] BETWEEN 0 AND 59 THEN 1 END)/COUNT(1) AS FLOAT)) + '%'
from #temp
GROUP BY [xxmc]
/*
xxmc语文优秀率语文及格率语文低分率数学优秀率数学及格率数学低分率英语优秀率英语及格率英语低分率物理优秀率物理及格率物理低分率
十六中0%100%0%100%0%0%100%0%0%0%100%0%
一中0%50%50%50%50%0%0%0%100%0%100%0%
*/