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