博客

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

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

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

创建查询的方法如下:

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

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

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

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

SELECT
    WorkingHours.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
FROM
    WorkingHours
WHERE
    (((Year([WorkDate])) = 2018));
1
2
3
4
5
6
7
8
9
10
11
12

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

选取 V1 的所有字段：

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

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

对应的 SQL 语句如下：

SELECT
    V1.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
    V1
GROUP BY
    V1.EmployeeID;
1
2
3
4
5
6
7
8
9
10
11
12

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

SELECT
    V1.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 BY
    V1.EmployeeID;
1
2
3
4
5
6
7
8
9
10
11
12

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

SELECT
    V1.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
    (
        SELECT
            WorkingHours.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
        FROM
            WorkingHours
        WHERE
            (((Year([WorkDate])) = 2018))
    ) as V1
GROUP BY
    V1.EmployeeID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

这样就实现了一个完整的查询，比手工编写方便太多了。如果有更多层的子查询，都可以用同样的方法来实现。