全面掌握MS ACCESS SQL（34）

时　间:2018-01-19 15:29:50
作　者:Big Young   ID：252  城市：襄阳
摘　要:    TRANSFORM语句的完整语法。
正　文:

第十章 运用TRANSFORM创建交叉表

交叉表（Cross Tabulations）是一种常用的分类汇总表格。在统计学中，交叉表是矩阵格式的一种表格，显示变量的（多变量）频率分布。交叉表被广泛用于调查研究，商业智能，工程和科学研究。它们提供了两个变量之间的相互关系的基本画面，可以帮助他们发现它们之间的相互作用。卡尔·皮尔逊（Karl Pearson）首先在“关于应变的理论及其关联理论与正常相关性”中使用了交叉表。

多元统计学的一个关键问题是找到高维应变表中包含的变量的（直接）依赖结构。如果某些有条件的独立性被揭示，那么甚至可以以更智能的方式来完成数据的存储。为了做到这一点，可以使用信息理论概念，它只能从概率分布中获得信息，这可以通过相对频率从交叉表中容易地表示。

利用交叉表查询数据非常直观明了，被广泛应用。交叉表查询也是数据库的一个特点。

第一节 TRANSFORM语句的完整语法

交叉表查询是将来源于某个表中的字段进行分组，一组列在交叉表左侧，一组列在交叉表上部。交叉表查询是将来源于某个表中的字段进行分组，一组列在交叉表左侧，一组列在交叉表上部，并在交叉表行与列交叉处显示表中某个字段的各种计算值。

交叉表分两种：静态交叉表和动态交叉表。

使用交叉表查询来计算和重构数据，可以简化数据分析。交叉表查询计算数据的总和，平均值，计数及其他类型的统计，并将它们分组，一组列在数据表左侧作为交叉表的行字段，另一组列在数据表的顶端作为交叉表的列字段。在ACCSS SQL中创建交叉表的语句是TRANSFORM，其完整的语法结构如下：

一、TRANSFORM语句的基本语法

TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, ...]])];

TRANSFORM语句包含以下部分：

二、TRANSFORM语法的几点说明

使用交叉表查询汇总数据时，将从作为列标题的指定字段或表达式中选择值，以便能够以一种比使用选择查询更紧凑的方式来查看数据。

TRANSFORM是可选的，但如果包括它，则应为SQL字符串中的第一个语句。它在指定作为行标题的字段的Select语句之前，在指定行分组方法的GROUP BY子句之前。您也可以包含其他子句（例如，指定其他选择或排序条件的Where）。还可以在交叉表查询中使用子查询作为谓词，特别是在Where子句中。

pivotfield中返回的值作为查询结果集中的列标题。例如，在交叉表查询中如果依据月销售量来透视销售数据，将会创建12个列。可以约束pivotfield以便从可选IN子句中所列出的固定值（value1，value2）内选择标题。也可以包含固定值用于没有数据来创建其他列的情况。

三、TRANSFORM语句在VBA编程中的应用实例

以下示例使用SQL TRANSFORM子句创建交叉表查询，以显示每个雇员在1994年的每个日历季度完成的订单数。运行此过程需要使用SQLTRANSFORMOutput 函数。

VBA

Sub TransformX1()

 

    Dim dbs As Database

    Dim strSQL As String

    Dim qdfTRANSFORM As QueryDef

 

    strSQL = "PARAMETERS prmYear SHORT; TRANSFORM " _

        & "Count(OrderID) " _

        & "Select FirstName & "" "" & LastName AS " _

        & "FullName FROM Employees INNER JOIN orders " _

        & "ON Employees.EmployeeID = " _

        & "Orders.EmployeeID Where DatePart " _

        & "(""yyyy"", orderDate) = [prmYear] "

  

       strSQL = strSQL & "GROUP BY FirstName & " _

        & """ "" & LastName " _

        & "ORDER BY FirstName & "" "" & LastName " _

        & "PIVOT DatePart(""q"", orderDate)"

    

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

 

    Set qdfTRANSFORM = dbs.CreateQueryDef _

        ("", strSQL)

     

    SQLTRANSFORMOutput qdfTRANSFORM, 1994

    

    dbs.Close

 

End Sub

以下示例使用SQL TRANSFORM子句创建一个稍微复杂的交叉表查询，以显示每个雇员在1994年的每个日历季度所完成的订单的总金额（美元）。运行此过程需要使用SQLTRANSFORMOutput函数。

Sub TransformX2()

 

    Dim dbs As Database

    Dim strSQL As String

    Dim qdfTRANSFORM As QueryDef

 

    strSQL = "PARAMETERS prmYear SMALLINT; TRANSFORM " _

        & "Sum(Subtotal) Select FirstName & "" """ _

        & "& LastName AS FullName " _

        & "FROM Employees INNER JOIN " _

        & "(Orders INNER JOIN [Order Subtotals] " _

        & "ON orders.OrderID = " _

        & "[Order Subtotals].OrderID) " _

        & "ON Employees.EmployeeID = " _

        & "Orders.EmployeeID Where DatePart" _

        & "(""yyyy"", orderDate) = [prmYear] "

   

       strSQL = strSQL & "GROUP BY FirstName & "" """ _

        & "& LastName " _

        & "ORDER BY FirstName & "" "" & LastName " _

        & "PIVOT DatePart(""q"",OrderDate)"        

        

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

 

    Set qdfTRANSFORM = dbs.CreateQueryDef _

        ("", strSQL)

    

    SQLTRANSFORMOutput qdfTRANSFORM, 1994

    

    dbs.Close

 

End Sub

 

Function SQLTRANSFORMOutput(qdfTemp As QueryDef, _

    intYear As Integer)

    

    Dim rstTRANSFORM As Recordset

    Dim fldLoop As Field

    Dim booFirst As Boolean

 

    qdfTemp.PARAMETERS!prmYear = intYear

    Set rstTRANSFORM = qdfTemp.OpenRecordset()

    

    Debug.Print qdfTemp.SQL

    Debug.Print

    Debug.Print , , "Quarter"

 

    With rstTRANSFORM

        booFirst = True

        For Each fldLoop In .Fields

            If booFirst = True Then

                Debug.Print fldLoop.Name

                Debug.Print , ;

                booFirst = False

            Else

                Debug.Print , fldLoop.Name;

            End If

        Next fldLoop

        Debug.Print

        

        Do While Not .EOF

            booFirst = True

            For Each fldLoop In .Fields

                If booFirst = True Then

                    Debug.Print fldLoop

                    Debug.Print , ;

                    booFirst = False

                Else

                    Debug.Print , fldLoop;

                End If

            Next fldLoop

            Debug.Print

            .MoveNext

        Loop

    End With

    

End Function

Access软件网QQ交流群 (群号:54525238) 　　　　　　Access源码网店