SQL语句精简

求高人。
精简也好。换个思路也好。只要能解决问题。
先谢谢了。


表结构如下：
成绩表：
RID  成绩编号
SID  学生编号
TID  考试编号
Class 班级编号
Subject  科目编号
Score    分数

班级表：
CID  班级编号
Name 班级名称
Grade 年级名称

年级就是高一和高二


SELECT [Class],[Subject],应考人数,实考人数,A类,B类,C类,D类,最高分,最低分,班平均,级平均,B值,DENSE_RANK() OVER(PARTITION BY B值 ORDER BY 年级,[Subject] ASC) as B序
from (
SELECT [Class],[Subject],(SELECT [Grade] from [Classes] WHERE [Name] = '高二一班') AS 年级,
(SELECT COUNT(ID) from [Student] AS A WHERE A.[Class] = [Class]) AS 应考人数,
(SELECT COUNT(ID) from [Student] AS A WHERE A.[Class] = [Class] AND [Score] > 0 ) AS 实考人数,
(SELECT COUNT(DISTINCT  [SID]) from [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] >= (SELECT AVG([Score]) from (SELECT TOP 30 PERCENT [Score] from [Results] ORDER BY [Score] DESC) T)) AS A类,

(SELECT COUNT(DISTINCT  [SID]) from [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] < (SELECT AVG([Score]) from (SELECT TOP 30 PERCENT [Score] from [Results] ORDER BY [Score] DESC) T)
AND [Score] >= (SELECT AVG([Score]) from (SELECT TOP