请帮助简化tsql，太长，以至于超过了sql server 2005的长度限制

Question

我写的一个sql server 2005 存储过程如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[zlfx]
AS
BEGIN
declare @km nvarchar(max)
declare @bj nvarchar(10)
declare @ss nvarchar(max)
set @ss=''
set @km='语文数学英语'
declare @j int
declare my_cursor cursor scroll dynamic
for
select distinct [班级] from  mcb
open my_cursor
fetch next from my_cursor into @bj
while(@@fetch_status=0)
begin
set @j=1
while @j<=len(@km)
begin
--substring(@km,@j,2)
declare @i int
declare @s varchar(max)
set @i=80
set @s='select '+ltrim(str(@bj))+' 班级,'''+substring(@km,@j,2)+''' 科目'
set @s=@s
+',(select sum(成绩) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+') '+'总分'
+',(select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+') '+'人数'
+',(select avg(成绩) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+') '+'平均'
+',case when (select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+')=0 then 0 else (select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>=80 and 班级='+ltrim(str(@bj))+')/(select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+') end '+'优秀率'
+',case when (select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+')=0 then 0 else (select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>=60 and 班级='+ltrim(str(@bj))+')/(select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+') end '+'及格率'
+',case when (select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+')=0 then 0 else (select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 成绩<30 and 班级='+ltrim(str(@bj))+')/(select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 班级='+ltrim(str(@bj))+') end '+'低分率'
+',(select max(成绩) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 班级='+ltrim(str(@bj))+') '+'最高'
+',(select min(成绩) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 班级='+ltrim(str(@bj))+' and 成绩>0) '+'最低'
+',(select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>=90 and 班级='+ltrim(str(@bj))+') '+'段90'
while @i>=30
begin
set @s=@s+',(select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>='+ltrim(str(@i))+' and 成绩<'+ltrim(str(@i+10))+' and 班级='+ltrim(str(@bj))+') '+'段'+ltrim(str(@i))
set @i=@i-10
end
set @s=@s+',(select count(*) from v_cjb where 科目='''+substring(@km,@j,2)+''' and 成绩>0 and 成绩<30 and 班级='+ltrim(str(@bj))+') '+'段0'
exec(@s)
set @ss=@ss+@s
set @ss=@ss+' uinion '
set @j=@j+2
end
fetch next from my_cursor into @bj
end
close my_cursor
deallocate my_cursor
select @ss
END可以看出里面不停地重复引用了相似的where条件，不知道有没有办法只引用一次，能引用次数少点也成。
我上面代码中“企图”用union生成一个总的查询语句放在@ss中，以便调用一次exec(@ss)就完成整个所有班级所有学科的质量分析查询。但是我的select @ss产生的显示出现了截断！（是不是超过了sql server 2005查询串最大长度？）
怎么办？我上面代码只用了'语文数学英语'三科示例，实际应用中需要'语文数学英语物理化学生物政治历史地理能力'甚至更多的学科。能不能想办法缩减最络形成的@ss的长度？我也考虑过用insert的方式查询出一个班的就插入一个建立好的表中，但是不喜欢这种式，实在没法子再试试这种方法。下面是@ss开始的部分代码
select 1 班级,'语文' 科目,(select sum(成绩) from v_cjb where 科目='语文' and 成绩>0 and 班级=1) 总分,(select count(*) from v_cjb where 科目='语文' and 成绩>0 and 班级=1) 人数,(select avg(成绩) from v_cjb where 科目='语文' and 成绩>0 and 班级=1) 平均,case when (select count(*) from v_cjb where 科目='语文' and 成绩>0 and 班级=1)=0 then 0 else (select count(*) from v_cjb where 科目='语文' and 成绩>=80 and 班级=1)/(select count(*) from v_cjb where 科目='语文' and 成绩>0 and 班级=1) end 优秀率,case when (select count(*) from v_cjb where 科目='语文' and 成绩>0 and 班级=1)=0 then 0 else (select count(*) from v_cjb where 科目='语文' and 成绩>=60 and 班级=1)/(select count(*) from v_cjb where 科目='语文' and 成绩>0 and 班级=1) end 及格率,case when (select count(*) from v_cjb where 科目='语文' and 成绩>0 and 班级=1)=0 then 0 else (select count(*) from v_cjb where 科目='语文' and 成绩>0 and 成绩<30 and 班级=1)/(select count(*) from v_cjb where 科目='语文' and 成绩>0 and 班级=1) end 低分率,(select max(成绩) from v_cjb where 科目='语文' and 班级=1) 最高,(select min(成绩) from v_cjb where 科目='语文' and 班级=1 and 成绩>0) 最低,(select count(*) from v_cjb where 科目='语文' and 成绩>=90 and 班级=1) 段90,(select count(*) from v_cjb where 科目='语文' and 成绩>=80 and 成绩<90 and 班级=1) 段80,(select count(*) from v_cjb where 科目='语文' and 成绩>=70 and 成绩<80 and 班级=1) 段70,(select count(*) from v_cjb where 科目='语文' and 成绩>=60 and 成绩<70 and 班级=1) 段60,(select count(*) from v_cjb where 科目='语文' and 成绩>=50 and 成绩<60 and 班级=1) 段50,(select count(*) from v_cjb where 科目='语文' and 成绩>=40 and 成绩<50 and 班级=1) 段40,(select count(*) from v_cjb where 科目='语文' and 成绩>=30 and 成绩<40 and 班级=1) 段30,(select count(*) from v_cjb where 科目='语文' and 成绩>0 and 成绩<30 and 班级=1) 段0
 union
select 1 班级,'数学' 科目,(select sum(成绩) from v_cjb where 科目='数学' and 成绩>0 and 班级=1) .........
 union
.....

Answer 1

-- 从脚本上看, 应该是这样查询就可以了
SELECT
班级 = CASE
WHEN GROUPING(班级) = 1 THEN N'<总计>'
WHEN GROUPING(科目) = 1 THEN N'<' + 科目 + N'_小计>'
ELSE 班级
END,
科目 = CASE
WHEN GROUPING(科目) = 0 THEN 科目
ELSE N''
END,
总分 = SUM(CASE WHEN 成绩 > 0 THEN 成绩 END),
人数 = SUM(CASE WHEN 成绩 > 0 THEN 1 END),
平均 = AVG(CASE WHEN 成绩 > 0 THEN 成绩 END),
优秀率 = SUM(CASE WHEN 成绩 > 80 THEN 1 END) / SUM(CASE WHEN 成绩 > 0 THEN 1 END),
及格率 = SUM(CASE WHEN 成绩 > 60 THEN 1 END) / SUM(CASE WHEN 成绩 > 0 THEN 1 END),
低分率 = SUM(CASE WHEN 成绩 > 0 AND 成绩 < 30 THEN 1 END) / SUM(CASE WHEN 成绩 > 0 THEN 1 END),
最高 = MAX(成绩),
最低 = MIN(CASE WHEN 成绩 > 0 THEN 成绩 END),
段90 = SUM(CASE WHEN 成绩 >= 90 THEN 1 END),
段80 = SUM(CASE WHEN 成绩 >= 80 AND 成绩 < 90 THEN 1 END),
段70 = SUM(CASE WHEN 成绩 >= 70 AND 成绩 < 80 THEN 1 END),
段60 = SUM(CASE WHEN 成绩 >= 60 AND 成绩 < 70 THEN 1 END),
段50 = SUM(CASE WHEN 成绩 >= 50 AND 成绩 < 60 THEN 1 END),
段40 = SUM(CASE WHEN 成绩 >= 40 AND 成绩 < 50 THEN 1 END),
段30 = SUM(CASE WHEN 成绩 >= 30 AND 成绩 < 40 THEN 1 END),
段0 = SUM(CASE WHEN 成绩 > 0 AND 成绩 <= 30 THEN 1 END)
FROM v_cjb
GROUP BY 班级, 科目 WITH ROLLUP

Answer 2

总体要求：
已有视图：v_cjb(学号，班级，科目，成绩)
视图内容举例：
学号，班级，科目，成绩
1    1     语文  76
1    1     数学  87
1    1     英语  99
2    1     语文 100
2    1     数学  56
2    1     英语  35
3    2     语文 111
3    2     数学  90
3    2     英语 114查询出各班各科的:
班级,科目,总分,人数,平均,优秀率,及格率,低分率,最高,最低,段90,段80,段70,段60,段50,段40,段30,段0请注意：
1.被零除问题
2.查询出的行数应该是班级数×科目数

Answer 3

-- 示例数据
DECLARE @v TABLE(
学号 int, 班级 int, 科目 nvarchar(10), 成绩 int);
INSERT @v
SELECT 1, 1, N'语文', 76  UNION ALL
SELECT 1, 1, N'数学',  87   UNION ALL
SELECT 1, 1, N'英语',  99   UNION ALL
SELECT 2, 1, N'语文', 100   UNION ALL
SELECT 2, 1, N'数学',  56   UNION ALL
SELECT 2, 1, N'英语',  35   UNION ALL
SELECT 3, 2, N'语文', 111   UNION ALL
SELECT 3, 2, N'数学',  90   UNION ALL
SELECT 3, 2, N'英语', 114 SELECT
班级 = CASE
WHEN GROUPING(班级) = 1 THEN N'<总计>'
ELSE RTRIM(班级)
END,
科目 = CASE
WHEN GROUPING(科目) = 0 THEN 科目
ELSE N'<小计>'
END,
总分 = SUM(CASE WHEN 成绩 > 0 THEN 成绩 END),
人数 = SUM(CASE WHEN 成绩 > 0 THEN 1 END),
平均 = AVG(CASE WHEN 成绩 > 0 THEN 成绩 END),
优秀率 = CASE
WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩 > 80 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩 > 0 THEN 1 END))
END,
及格率 = CASE
WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩 > 60 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩 > 0 THEN 1 END))
END,
低分率 = CASE
WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩 > 0 AND 成绩 < 30 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩 > 0 THEN 1 END))
END,
最高 = MAX(成绩),
最低 = MIN(CASE WHEN 成绩 > 0 THEN 成绩 END),
段90 = SUM(CASE WHEN 成绩 >= 90 THEN 1 END),
段80 = SUM(CASE WHEN 成绩 >= 80 AND 成绩 < 90 THEN 1 END),
段70 = SUM(CASE WHEN 成绩 >= 70 AND 成绩 < 80 THEN 1 END),
段60 = SUM(CASE WHEN 成绩 >= 60 AND 成绩 < 70 THEN 1 END),
段50 = SUM(CASE WHEN 成绩 >= 50 AND 成绩 < 60 THEN 1 END),
段40 = SUM(CASE WHEN 成绩 >= 40 AND 成绩 < 50 THEN 1 END),
段30 = SUM(CASE WHEN 成绩 >= 30 AND 成绩 < 40 THEN 1 END),
段00 = SUM(CASE WHEN 成绩 > 0 AND 成绩 <= 30 THEN 1 END)
FROM @v v_cjb
GROUP BY 班级, 科目 WITH ROLLUP

Answer 4

谢谢，很有启发，不过是不是有被零除溢除的可能性？SUM(CASE WHEN 成绩 > 0 THEN 1 END）在实际中有可能是0

Answer 5

你看看不就知道了，那些除数为0的项一律为null.

Answer 6

null  值用 ISNULL 处理一下就行了嘛, 你前面没说要处理这个呢
SELECT
班级 = CASE
WHEN GROUPING(班级) = 1 THEN N'<总计>'
ELSE RTRIM(班级)
END,
科目 = CASE
WHEN GROUPING(科目) = 0 THEN 科目
ELSE N'<小计>'
END,
总分 = ISNULL(SUM(CASE WHEN 成绩 > 0 THEN 成绩 END), 0),
人数 = ISNULL(SUM(CASE WHEN 成绩 > 0 THEN 1 END), 0),
平均 = ISNULL(AVG(CASE WHEN 成绩 > 0 THEN 成绩 END), 0),
优秀率 = ISNULL(CASE
WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩 > 80 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩 > 0 THEN 1 END))
END, 0),
及格率 = ISNULL(CASE
WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩 > 60 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩 > 0 THEN 1 END))
END, 0),
低分率 = ISNULL(CASE
WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩 > 0 AND 成绩 < 30 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩 > 0 THEN 1 END))
END, 0),
最高 = ISNULL(MAX(成绩), 0),
最低 = ISNULL(MIN(CASE WHEN 成绩 > 0 THEN 成绩 END), 0),
段90 = ISNULL(SUM(CASE WHEN 成绩 >= 90 THEN 1 END), 0),
段80 = ISNULL(SUM(CASE WHEN 成绩 >= 80 AND 成绩 < 90 THEN 1 END), 0),
段70 = ISNULL(SUM(CASE WHEN 成绩 >= 70 AND 成绩 < 80 THEN 1 END), 0),
段60 = ISNULL(SUM(CASE WHEN 成绩 >= 60 AND 成绩 < 70 THEN 1 END), 0),
段50 = ISNULL(SUM(CASE WHEN 成绩 >= 50 AND 成绩 < 60 THEN 1 END), 0),
段40 = ISNULL(SUM(CASE WHEN 成绩 >= 40 AND 成绩 < 50 THEN 1 END), 0),
段30 = ISNULL(SUM(CASE WHEN 成绩 >= 30 AND 成绩 < 40 THEN 1 END), 0),
段00 = ISNULL(SUM(CASE WHEN 成绩 > 0 AND 成绩 <= 30 THEN 1 END), 0)
FROM @v v_cjb
GROUP BY 班级, 科目 WITH ROLLUP

Answer 7

除数为 0 会直接报错
NULL 值做为被除数才会产生 NULL 值不过不管怎么样, 如果你不喜欢 NULL 值, 都可以对结果用 ISNULL 处理, 用CASE WHEN 的话, 脚本会更长了

Answer 8

SELECT
    班级,
    科目 = CASE
            WHEN GROUPING(科目) = 0 THEN 科目
            ELSE N'<小计>'
        END,
    总分 = ISNULL(SUM(CASE WHEN 成绩 > 0 THEN 成绩 END), 0),
    人数 = ISNULL(SUM(CASE WHEN 成绩 > 0 THEN 1 END), 0),
    平均 = ISNULL(AVG(CASE WHEN 成绩 > 0 THEN 成绩 END), 0),
    优秀率 = ISNULL(CASE
                WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
                ELSE CONVERT(decimal(4, 2),
                            SUM(CASE WHEN 成绩 > 80 THEN 1 END) * 1.
                            / SUM(CASE WHEN 成绩 > 0 THEN 1 END))
            END, 0),
    及格率 = ISNULL(CASE
                WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
                ELSE CONVERT(decimal(4, 2),
                            SUM(CASE WHEN 成绩 > 60 THEN 1 END) * 1.
                            / SUM(CASE WHEN 成绩 > 0 THEN 1 END))
            END, 0),
    低分率 = ISNULL(CASE
                WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
                ELSE CONVERT(decimal(4, 2),
                            SUM(CASE WHEN 成绩 > 0 AND 成绩 < 30 THEN 1 END) * 1.
                            / SUM(CASE WHEN 成绩 > 0 THEN 1 END))
            END, 0),
    最高 = ISNULL(MAX(成绩), 0),
    最低 = ISNULL(MIN(CASE WHEN 成绩 > 0 THEN 成绩 END), 0),
    段90 = ISNULL(SUM(CASE WHEN 成绩 >= 90 THEN 1 END), 0),
    段80 = ISNULL(SUM(CASE WHEN 成绩 >= 80 AND 成绩 < 90 THEN 1 END), 0),
    段70 = ISNULL(SUM(CASE WHEN 成绩 >= 70 AND 成绩 < 80 THEN 1 END), 0),
    段60 = ISNULL(SUM(CASE WHEN 成绩 >= 60 AND 成绩 < 70 THEN 1 END), 0),
    段50 = ISNULL(SUM(CASE WHEN 成绩 >= 50 AND 成绩 < 60 THEN 1 END), 0),
    段40 = ISNULL(SUM(CASE WHEN 成绩 >= 40 AND 成绩 < 50 THEN 1 END), 0),
    段30 = ISNULL(SUM(CASE WHEN 成绩 >= 30 AND 成绩 < 40 THEN 1 END), 0),
    段00 = ISNULL(SUM(CASE WHEN 成绩 > 0 AND 成绩 <= 30 THEN 1 END), 0)
FROM @v v_cjb
GROUP BY 班级, 科目 WITH ROLLUP
WHERE 班级 IS NOT NULL