Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > Access数据库-查询/SQL语句

全面掌握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语句包含以下部分:

部分

说明

aggfunction

对所选数据进行计算的 SQL 聚合函数。

selectstatement

Select 语句。

pivotfield

希望用于创建查询结果集中列标题的字段或表达式。

value1value2

用于创建列标题的固定值。

二、TRANSFORM语法的几点说明

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

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

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

三、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源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助