论文部分内容阅读
[摘 要]公司和企业中经常要求对数据进行分类统计，通过交叉表查询可以实现此目的，使得显示效果清晰明了。本文主要在SQL Server中以学生成绩表为例研究了交叉表查询，实现对学生各门课程成绩的统计。
[关键词] 交叉表查询，case-when语句， SQL Server
中国分类号：TP311.1
1引言
现今数据库的应用非常广泛，SQLServer是微软公司开发的一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的关系型数据库管理系统，提供了基于事务的企业级信息管理系统方案。SQLServer在企业、政府部门及学校等管理系统中得到广泛的应用，在这些系统中经常要对数据进行分类统计，使得显示效果更加清晰，习惯使用交叉表查询显示结果。下面主要通过学校管理系统中学生成绩查询作为示例研究交叉表查询。
2 交叉表查询
2.1交叉表查询的概念和功能
交叉表查询即在原有关系表的基础上，以行和列的字段作为标题和条件选取交叉表查询并在行与列的交叉处对数据进行汇总、统计等计算。
例如：学生成绩表如图1，经过交叉表查询后的结果如图2，即以name（姓名）为行标题，以subject（课程）为列标题，行和列交叉处对source（成绩）数据进行汇总。从对图1和图2对比中，可以看出交叉表查询结果非常清晰。
图1 图2
2.2交叉表查询的分类
交叉表查询一般分为静态和动态交叉表。静态交叉表就是交叉表中的列是预先设定好的，列数是一个确定的数值。如图2所示，语文、数学、英语三科的成绩放在学生成绩表，需要从学生成绩表得到每一个学生的语文、数学、英语三科的交叉成绩。动态交叉表即交叉表中的列是未知的或者列数比较多，不能通过静态交叉表的方法把所有列都罗列出来，此是就要使用动态交叉表的方法来实现。
2.3交叉表查询的实现
首先在SQLServer查询分析器中输入如下命令来建立一个学生选课程成绩表score 表，
CREATE TABLE [score]
（ [sno] [nchar]（10） NULL ，
[name] [nvarchar] （50） NULL ，
[subject] [nvarchar] （50） NULL ，
[Source] [numeric]（15，0） NULL） ON [PRIMARY]
Go
通过INSERT语句给score表中插入如图1中的数据，例如插入第一条命令如下：
INSERT INTO [score] （[sno]，[name]，[subject]，[Source]） values （'1001'，'李燕'，'语文'，60），其它数据的添加方法相同。
静态交叉表查询的实现语句如下：
select name，sum（case subject when '数学' then source else 0 end） as '数学'，
sum（case subject when '英语' then source else 0 end） as '英语'，
sum（case subject when '语文' then source else 0 end） as '语文'
from score
group by name
在上面查询语句中主要用到Case-when语句，其语法格式如下：
CASE search_expression
WHEN expression1 THEN result1，
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
End
search_expression是要搜索的表达式，要与when语句后的expression1、expression2…expressionN对比，假如search_expression与expression1值相等的，就返回result1，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
添加汇总的动态交叉表查询语句如下：
declare @sql varchar（4000）
set @sql = 'select name，'
select @sql = @sql + 'sum（case subject when '''+subject+'''
then source else 0 end） as '''+subject+'''，'
from （select distinct subject from score） as b
select @sql = left（@sql，len（@sql）-1） +'，sum（source） as 总成绩'
+'，convert（dec（5，1），avg（source））as 平均成绩'
+' from score group by name'
exec（@sql）
其中len（@sql）-1的作用是求出字符串的长度减去一，因为在@sql字符串后有一个逗号，在使用left（）函数取出去掉逗号的字符串。语句查询的结果如图3
图3
3结束语
综上所述，SQL Server数据库中交叉表查询语句功能强大，查询结果清晰，本文主要通过简单的查询语句实现静态和动态交叉表查询，以后会进一步研究通过创建带参数的存储过程完成功能更加强大的交叉表查询的研究。
参考文献：
[1] 刘丽，潘志红.SQL Server 数据库基础教程[M].北京：機械工业出版，2011.
[2] 李锡辉.SQL Server 2008 数据库案例教程[M].北京：清华大学出版社，2011.
作者简介：
张慧娥 1977年出生，讲师，主要研究方向数据库，软件工程。
王晓迎 1963年出生，高级平面设计师，主要研究方向动漫设计。
[关键词] 交叉表查询，case-when语句， SQL Server
中国分类号：TP311.1
1引言
现今数据库的应用非常广泛，SQLServer是微软公司开发的一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的关系型数据库管理系统，提供了基于事务的企业级信息管理系统方案。SQLServer在企业、政府部门及学校等管理系统中得到广泛的应用，在这些系统中经常要对数据进行分类统计，使得显示效果更加清晰，习惯使用交叉表查询显示结果。下面主要通过学校管理系统中学生成绩查询作为示例研究交叉表查询。
2 交叉表查询
2.1交叉表查询的概念和功能
交叉表查询即在原有关系表的基础上，以行和列的字段作为标题和条件选取交叉表查询并在行与列的交叉处对数据进行汇总、统计等计算。
例如：学生成绩表如图1，经过交叉表查询后的结果如图2，即以name（姓名）为行标题，以subject（课程）为列标题，行和列交叉处对source（成绩）数据进行汇总。从对图1和图2对比中，可以看出交叉表查询结果非常清晰。
图1 图2
2.2交叉表查询的分类
交叉表查询一般分为静态和动态交叉表。静态交叉表就是交叉表中的列是预先设定好的，列数是一个确定的数值。如图2所示，语文、数学、英语三科的成绩放在学生成绩表，需要从学生成绩表得到每一个学生的语文、数学、英语三科的交叉成绩。动态交叉表即交叉表中的列是未知的或者列数比较多，不能通过静态交叉表的方法把所有列都罗列出来，此是就要使用动态交叉表的方法来实现。
2.3交叉表查询的实现
首先在SQLServer查询分析器中输入如下命令来建立一个学生选课程成绩表score 表，
CREATE TABLE [score]
（ [sno] [nchar]（10） NULL ，
[name] [nvarchar] （50） NULL ，
[subject] [nvarchar] （50） NULL ，
[Source] [numeric]（15，0） NULL） ON [PRIMARY]
Go
通过INSERT语句给score表中插入如图1中的数据，例如插入第一条命令如下：
INSERT INTO [score] （[sno]，[name]，[subject]，[Source]） values （'1001'，'李燕'，'语文'，60），其它数据的添加方法相同。
静态交叉表查询的实现语句如下：
select name，sum（case subject when '数学' then source else 0 end） as '数学'，
sum（case subject when '英语' then source else 0 end） as '英语'，
sum（case subject when '语文' then source else 0 end） as '语文'
from score
group by name
在上面查询语句中主要用到Case-when语句，其语法格式如下：
CASE search_expression
WHEN expression1 THEN result1，
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
End
search_expression是要搜索的表达式，要与when语句后的expression1、expression2…expressionN对比，假如search_expression与expression1值相等的，就返回result1，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
添加汇总的动态交叉表查询语句如下：
declare @sql varchar（4000）
set @sql = 'select name，'
select @sql = @sql + 'sum（case subject when '''+subject+'''
then source else 0 end） as '''+subject+'''，'
from （select distinct subject from score） as b
select @sql = left（@sql，len（@sql）-1） +'，sum（source） as 总成绩'
+'，convert（dec（5，1），avg（source））as 平均成绩'
+' from score group by name'
exec（@sql）
其中len（@sql）-1的作用是求出字符串的长度减去一，因为在@sql字符串后有一个逗号，在使用left（）函数取出去掉逗号的字符串。语句查询的结果如图3
图3
3结束语
综上所述，SQL Server数据库中交叉表查询语句功能强大，查询结果清晰，本文主要通过简单的查询语句实现静态和动态交叉表查询，以后会进一步研究通过创建带参数的存储过程完成功能更加强大的交叉表查询的研究。
参考文献：
[1] 刘丽，潘志红.SQL Server 数据库基础教程[M].北京：機械工业出版，2011.
[2] 李锡辉.SQL Server 2008 数据库案例教程[M].北京：清华大学出版社，2011.
作者简介：
张慧娥 1977年出生，讲师，主要研究方向数据库，软件工程。
王晓迎 1963年出生，高级平面设计师，主要研究方向动漫设计。