SQL查詢語言基本教程（2）

SQL查詢語言基本教程（2）

三、GROUP BY...語句
    GROUP BY... 語句實現紀錄分組功能，它通常需要和上面我們提到過的統計函數 SUM、COUNT 等聯合使用，它的語法為：

 SELECT column1[, column2]
 FROM table1[,table2]
 WHERE conditions
 GROUP BY column1[, column2]
 ORDER BY column1[, column2]

范例四：計算學生成績及總成績
    我們依然使用上一章使用的數據庫 c:\db4.mdb，在其中加入一個名字為 db2 的表，表的結構以及數據如下：
 
字段名 學生 科目 成績
 張嚴 語文 86.5
 李永 語文 93
 王為 語文 91
 張嚴 數學 96.5
 李永 數學 90
 王為 數學 87
 張嚴 英語 80.5
 李永 英語 94
 王為 英語 98

    建立新工程，加入DAO定義庫。在Form1中加入一個ListBox控件，然后在Form_load中加入以下代碼：

Private Sub Form_Load()
    Dim rsTemp As Recordset
    Dim dbTemp As Database
    Dim astr As String
   
    Set dbTemp = DBEngine(0).OpenDatabase("c:\db4.mdb", dbOpenSnapshot)
    astr = "SELECT SUM(db2.成績)AS rTotal, FORMAT((AVG(db2.成績)),'###.#') AS rAVG, " & _
            " (db2.學生) AS Student FROM db2 GROUP BY db2.學生"

    Set rsTemp = dbTemp.OpenRecordset(astr)
    If rsTemp.RecordCount > 0 Then
        rsTemp.MoveFirst
        Do Until rsTemp.EOF
            List1.AddItem rsTemp![Student] & Chr(5) & rsTemp![rTotal] & _
                "       " & rsTemp![rAVG]
            rsTemp.MoveNext
        Loop
    End If
End Sub
    在上面的代碼中，我們利用GROUP BY將紀錄根據學生姓名分組，再建立了兩個統計字段rTotal和rAvg并分別利用
統計函數SUM和AVG分別統計各個分組的總成績以及平均成績。要注意的是，在SELECT語句中出現的字段，如果沒有包含
在統計函數內的話，都要包含在GROUP BY子句中。
    另外在上面的SQL查詢中我們還使用了FORMAT子句，這是SQL中的轉換和格式化語句中的一個，該語句的語法同VB中
的Format語句是一樣的，相似的語句還有FIX語句。需要注意的一點是，雖然在Microsoft JET Engine 中的SQL語法和
ANSI決大部分是一樣的，但是有一些還是保留了“微軟特色”，特別是象這一類的轉換和格式化語句，例如FORMAT就是
ANSI中沒有的。而象其它數據庫，諸如oracle也有各自的SQL語法擴展。在使用不同數據庫進行SQL查詢時要注意這一點。
    在GROUP BY 語句中還可以連接使用HAVING子句。該語句同GROUP BY的關系就如同WHERE子句同SELECT的關系類似，
WHERE子語句為SELECT所選擇的列設置條件，而HAVING子語句是給由GROUP BY創建的組設置條件。例如如果將上面的范例
中的astr改變為如下的字符串：

astr = "SELECT SUM(db2.成績)AS rTotal, FORMAT((AVG(db2.成績)),'###.#') " & _
            "AS rAVG, (db2.學生) AS Student FROM db2 GROUP BY db2.學生" & _
            " HAVING (AVG(db2.成績))>=90"

則在List中就將只會列出平均成績大于90分的學生的成績和名字。

范例五：獲得分數高于總平均分數的學生及科目
    我們仍然使用上面建立的db2表。建立新工程，加入DAO定義庫。在Form1中加入一個ListBox控件和一個Label控件
然后在Form_load中加入以下代碼：

Private Sub Form_Load()
    Dim rsTemp As Recordset
    Dim dbTemp As Database
    Dim astr As String
   
    Set dbTemp = DBEngine(0).OpenDatabase("c:\db4.mdb", dbOpenSnapshot)
   
    astr = "SELECT FORMAT(AVG(db2.成績),'###.#') AS tAVG FROM db2"
    Set rsTemp = dbTemp.OpenRecordset(astr)
    Label1.Caption = "總平均成績：" & rsTemp![tAVG]
    rsTemp.Close
    Set rsTemp = Nothing
   
    astr = "SELECT db2.成績, db2.學生,db2.科目 FROM db2 WHERE db2.成績 > " & _
            "(SELECT AVG(db2.成績) FROM db2) GROUP BY db2.學生,db2.成績,db2.科目 " & _
            "ORDER BY db2.學生"
    Set rsTemp = dbTemp.OpenRecordset(astr)
    If rsTemp.RecordCount > 0 Then
        rsTemp.MoveFirst
        Do Until rsTemp.EOF
            List1.AddItem rsTemp![學生] & "    " & rsTemp![科目] & "    " & rsTemp![成績]
            rsTemp.MoveNext
        Loop
    End If
End Sub
    運行程序，在Lable1中列出總平均分數。在List1中列出了學生姓名、獲得高于平均分數的科目以及科目成績。
在上面的查詢中，我們使用了一個嵌套查詢，首先在子查詢中獲得所有科目總的平均分數，然后在查詢中查詢成績字段
值大于平均分數的紀錄。

四、TRANSFORM...PIVOT... 語句
    這是Microsoft JET Engine 3.5以上版本所特有的SQL查詢語句，該語句的特點是可以建立一個交叉表格式的查詢，
一個交叉表同電子表相類似。該語句可以將表中的某些數據作為行，某些數據作為列建立交叉表。該語句的語法如下：

 TRANSFORM condition [select opreation] PIVOT column

    其中condition是在交叉表中要顯示的數據，select opreation 是一個SELECT...FROM... 查詢，該查詢形成交叉表的
航信息，PIVOT recordset中column為表中的一個字段，PIVOT子句使用該字段形成交叉表的列。

范例六：建立學生成績表
    我們還是使用上面已經建立的db4.mdb中的db2表。首先建立一個新的工程，然后在Form1中加入一個DataGrid控件，然后
向工程中加入一個DataEnvironment，在Connection1上點擊鼠標右鍵，在菜單中選擇 properties... ,在屬性窗口的 提供者
頁面中選擇 Microsoft JET 4.0 OLE DB Provider ，在 連接 頁面的數據庫名稱輸入框中輸入 c:\db4.mdb ，然后點擊 測試
連接 按鈕，如果正常，點擊確定退出。再在Connection1上點擊鼠標右鍵，在菜單中選擇 Add command 建立一個名為Command1
的命令，點擊Command1右鍵菜單，選擇 Properties... 項，然后在Command1屬性窗口的General頁面中選擇 SQL Statement，
在SQL查詢語句輸入框中輸入下面的查詢：

 Transform SUM(db2.成績)AS iRes SELECT db2.學生 FROM db2 GROUP BY db2.學生 Pivot db2.科目

    注意文本框回自動換行，不要輸入回車。然后點擊確定按鈕。
    回到Form1，將DataGrid1的DataSource設置為DataEnvironment1，將DataMember設置為Command1，然后運行程序，可以看
到在DataGrid1中以表的形式列出了學生成績，以學生為行，以成績為列。運行后得到的表格效果如下：

 學生 數學 英語 語文
 李永 90 94 93
 王為 87 98 91
 張嚴 96.5 80.5 86.5

    再回到DataEnvironment界面，雙擊Command1就可以看到查詢建立的數據列，在上面的查詢共建立了4個數據列，其中三個
分別是科目分類，列中的數據為科目成績，第一列為學生的名字，列中的數據為學生的名字。
    上面的查詢中還使用了SUM子語句，這時因為對于GROUP BY來說，沒有包含在統計函數內的列都要包含在GROUP BY中，如果
將字段db2.成績包含在 GROUP BY 子語句中，就會使最終結果出現9行而不是3行。由于每個學生的每科成績只有一個，所以可以
使用SUM函數將字段db2.成績排除在GROUP BY外面。
    在下一章內，將向大家介紹SQL語言中的數據庫結構定義部分以及數據操縱部分。

www.applevb.com