需求：依据各班同学各科目的成绩，按设定的分段计算出优秀率、优良率、及格率等。
实现：表间公式
写法：
下面截图是导入的各班同学各科目的成绩：

最终得到下面的结果

下面是写法：

1. Set NoCount On
   
2. 定义变量 @Field AS VARCHAR(20);
   
3. 定义变量 @Cname AS VARCHAR(20);
   
4. 定义变量 @sql AS VARCHAR(max);
   
5. IF Object_id( 'tempdb..#ScoreAnalysisResult' ) IS NOT NULL
   
6.   DROP TABLE #ScoreAnalysisResult
   
7. CREATE TABLE #ScoreAnalysisResult
   
8.   (
   
9.      学年    VARCHAR(50),
   
10.      班级    VARCHAR(10),
    
11.      科目    VARCHAR(10),
    
12.      年级平均分 NUMERIC(18, 2),
    
13.      班级平均分 NUMERIC(18, 2),
    
14.      优秀率   NUMERIC(18, 4),
    
15.      良好率   NUMERIC(18, 4),
    
16.      及格率   NUMERIC(18, 4)
    
17.   )
    
18. 定义变量 Cursor1 CURSOR FOR
    
19.    SELECT    'F_200'   ,'语文'
    
20.   UNION  SELECT    'F_201'   ,'数学'
    
21.   UNION  SELECT    'F_202'   ,'英语'
    
22.   UNION  SELECT    'F_203'   ,'物理'
    
23.   UNION  SELECT    'F_204'   ,'化学'
    
24.   UNION  SELECT    'F_205'   ,'生物'
    
25.   UNION  SELECT    'F_206'   ,'历史'
    
26.   UNION  SELECT    'F_207'   ,'地理'
    
27.   UNION  SELECT    'F_231'   ,'政治'
    
28. OPEN Cursor1
    
29. Fetch Next FROM Cursor1 INTO @Field, @cname
    
30. WHILE @@Fetch_STATUS = 0
    
31.   BEGIN
    
32.       设置变量值 @sql ='
    
33. Insert Into #ScoreAnalysisResult(学年,班级,科目,年级平均分,班级平均分,优秀率,良好率,及格率)
    
34. Select Distinct
    
35. F_237 As 学年
    
36. ,F_199 As 班级
    
37. ,''' + @Cname + ''' As 科目
    
38. ,Avg(' + @Field + ') Over() As 年级平均分
    
39. ,Avg(' + @Field + ') Over(Partition By F_199) As 班级平均分
    
40. ,四舍五入(Cast(Count(Case When ' + @Field + '>=120 Then 1 Else Null end ) Over(Partition By F_199) As Float) / Cast(Count(F_198) Over(Partition By F_199 ) As Float) ,4) As 优秀率
    
41. ,四舍五入(Cast(Count(Case When ' + @Field + '>=100 Then 1 Else Null end ) Over(Partition By F_199) As Float) / Cast(Count(F_198) Over(Partition By F_199 ) As Float) ,4) As 良好率
    
42. ,四舍五入(Cast(Count(Case When ' + @Field + '>=90  Then 1 Else Null end ) Over(Partition By F_199) As Float) / Cast(Count(F_198) Over(Partition By F_199 ) As Float) ,4) As 及格率
    
43. From T_36,T_37 Where 1=1  并且 T_36.ID = T_37.ID '
    
44.       EXEC(@sql)
    
45.       Fetch Next FROM Cursor1 INTO @Field, @cname
    
46.   END
    
47. CLOSE Cursor1
    
48. DEALLOCATE Cursor1
    
49. SELECT 排除重复
    
50. 班级 AS 本报表.[学生考试成绩分析_明细表].[所代班级]x填充不锁定,
    
51. 科目 AS 本报表.[学生考试成绩分析_明细表].[学科]x填充不锁定,
    
52. 优秀率 AS 本报表.[学生考试成绩分析_明细表].[优秀率]x填充不锁定,
    
53. 良好率 AS 本报表.[学生考试成绩分析_明细表].[良好率]x填充不锁定,
    
54. 及格率 AS 本报表.[学生考试成绩分析_明细表].[及格率]x填充不锁定,
    
55. 班级平均分 AS 本报表.[学生考试成绩分析_明细表].[平均分]x填充不锁定
    
56. FROM  #ScoreAnalysisResult
    
57. WHERE 1=1
    
58. Order By 班级,科目 Desc
    
59. Set NoCount Off

复制代码

需求：依据各班同学各科目的成绩，按设定的分段计算出优秀率、优良率、及格率等。
实现：表间公式
写法：
下面截图是导入的各班同学各科目的成绩：

最终得到下面的结果

下面是写法：

1. Set NoCount On
   
2. 定义变量 @Field AS VARCHAR(20);
   
3. 定义变量 @Cname AS VARCHAR(20);
   
4. 定义变量 @sql AS VARCHAR(max);
   
5. IF Object_id( 'tempdb..#ScoreAnalysisResult' ) IS NOT NULL
   
6.   DROP TABLE #ScoreAnalysisResult
   
7. CREATE TABLE #ScoreAnalysisResult
   
8.   (
   
9.      学年    VARCHAR(50),
   
10.      班级    VARCHAR(10),
    
11.      科目    VARCHAR(10),
    
12.      年级平均分 NUMERIC(18, 2),
    
13.      班级平均分 NUMERIC(18, 2),
    
14.      优秀率   NUMERIC(18, 4),
    
15.      良好率   NUMERIC(18, 4),
    
16.      及格率   NUMERIC(18, 4)
    
17.   )
    
18. 定义变量 Cursor1 CURSOR FOR
    
19.    SELECT    'F_200'   ,'语文'
    
20.   UNION  SELECT    'F_201'   ,'数学'
    
21.   UNION  SELECT    'F_202'   ,'英语'
    
22.   UNION  SELECT    'F_203'   ,'物理'
    
23.   UNION  SELECT    'F_204'   ,'化学'
    
24.   UNION  SELECT    'F_205'   ,'生物'
    
25.   UNION  SELECT    'F_206'   ,'历史'
    
26.   UNION  SELECT    'F_207'   ,'地理'
    
27.   UNION  SELECT    'F_231'   ,'政治'
    
28. OPEN Cursor1
    
29. Fetch Next FROM Cursor1 INTO @Field, @cname
    
30. WHILE @@Fetch_STATUS = 0
    
31.   BEGIN
    
32.       设置变量值 @sql ='
    
33. Insert Into #ScoreAnalysisResult(学年,班级,科目,年级平均分,班级平均分,优秀率,良好率,及格率)
    
34. Select Distinct
    
35. F_237 As 学年
    
36. ,F_199 As 班级
    
37. ,''' + @Cname + ''' As 科目
    
38. ,Avg(' + @Field + ') Over() As 年级平均分
    
39. ,Avg(' + @Field + ') Over(Partition By F_199) As 班级平均分
    
40. ,四舍五入(Cast(Count(Case When ' + @Field + '>=120 Then 1 Else Null end ) Over(Partition By F_199) As Float) / Cast(Count(F_198) Over(Partition By F_199 ) As Float) ,4) As 优秀率
    
41. ,四舍五入(Cast(Count(Case When ' + @Field + '>=100 Then 1 Else Null end ) Over(Partition By F_199) As Float) / Cast(Count(F_198) Over(Partition By F_199 ) As Float) ,4) As 良好率
    
42. ,四舍五入(Cast(Count(Case When ' + @Field + '>=90  Then 1 Else Null end ) Over(Partition By F_199) As Float) / Cast(Count(F_198) Over(Partition By F_199 ) As Float) ,4) As 及格率
    
43. From T_36,T_37 Where 1=1  并且 T_36.ID = T_37.ID '
    
44.       EXEC(@sql)
    
45.       Fetch Next FROM Cursor1 INTO @Field, @cname
    
46.   END
    
47. CLOSE Cursor1
    
48. DEALLOCATE Cursor1
    
49. SELECT 排除重复
    
50. 班级 AS 本报表.[学生考试成绩分析_明细表].[所代班级]x填充不锁定,
    
51. 科目 AS 本报表.[学生考试成绩分析_明细表].[学科]x填充不锁定,
    
52. 优秀率 AS 本报表.[学生考试成绩分析_明细表].[优秀率]x填充不锁定,
    
53. 良好率 AS 本报表.[学生考试成绩分析_明细表].[良好率]x填充不锁定,
    
54. 及格率 AS 本报表.[学生考试成绩分析_明细表].[及格率]x填充不锁定,
    
55. 班级平均分 AS 本报表.[学生考试成绩分析_明细表].[平均分]x填充不锁定
    
56. FROM  #ScoreAnalysisResult
    
57. WHERE 1=1
    
58. Order By 班级,科目 Desc
    
59. Set NoCount Off

复制代码