使用 Access 查询设计器轻松构造复杂 SQL 语句

article/2024/11/14 12:34:49

Office 家族中，平时使用 MS Acess 的人非常少，如果从工作效率的角度来说，掌握了 Access，很多时候岂止是事半功倍啊！下面就介绍一种基于 Access 查询轻松构造 SQL 语句的方法。因为本文面向把 MS Office 作为办公工具的用户，熟练编写 SQL 语句的请忽略。

假设我们有如下的员工工时记录表：

目标是按固定月份（1~12 月）的格式统计员工的工时，格式如下：

创建查询的方法如下:

在【创建】选项卡中，点击 “查询设计” 按钮，新建一个查询，添加 WorkingHours 表：

因为需要按月统计，需要提取日期的月份，并且每个月作为单独的一列，使用表达式生成器来设置元月数据列为：

Access 不支持 case when，所以使用 IIF 函数，IIF函数比 case when 更加简洁。用同样的方法，为每个月份分别设置一列，此时的查询设计界面如下：

切换到 SQL 视图，对应的 SQL 语句如下：

SELECTWorkingHours.EmployeeID,IIf(Month([WorkDate]) = 1, [WorkHours], 0) AS Jan,IIf(Month([WorkDate]) = 2, [WorkHours], 0) AS Feb,IIf(Month([WorkDate]) = 3, [WorkHours], 0) AS Mar,IIf(Month([WorkDate]) = 4, [WorkHours], 0) AS Apr,IIf(Month([WorkDate]) = 5, [WorkHours], 0) AS May,IIf(Month([WorkDate]) = 6, [WorkHours], 0) AS Jun
FROMWorkingHours
WHERE(((Year([WorkDate])) = 2018));

将查询保存为 v1。基于刚才创建的查询 v1，新建一个查询:

选取 V1 的所有字段：

在【设计】选项卡中，点击 “汇总” 按钮，此时在表下面增加了一行：总计，界面变为：

因为我们需要统计各个月份的工时，所以将除了 EmployeeID 字段之外的其他字段更改为 “合计”，同时保持列名不变。

对应的 SQL 语句如下：

SELECTV1.EmployeeID,Sum(V1.Jan) AS Jan,Sum(V1.Feb) AS Feb,Sum(V1.Mar) AS Mar,Sum(V1.Apr) AS Apr,Sum(V1.May) AS May,Sum(V1.Jun) AS Jun
FROMV1
GROUP BYV1.EmployeeID;

基于查询的查询其实是一个子查询，我们在 from V1 前加上 () as, sql 语句变为下面的样子：

SELECTV1.EmployeeID,Sum(V1.Jan) AS Jan,Sum(V1.Feb) AS Feb,Sum(V1.Mar) AS Mar,Sum(V1.Apr) AS Apr,Sum(V1.May) AS May,Sum(V1.Jun) AS Jun
FROM() as V1
GROUP BYV1.EmployeeID;

然后把刚才查询 v1 的 sql 语句放在括号中:

SELECTV1.EmployeeID,Sum(V1.Jan) AS Jan,Sum(V1.Feb) AS Feb,Sum(V1.Mar) AS Mar,Sum(V1.Apr) AS Apr,Sum(V1.May) AS May,Sum(V1.Jun) AS Jun
FROM(SELECTWorkingHours.EmployeeID,IIf(Month([WorkDate]) = 1, [WorkHours], 0) AS Jan,IIf(Month([WorkDate]) = 2, [WorkHours], 0) AS Feb,IIf(Month([WorkDate]) = 3, [WorkHours], 0) AS Mar,IIf(Month([WorkDate]) = 4, [WorkHours], 0) AS Apr,IIf(Month([WorkDate]) = 5, [WorkHours], 0) AS May,IIf(Month([WorkDate]) = 6, [WorkHours], 0) AS JunFROMWorkingHoursWHERE(((Year([WorkDate])) = 2018))) as V1
GROUP BYV1.EmployeeID;