管理信息系統中動態交叉表的實現方式研究

摘要：交叉表因其包含的信息量比普通表格要大很多，而且直觀易懂，因此在各種管理信息系統中被廣泛采用。本文以日常生活中常見的學生成績表為例，分別了介紹用Delphi和SQL Server實現動態交叉表的方法。

關鍵詞：管理信息系統; 交叉表; Delphi; SQL Server; Transact-SQL

中圖分類號：TP311文獻標識碼：A 文章編號：1009-3044(2008)31-0995-02

Research of the Implementation of Dynamic Crosstab in MIS

ZHANG Xian-bin， FEI Shu-min

(School of Automation， Southeast University， Nanjing 210096， China)

Abstract: Crosstab is widely used in various MIS because it contain more information and is intuitively easy to understand. This article presents the implement of dynamic crosstab in Delphi or SQL Server taken students' score for example， which is one of the most common crosstab in everyday life.

Key words: MIS; Crosstab; Delphi; SQL Server; Transact-SQL

1 引言

一般的二維表只有列頭，行就是數據；而交叉表是行和列都有相應的行頭和列頭，行和列交叉的單元格中是所對應的數據。交叉表包含的信息量比普通表格要大很多，而且直觀易懂，因此在各種管理信息系統中被廣泛采用。由于關系數據庫的表只能是二維表，所以在應用程序中一般要借助報表控件（如FastReport）來實現交叉表。本文將以日常生活中常見的學生成績表為例，分別介紹用Delphi和SQL Server實現動態交叉表的方法。

2 問題的提出

規范化的數據庫設計不僅可以消除關系數據庫的多義性，而且也可以減少數據的冗余性，一定程度上也使數據查詢方式易于接近自然語言。但如果直接輸出則不符合人們觀看的習慣，因此需要進行一定的轉換，交叉表就是最常見的一種經轉換后的表格形式。

以學生成績表為例，一般顯示給用戶的學生成績表如表1所示，但如果在數據庫中定義的表結構也是這樣，則是不完善的，是有潛在沖突的。如要增加科目，就得更改表結構，特別是大學，專業多、科目多，而有些科目是選修的，這將會使表結構變得相當復雜，有多少科目就得有多少個科目的字段，有部分字段值必然為空。不僅浪費大量的磁盤空間，還會給程序的編寫帶來極大的困難。因此在數據庫設計時應采用如表2所示的滿足范式要求的表結構。

由此看出，規范化的數據雖然具有數據冗余小，易于實現數據常規操作（增加、刪除、修改）等優點，特別是在科目增加或減少時，無需修改表結構，具有良好的擴展性。然而為了方便用戶觀看數據，則需要從表2中篩選出各科目名稱來作為列標題，且對應的成績進行轉置，生成如表1所示的交叉表，通常，交叉表的列標題不是固定的，是根據特定字段的記錄動態產生的，由此就產生了動態交叉表的問題。

3 新建虛擬數據集生成交叉表

3.1 實現步驟

1) 根據條件利用數據集控件（記為ADOQuery1）從數據庫中查詢出符合條件的記錄。

2) 向虛擬數據集（記為ADODataSet1）添加字段，字段名包括交叉表行頭字段、查詢數據集中交叉表列頭字段的不同記錄及必要的匯總字段（如：合計）等，然后調用CreateDataSet過程，得到虛擬數據集的表結構。

3) 遍歷查詢數據集，給虛擬數據集各字段賦值。

具體步驟用流程圖表示如圖1所示。

需要注意的是，ADODataSet1記錄集是虛擬的，和SQL Server中的數據表并無直接聯系。在數據集關閉或程序退出后，這些數據就不存在了。

3.2 關鍵代碼說明

1) 判斷ADODataSet1是否存在course字段，可以使用IndexOf函數，如存在則返回字段在數據集中的索引值，否則返回-1。完整代碼如下：

course:= ADOQuery1.FieldByName('科目').AsString; //course為定義的字符型變量

if ADODataSet1.FieldDefs.IndexOf(course) = -1 then //字段不存在，IndexOf函數返回-1

ADODataSet1.FieldDefs.Add(course， ftfloat);

2) 判斷ADODataSet1中是否存在交叉表行頭字段值為name的記錄，應使用Locate方法。Locate方法的返回值是布爾值，表示是否成功找到了符合條件的記錄。如果找到，則返回True，否則返回False。當Locate方法成功找到了符合條件的記錄后，數據集指針就會移至這條記錄，此時可直接進行編輯（Edit）；沒有找到，則新增（Append）。

3) 在遍歷數據集前應當調用DisableControls禁止刷新，這樣能夠加快遍歷的速度，因為刷新也是要花費時間的；遍歷結束后，應當調用EnableControls恢復刷新。

4利用動態Transact-SQL語句生成交叉表

4.1 實現思路

1) 在交叉表中，當列頭字段的值固定時，可以使用CASE函數和聚合函數得到。例如，如果‘科目’字段只有‘數據結構’、‘微機原理’，則可以使用以下SQL語句（記學生成績表名為SCORE，下同）：

SELECT 姓名，

數據結構=SUM(CASE 科目 WHEN '數據結構' THEN 成績 END)，

微機原理=SUM(CASE 科目 WHEN '微機原理' THEN 成績 END)

FROM SCORE GROUP BY 姓名

其中利用了CASE語句判斷，只有是相應的列，才取需要統計的成績，然后再合計。常用的聚合函數有SUM、AVG、MAX、MIN、COUNT，可根據情況選擇不同的聚合函數。

在動態交叉表中，列標題不是固定的，是根據特定字段的記錄動態產生的，而普通的Transact-SQL語句無法創建列數動態的查詢或者視圖，此時，就需要配合動態Transact-SQL語句來實現。

2) 動態Transact-SQL語句是指在執行Transact-SQL語句時，根據用戶指定的參數條件或者其它條件決定或生成要執行的Transact-SQL語句。SQL Server通過EXECUTE或sp_executesql來執行動態Transact-SQL語句。

3) 使用動態Transact-SQL語句實現交叉表，主要的問題是如何根據待處理表中的數據，得到生成交叉表處理的Transact-SQL語句。假設有一表Tbl(Col nvarchar(10))，該表有3條記錄：'A'，'B'，'C'，執行以下SQL語句：

DECLARE @s varchar(100)

SELECT @s= ISNULL(@s+'，'， '')+Col FROM Tbl

SELECT @s

將會得到結果：A，B，C。因此，可以使用這種方法得到形如1中的生成交叉表處理的Transact-SQL語句。

簡而言之，利用動態Transact-SQL語句生成交叉表就是利用3中的方法拼接得到形如1中Transact-SQL語句的字符串，然后通過EXECUTE或sp_executesql來執行該字符串。

4.2 代碼及說明

該方法一般使用存儲過程來實現，首先新建一存儲過程（記為usp_CrossTable），Transact-SQL語句如下：

CREATE PROCEDURE [dbo].[usp_CrossTable]

AS

DECLARE @SQL varchar(8000)

SET @SQL='select 姓名'

SELECT @SQL = @SQL + '，sum(case 科目 when ''' +科目+ ''' then 成績 else 0 end) as ['+科目+']'

FROM (SELECT DISTINCT 科目 FROM SCORE) AS a

SET @SQL= @SQL + 'from SCORE group by 姓名'

EXEC(@SQL)

在程序端，使用TADOStoredProc1控件執行該存儲過程，即可得到結果。

5 結束語

本文以學生成績表為例分別介紹了在Delphi和SQL Server中實現動態交叉表的方法，在實際應用中，可能會有更復雜的情況，但實現原理是相同的。使用以上方法，不僅可以節約報表控件的投資；還能達到所見即所得的效果。另外，以上兩種方法均可以寫成通用事件或存儲過程在使用時直接調用，在此不再贅述。

參考文獻：

[1] 鄭人杰，等. 實用軟件工程[M]. 北京：清華大學出版社，1997.

[2] 薩師煊. 數據庫系統概論[M]. 北京：高等教育出版社，2000.

[3] 張新林. 范式下的Oracle數據庫設計及其動態交叉表的生成[J]. 計算機與現代化，2005，21(9):57-59.

[4] 戴薇，張士軍. 在SQL Server數據庫中利用存儲過程實現動態交叉表 [J]. 計算機與數字工程，2006，34(12):126-128.

[5] 索劍. SQL Server交叉表的客戶端實現方案[J]. 計算機應用，2002，12(12):124-125.

[6] 明日科技. Delphi開發技術大全[M]. 北京：人民郵電出版社，2007.

[7] 鄒建. 中文版SQL Server2000開發與管理應用實例[M]. 北京: 人民郵電出版社，2005.