select 科目,优秀率=SUM(case when 成绩 between 90 and 100 then 1 else 0 end)/COUNT(1), 及格率=SUM(case when 成绩 between 60 and 89 then 1 else 0 end)/COUNT(1), 低分率=SUM(case when 成绩 between 0 and 29 then 1 else 0 end)/COUNT(1) from tb group by 科目
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% */
及格率=SUM(case when 成绩 between 60 and 89 then 1 else 0 end)/COUNT(1),
低分率=SUM(case when 成绩 between 0 and 29 then 1 else 0 end)/COUNT(1)
from tb
group by 科目
xm xb yw sx wy zf
张三 男 60 80 59 68
李四 女 56 99 56 68
王五 男 88 91 98 68
........
xm xb xxmc yw sx wy zf
张三 男 一中 60 80 59 68
李四 女 一中 56 99 56 68
王五 男 十六中 88 91 98 68
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%
*/