啊啦神灯

   :: 首页 :: 联系 :: 聚合  :: 管理
  11 Posts :: 0 Stories :: 1 Comments :: 0 Trackbacks

为了说明问题,我们用SqlServer自带的事例数据库(Northwind)来进行验证,所有的例子请放到Northwind中运行,我可能会省略Use语句,所引用的表,都是Northwind中的,下面我就不再说明了

这里指的交叉表,就是象Access的交叉表查询一样的效果,比如Employees表中City字段代表了城市的名称,TitleOfCourtesy代表称呼,我们希望按照City和TitleOfCourtesy的情况来统计ReportsTo字段的合计数(本统计没有任何实际意义,只是挑选一些记录包含重复内容的字段来说明情况),并显示成以下格式:(TitleOfCourtesy作为行,City作为列)
探讨这个问题之前,我们首先来看一下如何建立静态的交叉表,也就是说列数固定的交叉表,这种情况其实只要一句简单的Select查询就可以搞定:

SELECT  TitleOfCourtesy, 
  
SUM ( CASE  City  WHEN   ' London '   THEN  ReportsTo  ELSE   NULL   END AS   [ London City ]
 
SUM ( CASE  City  WHEN   ' Redmond '   THEN  ReportsTo  ELSE   NULL   END AS   [ Redmond City ]
 
SUM ( CASE  City  WHEN   ' Seattle '   THEN  ReportsTo  ELSE   NULL   END AS   [ Seattle City ]
FROM  Employees  GROUP   BY  TitleOfCourtesy

其中利用了CASE语句判断,如果是相应的列,则取需要统计的ReportsTo数值,否则取NULL,然后再合计
其中有两个常见问题说明一下:
a、用NULL而不用0是有道理的,假如用0,虽然求和函数SUM可以取到正确的数,但类似COUNT函数(取记录个数),结果就不对了,因为Null不算一条记录,而0要算,同理空字串("")也是这样,总之在这里应该用NULL,这样任何函数都没问题。

b、假如在视图的设计界面保存以上的查询,则会报错“没有输出列”,从而无法保存,其实只要在查询前面加上一段:Create View ViewName AS ...,ViewName是你准备给查询起的名称,...就是我们的查询,然后运行一下,就可以生成视图了,对于其他一些设计器不支持的语法,也可以这样保存。

以上查询作用也很大,对于很多情况,比如按照季度统计、按照月份统计等列头内容固定的情况,这样就行了,但往往大多数情况下列头内容是不固定的,象City,用户随时可能删除、添加一些城市,这种情况,我们就需要用存储过程来解决:

总体思路其实很简单,首先检索列头信息,形成一个游标,然后遍历游标,将上面查询语句里Case判断的内容用游标里的值替代,形成一条新的Sql查询,然后执行,返回结果,就可以了,以下是我写的一个存储过程,供大家参考:

CREATE   procedure  CorssTab 
@strTabName   as   varchar ( 50 =   ' Employees ' -- 此处放表名
@strCol   as   varchar ( 50 =   ' City ' ,                        -- 表头分组依据字段
@strGroup   as   varchar ( 50 =   ' TitleOfCourtesy ' , -- 分组字段
@strNumber   as   varchar ( 50 =   ' ReportsTo ' ,     -- 被统计的字段
@strSum   as   varchar ( 10 =   ' Sum '                       -- 运算方式
AS

DECLARE   @strSql   as   varchar ( 1000 ),  @strTmpCol   as   varchar ( 100 )
EXECUTE  ( ' DECLARE corss_cursor CURSOR FOR SELECT DISTINCT  '   +   @strCol   +   '  from  '   +   @strTabName   +   '  for read only  ' -- 生成游标
begin
  
SET  nocount  ON  
  
SET   @strsql   = ' select  '   +   @strGroup   +   ' '   +   @strSum   +   ' ( '   +   @strNumber   +   ' ) AS [ '   +   @strSum   +   '  of  '   +   @strNumber   +   ' ] '   -- 查询的前半段

  
OPEN  corss_cursor
  
while  ( 0 = 0 )
  
BEGIN
    
FETCH   NEXT   FROM  corss_cursor  -- 遍历游标,将列头信息放入变量@strTmpCol
     INTO   @strTmpCol
    
if  ( @@fetch_status <> 0 break
          
SET   @strsql   =   @strsql   +   ' '   +   @strSum   +   ' (CASE  '   +   @strCol   +   '  WHEN  '''   +   @strTmpCol   +   '''  THEN  '   +   @strNumber   +   '  ELSE Null END) AS [ '   +   @strTmpCol   +   '   '   +   @strCol   +   ' ] '   -- 构造查询
   END
        
SET   @strsql   =   @strsql   +   '  from  '   +   @strTabname   +   '  group by  '   +   @strGroup   -- 查询结尾

  
EXECUTE ( @strsql -- 执行
   IF   @@error   <> 0   RETURN   @@error   -- 如果出错,返回错误代码
   CLOSE  corss_cursor 
  
DEALLOCATE  corss_cursor  RETURN   0   -- 释放游标,返回0表示成功



end
GO

几点说明:
a、这是一个通用存储过程,使用时@strTabName、@strCol、@strGroup、@strNumber、@strSum几个变量设置一下就可以用到其他表上,其中结果集的第二列我加了个合计列
b、为了测试方便,我在存储过程中设置了默认值,就是前面提到的Employees表,这样直接运行时就可以出来我上面提到的结果。
c、使用时,可以把上面的代码复制到企业管理器的查询设计界面Sql窗格,或者查询分析器里运行一下(注意正确选择NorthWind数据库),就可以生成一个存储过程:CorssTab,然后直接运行CorssTab,如果出现本文前面类似的窗格,就表示运行成功了。
d、假如用于其它表,首先需要在你的用户数据库里生成此存储过程(当然也可以放到Master里,然后再加个变量:@DataBase,赋值为数据库名称,然后在上面代码打开指定数据库,这样所有的数据库都可以调用它),当你调用时,采取以下格式:

CorssTab @strTabName = 'Orders', @strCol = 'DATEPART(yy, OrderDate)',@strGroup = 'CustomerID', @strNumber = 'OrderID', @strSum = 'Count'

上面这条语句统计了NorthWind中Orders表里每个客户年度订单数量,大家可以运行试一下效果,虽然列头显示的名称不恰当,但基本效果出来了,相信大家通过对我的代码再作简单修改,可以达到满意的交叉表效果。

posted on 2006-09-18 23:26 啊啦神灯 阅读(712) 评论(0)  编辑 收藏 引用 所属分类: 数据库
只有注册用户登录后才能发表评论。