SQL语句之利用UNION查询生成报表

        前段时间做了一个在线考试系统，因为要生成报表，所以用到了很多SQL语句，下面介绍一下UNION 语句在生成报表中的应用：
        一、生成报表中经常有合计这一项，可以通过sql中的UNION来实现，不需要额外的编写程序。下面是我在做在线考试系统时生成试卷的双向细目报表所用的SQL语句。其中的 SXXMTableC()函数得到双向细目表的数据，再通过 SXXMTableD()函数利用UNION ALL实现底部的合计。

CREATE   FUNCTION  SXXMTableD ( @pid   int )
RETURNS   TABLE
  AS
RETURN   
( SELECT   id, 章节,小计,核心内容,重点内容,一般内容, 回忆, 理解,问题解决,选择题, 填空题,判断题,计算题
FROM  dbo.SXXMTableC( @pid )
UNION   ALL
SELECT   Max (id) + 1 , ' 合计 ' , sum (小计), sum (核心内容), sum (重点内容), sum (一般内容), sum (回忆), sum (理解),
      sum (问题解决), sum (选择题), sum (填空题), sum (判断题), sum (计算题)
FROM  dbo.SXXMTableC( @pid )
)

       二、在统计试卷成绩的各个指标的时候用UNION也很方便。其中StudentGradeClass(@epid,@sclass) 函数是统计epid场的考试sclass班的同学的分数。GradeSection(@epid,@sclass)是统计 epid场的考试sclass班的分数段 。

/**/ /*成绩分析表
@epid: 考试安排ID
@sclass: 要统计的班级
Return:统计表
*/
CREATE   FUNCTION  GradeStat( @epid   int  , @sclass   varchar ( 50 ))
  RETURNS   TABLE
  AS
RETURN  
( SELECT   ' 平均分 '   AS  统计项, AVG (成绩)  AS  数值 
FROM  StudentGradeClass( @epid , @sclass )
UNION   ALL
SELECT   ' 最高分 '   AS  统计项, Max (成绩)  AS  数值 
FROM  StudentGradeClass( @epid , @sclass )
UNION   ALL
SELECT   ' 最低分 '   AS  统计项, Min (成绩)  AS  数值 
FROM  StudentGradeClass( @epid , @sclass )
UNION   ALL
SELECT   ' 通过率 '   AS  统计项, CAST ( COUNT ( * )  AS   float ) / ( SELECT   COUNT ( * )  FROM  StudentGradeClass( @epid , @sclass ))
FROM  StudentGradeClass( @epid , @sclass )
WHERE  成绩 >= 60
UNION   ALL
SELECT   ' 方差 '   AS  统计项, Var (成绩)  AS  数值 
FROM  StudentGradeClass( @epid , @sclass )
UNION   ALL
SELECT   ' 标准差 '   AS  统计项, stdev (成绩)  AS  数值 
FROM  StudentGradeClass( @epid , @sclass )
UNION   ALL
SELECT   *
FROM   GradeSection( @epid , @sclass )
)

       三、在难度区分度双向细目标准也用到了UNION。其中 DiffDiscrimTableA(@epid int,@pid int,@PMin float,@PMax float,@RMin float,@RMax float)函数查询某场考试某份试卷对于难度范围PMin到PMax、区分度范围RMin到RMax时的试题占的百分比。 STFXTableC( @epid , @pid )为试题分析表的查询函数。

CREATE   FUNCTION  DiffDiscrimTableB( @epid   int , @pid   int )
  RETURNS   TABLE
  AS
RETURN   
( SELECT   ' 0.3≤P≤0.7 R≥0.15 ' + char ( 10 ) + ' 难度和区分度均合适 '   AS  dd, dbo.DiffDiscrimTableA( @epid , @pid , 0.3 , 0.7 , 0.15 , 1 )   AS  题目名称 , COUNT ( * )   AS  题目数
FROM  STFXTableC( @epid , @pid ) 
WHERE  (难度系数 >= 0.3 )  and  (难度系数 <= 0.7 )  and  (区分度 >= 0.15 )

UNION   ALL  
SELECT   ' 0.3≤P≤0.7 R<0.15 ' + char ( 10 ) + ' 难度合适 区分度不合适 '   AS  dd, dbo.DiffDiscrimTableA( @epid , @pid , 0.3 , 0.7 , 0 , 0.14999 )   AS  题目名称, COUNT ( * )  AS  题目数 
FROM  STFXTableC( @epid , @pid ) 
WHERE  (难度系数 >= 0.3 )  and  (难度系数 <= 0.7 )  and  (区分度 < 0.15 )

UNION   ALL  
SELECT   ' P>0.7 R≥0.15 ' + char ( 10 ) + ' 难度偏难 区分度合适 '   AS  dd, dbo.DiffDiscrimTableA( @epid , @pid , 0.70001 , 1 , 0.15 , 1 )   AS  题目名称, COUNT ( * )  AS  题目数 
FROM  STFXTableC( @epid , @pid ) 
WHERE  (难度系数 > 0.7 )  and  (区分度 >= 0.15 )

UNION   ALL
SELECT   ' P<0.3 R≥0.15 ' + char ( 10 ) + ' 难度偏易 区分度合适 '   AS  dd, dbo.DiffDiscrimTableA( @epid , @pid , 0 , 0.29999 , 0.15 , 1 )   AS  题目名称, COUNT ( * )  AS  题目数 
FROM  STFXTableC( @epid , @pid ) 
WHERE  (难度系数 < 0.3 )  and  (区分度 >= 0.15 )

UNION   ALL  
SELECT   ' P>0.7 R<0.15 ' + char ( 10 ) + ' 难度偏难 区分度不合适 '   AS  dd, dbo.DiffDiscrimTableA( @epid , @pid , 0.70001 , 1 , 0 , 0.14999 )  AS  题目名称, COUNT ( * )  AS  题目数 
FROM  STFXTableC( @epid , @pid ) 
WHERE  (难度系数 > 0.7 )  and  (区分度 < 0.15 )

UNION   ALL  
SELECT   ' P<0.3 R<0.15 ' + char ( 10 ) + ' 难度偏易 区分度不合适 '   AS  dd,dbo.DiffDiscrimTableA( @epid , @pid , 0 , 0.29999 , 0 , 0.14999 )   AS  题目名称, COUNT ( * )  AS  题目数 
FROM  STFXTableC( @epid , @pid ) 
WHERE  (难度系数 < 0.3 )  and  (区分度 < 0.15 )
)