使用聯集查詢合併多個查詢產生單一結果

在前一節使用的 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 範例資料庫中的「產品交易」聯集查詢，來了解其中原理。 請開啟範例資料庫，然後在資料工作表檢視中開啟 [產品交易] 查詢。 最後十筆記錄應該會類似下列輸出結果：

我們現在假設您想要將 [數量] 欄位分成兩個：[買入] 和 [賣出]。 同時，也假設您想要在沒有值的欄位中放入一個 0 的定值。 這個聯集查詢的 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; 

如果您切換成資料工作表檢視，您會看到現在顯示的最後十筆資料如下：

繼續使用此範例，如果想要讓顯示為 0 的欄位保持空白的話，該怎麼做呢？ 您可以新增 Null 關鍵字來修改 SQL，以空白取代顯示的 0，就像下面這樣：

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

這個小技巧可以讓它永遠都為 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 資料庫中的產品識別碼=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，加入產品識別碼= 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 範例資料庫中的這兩筆記錄：

如您所見，先前提到的聯集查詢中並不包含 [訂購單識別碼] 欄位，因此這兩個欄位無法視為兩筆相異的記錄。

如果您想要包含所有記錄，請將 SQL 中使用的 UNION 改為 UNION ALL。 在大多數情況下，這會對結果的排序造成影響，因此我們建議您再加入一個 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 會執行九筆記錄的聯集，並包含固定的欄位值 <All> 和 "*"。

   由於此聯集子句並未包含 UNION ALL，因此 Access 只會傳回相異記錄，這表示每個城市只會傳回一次，且會傳回固定的相同值。

5. 現在，您擁有了一個只會顯示各城市名稱一次的聯集查詢，且其中包含一個能夠選取所有城市的選項，您可以將此查詢做為表單上某個下拉式方塊的記錄來源。 如果使用此特定範例做為模型，您可以在表單上建立一個下拉式方塊控制項，將此查詢設為該控制項的記錄來源，並將 [篩選] 資料行的 [欄寬] 屬性設為 0 (零) 以在視覺上將它隱藏，然後再將 [繫結資料行] 屬性設為 1，以做為第二個資料行的索引。 您可以再將下列程式碼加入表單本身的 [篩選] 屬性中，以此使用下拉式方塊控制項裡選取的值來啟用表單篩選：

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

   這樣一來，表單的使用者就能篩選表單記錄以獲得某個特定的城市名稱，或是選取 <全部> 來列出所有城市的所有記錄。

頁面頂端