2022年SQL查询语言基本教程 3.pdf
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 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 6 页 - - - - - - - - - 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语法扩展。在使用不同数据名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 6 页 - - - - - - - - - 库进行 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 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 6 页 - - - - - - - - - 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. 查询，该查询形成交叉表的名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 6 页 - - - - - - - - - 航信息， 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 个数据列，其中三个名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 6 页 - - - - - - - - - 分别是科目分类， 列中的数据为科目成绩，第一列为学生的名字，列中的数据为学生的名字。上面的查询中还使用了SUM 子语句，这时因为对于GROUP BY 来说，没有包含在统计函数内的列都要包含在GROUP BY 中，如果将字段 db2. 成绩包含在 GROUP BY 子语句中，就会使最终结果出现9 行而不是3 行。由于每个学生的每科成绩只有一个，所以可以使用 SUM 函数将字段db2. 成绩排除在GROUP BY 外面。在下一章内，将向大家介绍SQL语言中的数据库结构定义部分以及数据操纵部分名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 6 页 - - - - - - - - -
