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

SQL查詢語言基本教程（2） (轉)[@more@]

查詢語言基本教程（2）

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

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

範例四：計算學生成績及總成績
  我們依然使用上一章使用的 c:4.mdb，在其中加入一個名字為 的表，表的結構以及資料如下：
 
欄位名 學生 科目 成績
 張嚴 語文 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
  L
  End If
End Sub
  在上面的程式碼中，我們利用GROUP BY將紀錄根據學生姓名分組，再建立了兩個統計欄位rTotal和rAvg並分別利用
統計函式SUM和AVG分別統計各個分組的總成績以及平均成績。要注意的是，在SELECT語句中出現的欄位，如果沒有包含
在統計函式內的話，都要包含在GROUP BY子句中。
  另外在上面的SQL查詢中我們還使用了FORMAT子句，這是SQL中的轉換和格式化語句中的一個，該語句的語法同VB中
的Format語句是一樣的，相似的語句還有FIX語句。需要注意的一點是，雖然在 JET Engine 中的SQL語法和
ANSI決大部分是一樣的，但是有一些還是保留了“特色”，特別是象這一類的轉換和格式化語句，例如FORMAT就是
ANSI中沒有的。而象其它資料庫，諸如也有各自的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中列出了學生姓名、獲得高於平均分數的科目以及科目成績。
在上面的查詢中，我們使用了一個巢狀查詢，首先在子查詢中獲得所有科目總的平均分數，然後在查詢中查詢成績欄位
值大於平均分數的紀錄。

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

 TRANSFOcondition [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 Provr ，在 連線 頁面的資料庫名稱輸入框中輸入 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的Data設定為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語言中的資料庫結構定義部分以及資料操縱部分。