使用联合查询将多个查询合并到单个结果中

在上一节使用 Northwind 数据库的示例中，仅合并来自两个表的数据。 但是，你可以在联合查询中轻松合并三个或更多表格。 例如，基于前面的示例，你可能还希望在查询输出中包含员工的姓名。 你可以通过添加第三个查询，并使用其他 UNION 关键字与之前的 SQL 语句合并来完成该任务，如下所示：

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

当在数据表视图中查看结果时，所有员工都将以示例公司名称列出，这可能不是非常有用。 如果希望该字段指示某人是内部员工、来自供应商还是来自客户，则可以包含某固定值来代替公司名称。 SQL 如下所示：

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

结果在数据表视图中的显示方式如下。 Access 显示以下五个示例记录：

上面的查询可以进一步减少，因为 Access 只从联合查询的第一个查询中读取输出字段的名称。 此处你看到我们已经从第二个和第三个查询部分删除了输出：

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

在 Access 联合查询中，仅允许一次排序，但可以单独筛选每个查询。 基于前一节的联合查询，以下是通过添加 WHERE 子句来筛选每个查询的示例。

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

切换到数据表视图，会看到以下类似结果：

如果要联合的查询有很大不同，则可能会遇到输出字段必须合并不同类型数据的情况。 如果是这种情况，联合查询通常会将结果作为文本数据类型返回，因为该数据类型可以同时包含文本和数字。

为了解其工作原理，我们将使用 Northwind 示例数据库中的产品事务联合查询。 打开该示例数据库，然后在数据表视图中打开产品事务查询。 最后十条记录应该与以下输出类似：

假设你希望将“数量”字段一分为二 - 购买和销售。 假设你还想要为没有值的字段设置固定的零值。 以下是 SQL 查找此联合查询的方式：

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

如果切换到数据表视图，你将看到最后十条记录现在显示如下：

继续此示例，如果想要带零的字段为空？ 可以通过添加 Null 关键字来修改 SQL 以不显示任何内容而不是显示零，如下所示：

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

但是，如你所见切换到数据表视图，现在有一个意外的结果。 在“购买”列中，每个字段都被清除了：

发生这种情况是因为 Access 会决定第一个查询中字段的数据类型。 在此示例中，Null 不是数字。

那么，如果尝试为字段的空白值插入空字符串，会发生什么呢？ 此尝试的 SQL 可能如下所示：

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

当切换到数据表视图时，会看到 Access 检索“购买”值，但它会将值转换为文本。 可以看出这些是文本值，因为它们在数据表视图中是左对齐的。 第一个查询中的空字符串不是数字，这就是看到这些结果的原因。 此外还会发现，由于购买记录包含空字符串，“销售”值也会转换为文本。

那么如何解决此难题？

一个解决方案是强制查询期望字段值为数字。 这可以通过以下表达式来实现：

IIf(False, 0, Null)

要检查的条件，False 永远不会为 True，因此表达式将始终返回 Null，但 Access 仍会计算两个输出选项并决定输出为数字或 Null。

以下是在我们的工作示例中使用此表达式的方法：

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

请注意，不需要修改第二个查询。

如果切换到数据表视图，现在将看到我们希望的结果：

实现相同效果的另一种方法是使用另一个查询在联合查询的前面预置这些查询：

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

对于每个字段，Access 会返回你定义的数据类型的固定值。 当然，你不希望此查询的输出干扰结果，所以避免此情况的技巧是包含一个结果为 False 的 WHERE 子句：

WHERE False

这是一个小技巧，因为这始终是假的，然后查询不会返回任何内容。 合并此语句和现有的 SQL，我们得到一个完整的语句，如下所示：

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

联合查询的一个特殊情况是，合并一组记录和一个包含一个或多个字段总和的记录。

以下是可以在 Northwind 示例数据库中创建的另一个示例，以说明如何在联合查询中获取总数。

1. 使用以下 SQL 语法创建新的简单查询来查看啤酒的购买情况（在 Northwind 数据库中，产品 ID 为 34）：

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

2. 切换到数据表视图，应该看到四个购买：

   接收日期	数量
   2006/1/22	100
   2006/1/22	60
   2006/4/4	50
   2006/4/5	300

3. 要获得总数，请使用以下 SQL 创建简单的聚合查询：

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. 切换到数据表视图，应该只看到一条记录：

   接收日期的最大值	数量总和
   2006/4/5	510

5. 将这两个查询合并到一个联合查询中，以将具有总数量的记录附加到采购记录：

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

6. 切换到数据表视图，应该看到四个购买，分别显示各自的总和后跟数量总计记录。

   接收日期	数量
   2006/1/22	60
   2006/1/22	100
   2006/4/4	50
   2006/4/5	300
   2006/4/5	510

这包含将总计添加到联合查询中的基础知识。 你可能还希望在两个查询中都包含固定值，如“详细信息”和“总计”，以便将总计记录与其他记录进行直观分离。 你可以参阅在合并查询中合并三个或更多表或查询部分了解如何使用固定值。

默认情况下，Access 中的联合查询只包含不同的记录。 但如果想要包含所有记录呢？ 此处另一个示例可能很有用。

在上一部分，我们向你展示了如何在联合查询中创建总计。 修改该联合查询 SQL 以包含产品 ID = 48：

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

切换到数据表视图，应该看到一个稍有误导性的结果：

当然，一条记录不会返回总数的两倍。

你看到此结果是因为一天中卖出了两份同样数量的巧克力，如“采购订单详细信息”表中所记录。 以下是 Northwind 示例数据库中显示两条记录的简单选择查询结果：

在之前提到的联合查询中，可以看到未包含采购订单 ID 字段，并且这两个字段不构成两个不同的记录。

如果想要包含所有记录，请在 SQL 中使用 UNION ALL 而不是 UNION。 这很可能会对结果的排序产生影响，因此可能还需要包含 ORDER BY 子句以确定排序顺序。 以下是基于上一个示例修改的 SQL 语句：

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

切换到数据表视图，应该看到所有详细信息以及总计（最后一条记录）：

联合查询的常见用法是作为表单上组合框控件的记录源。 可以使用该组合框来选择一个值，以筛选表单的记录。 例如，按员工的城市筛选员工记录。

要了解其工作原理，可以在 Northwind 示例数据库中创建另一个示例来说明这种情况。

1. 使用以下 SQL 语法创建简单的选择查询：

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;

2. 切换到数据表视图，应该看到以下结果：

   城市	筛选器
   西雅图	西雅图
   贝尔维尤	贝尔维尤
   雷德蒙德	雷德蒙德
   柯克兰	柯克兰
   西雅图	西雅图
   雷德蒙德	雷德蒙德
   西雅图	西雅图
   雷德蒙德	雷德蒙德
   西雅图	西雅图

3. 光从这些结果你可能看不到太多价值。 你可以展开查询并通过使用以下 SQL 将其转换为联合查询：

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. 切换到数据表视图，应该看到以下结果：

   城市	筛选器
   <所有>	*
   贝尔维尤	贝尔维尤
   柯克兰	柯克兰
   雷德蒙德	雷德蒙德
   西雅图	西雅图

   Access 会对之前显示的九条记录执行联合（固定字段值为 <全部> 和“*”）。

   由于此联合子句不包含 UNION ALL，因此 Access 只返回不同的记录，这意味着每个城市只返回一次固定的相同值。

5. 现在，你已拥有一个城市名称仅显示一次的完整联合查询，以及一个可以有效选择所有城市的选项，你可以将此查询用作表单上组合框的记录源。 使用此特定示例作为模型，可以在表单上创建组合框控件，将此查询设置为其记录源，将“筛选”列的“列宽”属性设置为 0（零）以在视觉上隐藏它，然后将“绑定列”属性设置为 1 以指示第二列的索引。 在表单自身的“筛选器”属性中，可以添加如下代码，以使用组合框控件中选定内容的值激活表单筛选器​​：

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True

   然后，表单用户可以将表单记录筛选为特定的城市名称，或者选择 <全部> 以列出所有城市的所有记录。

返回页首