查询中的计算应用技巧

数据库管理系统不仅具有查询数据的功能，更重要的是还具有计算数据的功能，譬如可以计算一个字段值的总和、平均值、最大值和最小值等。

常用的计算函数如表4-1所示。

表4-1　常用的函数

根据查询计算时是否直接应用字段值，分为预定义计算查询和自定义计算查询，预定义计算查询及对数据表中的现有字段进行统计计算，而自定义计算查询则是根据现有字段创建一个新的字段的查询。

1.预定义计算查询

例4-7　创建名为“例4-7计算机科学与技术1班总分前10名学生”的查询，显示“学号”“姓名”和“总分”信息。

操作步骤：

（1）打开查询设计视图，添加“学生表”“班级表”和“成绩表”3个表。

（2）在查询对象设计区域添加查询字段“学号”“姓名”“成绩”和“班级名称”，单击“查询工具设计”→“显示/隐藏”→“汇总”按钮，在查询对象设计区域表格中会添加一个“总计”行，默认为“Group By”（分组）。通过总计单元格下拉按钮，修改计算方式。

本例中，单击“成绩”字段下方“Group By”左侧下拉按钮，如图4-43所示，在“合计”字段上单击，即对成绩进行求和运算。

（3）设置排序字段：在“成绩”排序行选择“降序”。

（4）设置其他条件：在“班级名称”列，同步骤（2），在图4-43中选择“Where”，条件行输入“计算机科学与技术1班”；在显示复选框中单击，即不显示“班级名称”，如图4-44所示。

注：只作为条件的字段不显示，如本例中的“班级名称”不显示。

（5）单击“查询工具设计”→“查询设置”→，将“All”改为“10”，即返回前10位同学的总计成绩信息。

注：单击“All”后的下拉菜单，弹出如图4-45所示的列表框。数字表示指定返回记录个数，例如，“All”表示所有查询记录，“5”表示前5条记录……百分比表示显示记录占查询记录的百分比，如5％，在查询结果中显示总记录的5％……

（6）单击“运行”查看结果，计算列显示“成绩之合计”，如图4-46所示。

图4-43　设置计算字段

图4-44　设置条件等

图4-45　返回记录个数设置

图4-46　计算机科学与技术1班总分前10名学生信息

（7）更改计算字段标题，将“成绩之合计”改为“总分”[同例4-3步骤（13）]。

（8）单击“运行”，查看结果，如图4-47所示。

（9）单击“保存”按钮，保存查询为“例4-7计算机科学与技术1班总分前10名学生”。

图4-47　更改字段名后运行结果

例4-8　创建名为“例4-8学生成绩统计”的查询，统计不同课程、不同班级参加考试的考生人数、平均分（保留2位小数）、最高分、最低分等信息。

操作步骤：

（1）打开查询设计视图，添加“学生表”“成绩表”“课程表”和“班级表”4个表。

（2）选择查询所需的字段，单击工具栏上的“汇总”按钮，在查询对象设计区域表格中会添加一个“总计”行，默认为“Group By”，通过总计单元格下拉按钮，修改计算字段，并输入条件表达式，如图4-48所示。

注：“成绩”条件设置为“Is Not Null”，意为成绩不能为空，即不统计缺考的学生。

图4-48　设置总计选项

（3）单击“运行”按钮，显示查询生成的数据，如图4-49所示。

图4-49　部分查询结果

（4）计算字段以默认标题显示，用户可以根据需要在设计视图的属性表里更改计算字段标题，操作步骤同例4-3第（13）步。修改后的计算字段标题如图4-50所示。

图4-50　修改后的查询字段

（5）平均分格式设置：切换到“设计视图”，将光标置于“平均分”列，单击工具栏“属性表”按钮，弹出“属性表”对话框，如图4-51所示，设置“格式”为“固定”，“小数位数”为“2”（对小数点后第3位进行四舍五入）。(www.xing528.com)

图4-51　平均分格式设置

（6）单击“运行”按钮，观察查询结果如图4-52所示。

图4-52　格式化后的部分查询结果

（7）单击“保存”按钮，保存查询为“例4-8学生成绩统计”查询。

2.自定义计算查询

如前所述，查询的数据不是数据源直接字段，也不是数据源字段直接计算的结果，而是应用数据源中一个或几个字段产生一个新字段的查询，即为自定义查询。

例4-9　创建名称为“例4-9教师教龄”的查询，从教师表中计算教师的教龄。显示字段为“教师编号”“教师姓名”“参加工作时间”和“教龄”，教龄为当前年份减去参加工作年份。

操作步骤：

（1）打开查询设计视图，添加“教师表”。

（2）在查询设计区域添加“教师编号”“教师姓名”和“参加工作时间”。

（3）在字段行第4列“字段”单元格中输入“教龄：Year（Date（））-Year（[参加工作时间]）”，此表达式以冒号分为两部分，其中冒号前为查询新字段标题，冒号后为计算表达式，如图4-53所示。

图4-53　字段设置

（4）单击工具栏上的“运行”按钮，查询结果如图4-54所示。

（5）保存查询为“例4-9教师教龄”。

图4-54　教师教龄查询结果

注：

① 公式中所有的标点符号均为英文状态下的。

② 在“教龄：Year（Date（））-Year（[参加工作时间]）”中，“（）”为函数参数，“[]”为字段的引用。

例4-10　创建名为“例4-10学生平均分”的查询，统计所有学生的平均分，要求平均分保留2位小数（应用函数完成），以班级为单位，按班级升序和平均分降序排列。

操作步骤：

（1）打开查询设计视图，添加“学生表”“班级表”和“成绩表”。

（2）在查询设计区域添加“班级名称”“学号”和“姓名”。

（3）单击工具栏上的“汇总”按钮，在查询对象设计区域表格中会添加一个“总计”行，默认为“Group By”（分组），设置“班级名称”的“排序”方式为“升序”。

（4）在字段行第4列单元格中输入“平均值：Round（Avg（[成绩]），2）”，即用“Avg”求平均值函数算出成绩的平均分，再用“Round”函数对值设置小数位数；在总计列选择“Expression”表达式；设置平均值列为降序排序，如图4-55所示。

图4-55　设置查询字段

（5）在设计视图下，单击工具栏上的“运行”按钮，查询如果如图4-56所示。

图4-56　查询结果

（6）保存查询为“例4-10学生平均分”。

注：

① 计算字段时，要注意计算字段的计算范围，如果没有分组，则对所有数据源字段值进行汇总计算；如有一个分组字段，则对分组的数据源字段进行汇总计算；如果有两个分组字段，先按第一个分组，再按第二个分组，优先顺序是从左向右。本例中，先按字段“班级名称”分组，在每个班级内，再按字段“学号”分组，每位学生构成一组。由于同一学号内没有重复的姓名，所以“姓名”字段在此题中只起提示信息作用。

② 采用函数Round（） 计算数据，计算结果的小数位由函数Round（） 参数决定，这与采用“属性表”格式设置的小数位数显示的数据虽然一样，但两者有本质的区别，前者是计算字段本身数据的显示，后者只是改变了显示数据，但存储数据并没有改变，数据计算是以存储数为准。

例如，某位学生的平均分为：98.5555587，采用“属性表”设置格式为“固定”，小数位数为“2”，显示为“98.56”，但如果设置筛选条件“>=98.6”，不显示此条记录，因为“98.5555587>=98.6”不成立。但用Round（98.5555587，1）=98.6，满足设置的筛选条件“>=98.6”成立，就会显示此条记录。

总结：如果希望记录设置的条件和显示的字段值一致，采用Round函数。