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)
)