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