Dùng một truy vấn hợp để kết hợp nhiều truy vấn vào một kết quả đơn

Trong ví dụ ở mục trước đó sử dụng cơ sở dữ liệu Northwind, chỉ kết hợp được dữ liệu từ hai bảng. Tuy nhiên, bạn có thể kết hợp ba hoặc nhiều bảng rất dễ dàng trong một truy vấn hợp. Ví dụ: dựng trên ví dụ trước, bạn có thể cũng muốn kèm theo tên của nhân viên trong đầu ra truy vấn. Bạn có thể thực hiện tác vụ đó bằng cách thêm một truy vấn thứ ba và kết hợp với câu lệnh SQL trước đó với từ khóa UNION bổ sung như thế này:

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];

Khi bạn xem kết quả ở dạng xem biểu dữ liệu, tất cả nhân viên sẽ được liệt kê cùng tên công ty mẫu, vốn có thể không phải rất hữu ích. Nếu bạn muốn trường đó cho biết một người là nhân viên nội bộ, từ nhà cung cấp hoặc từ khách hàng, bạn có thể đưa vào giá trị cố định thay cho tên công ty. Đây là giao diện của 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];

Đây là giao diện kết quả sẽ xuất hiện ở dạng xem biểu dữ liệu. Access sẽ hiển thị năm bản ghi ví dụ này:

Truy vấn bên trên có thể giảm được thậm chí nhiều hơn vì Access chỉ đọc tên của trường đầu ra từ truy vấn đầu tiên trong truy vấn hợp. Tại đây, bạn thấy chúng ta đã loại bỏ đầu ra khỏi các mục truy vấn thứ hai và thứ ba:

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];

Trong truy vấn hợp Access, chỉ cho phép xếp thứ tự một lần nhưng từng truy vấn có thể được lọc riêng biệt. Việc dựng trên truy vấn hợp của mục trước, dưới đây là ví dụ về vị trí chúng ta đã lọc từng truy vấn bằng cách thêm mệnh đề 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];

Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy kết quả có dạng như sau:

Nếu các truy vấn cần kết hợp đều rất khác nhau, bạn có thể gặp phải một tình huống là trường đầu ra phải kết hợp dữ liệu của các loại dữ liệu khác nhau. Nếu vậy, truy vấn hợp hầu như thường sẽ trả về kết quả dưới dạng loại dữ liệu văn bản do loại dữ liệu có thể lưu giữ cả văn bản và số.

Để tìm hiểu cách truy vấn này hoạt động, chúng ta sẽ sử dụng truy vấn hợp Giao dịch sản phẩm trong cơ sở dữ liệu mẫu Northwind. Mở cơ sở dữ liệu mẫu đó, rồi mở truy vấn Giao dịch sản phẩm ở dạng xem biểu dữ liệu. Mười bản ghi cuối cùng phải tương tự như đầu ra này:

Hãy giả định rằng bạn muốn trường Số lượng chia tách vào hai - Mua và Bán. Cũng hãy giả định rằng bạn muốn có giá trị số không cố định cho trường không có giá trị. Đây là giao diện của SQL đối với truy vấn hợp này:

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; 

Nếu bạn chuyển đổi sang dạng xem biểu dữ liệu, bạn sẽ thấy mười bản ghi cuối cùng giờ đây được hiển thị giống như sau:

Tiếp tục ví dụ này, điều gì sẽ xảy ra nếu bạn muốn các trường có số không là trống? Bạn có thể sửa đổi SQL để không hiển thị gì thay vì số không bằng cách thêm từ khóa Null giống như sau:

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;

Tuy nhiên, khi bạn có thể quan sát việc chuyển đổi sang dạng xem biểu dữ liệu, lúc này bạn đã có một kết quả không mong muốn. Trong cột Mua, mỗi trường đều bị xóa:

Lý do mà điều này xảy ra là vì Access xác định loại dữ liệu của các trường từ truy vấn đầu tiên. Trong ví dụ này, Null không phải là một số.

Vì vậy, điều gì xảy ra nếu bạn tìm cách và chèn một chuỗi trống cho giá trị của các trường trống? SQL đối với nỗ lực này có thể có dạng như sau:

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;

Khi bạn chuyển đổi sang dạng xem biểu dữ liệu, bạn sẽ thấy rằng Access truy xuất các giá trị Mua nhưng đã chuyển đổi các giá trị này thành văn bản. Bạn có thể biết đây là giá trị văn bản vì được căn trái ở dạng xem biểu dữ liệu. Chuỗi trống trong truy vấn đầu tiên không phải là số là vì bạn thấy các kết quả này. Bạn cũng sẽ nhận thấy rằng các giá trị Bán cũng được chuyển đổi thành văn bản vì các bản ghi mua có chứa chuỗi trống.

Vật thì bạn giải quyết vấn đề này như thế nào?

Giải pháp là buộc truy vấn có giá trị trường là một số. Điều đó có thể được thực hiện bằng biểu thức:

IIf(False, 0, Null)

Điều kiện để kiểm tra, False, sẽ không bao giờ là True, do đó biểu thức sẽ luôn trả về Null nhưng Access vẫn đánh giá cả hai tùy chọn đầu ra và quyết định đầu ra là số hoặc Null.

Đây là cách chúng ta có thể sử dụng biểu thức này trong ví dụ hoạt động của mình:

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;

Lưu ý rằng không cần thiết sửa đổi truy vấn thứ hai.

Nếu bạn chuyển đổi sang dạng xem biểu dữ liệu, lúc này bạn sẽ thấy kết quả mà chúng ta mong muốn:

Phương pháp thay thế để đạt được cùng một kết quả là thêm các truy vấn trong truy vấn hợp với một truy vấn khác:

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

Đối với từng trường, Access trả về các giá trị cố định của loại dữ liệu bạn xác định. Tất nhiên, bạn không muốn đầu ra của truy vấn này gây cản trở đến kết quả, do đó bí kíp để tránh điều này là thêm vào mệnh đề WHERE thành False:

WHERE False

Đây là một bí kíp nhỏ vì kết quả luôn là false và sau đó truy vấn không trả về bất kỳ giá trị nào. Kết hợp câu lệnh này với SQL hiện có và chúng ta có được câu lệnh được hoàn thành như sau:

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;

Một trường hợp đặc biệt về truy vấn hợp là kết hợp một bộ các bản ghi có một bản ghi chứa tổng của một hoặc nhiều trường.

Đây là một ví dụ khác mà bạn có thể tạo trong cơ sở dữ liệu mẫu Northwind để minh họa cách nhận tổng trong truy vấn hợp.

1. Tạo một truy vấn đơn giản mới để xem phần mua bia (ID Sản phẩm=34 trong cơ sở dữ liệu Northwind) bằng cú pháp SQL sau đây:

   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. Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy bốn sản phẩm mua:

   Ngày nhận	Số lượng
   22/01/2006	100
   22/01/2006	60
   04/04/2006	50
   05/04/2006	300

3. Để có được tổng, tạo một truy vấn tổng hợp đơn giản bằng SQL sau đây:

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

4. Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ chỉ thấy một bản ghi:

   Ngày nhận_tối_đa	Tổng_số_lượng
   05/04/2006	510

5. Kết hợp hai truy vấn này vào một truy vấn hợp để chắp thêm bản ghi có số lượng tổng vào các bản ghi mua:

   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. Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy bốn sản phẩm mua có tổng của từng sản phẩm kèm theo là một bản ghi tính tổng số lượng:

   Ngày nhận	Số lượng
   22/01/2006	60
   22/01/2006	100
   04/04/2006	50
   05/04/2006	300
   05/04/2006	510

Bài viết đó đề cập đến những thông tin cơ bản về cách thêm tổng vào một truy vấn hợp. Bạn có thể cũng muốn đưa vào các giá trị cố định trong cả hai truy vấn chẳng hạn như “Chi tiết” và “Tổng” để phân tách trực quan bản ghi tổng từ các bản ghi khác. Bạn có thể xem lại các giá trị cố định đang sử dụng trong mục Kết hợp ba hoặc nhiều bảng hoặc truy vấn trong truy vấn hợp.

Các truy vấn hợp trong Access theo mặc định chỉ bao gồm các bản ghi riêng biệt. Nhưng điều gì sẽ xảy ra nếu bạn muốn bao gồm tất cả bản ghi? Một ví dụ khác có thể hữu ích ở đây.

Trong mục trước đó, chúng tôi đã chỉ cho bạn cách tạo tổng trong truy vấn hợp. Sửa đổi truy vấn hợp SQL đó để có ID Sản phẩm= 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];

Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy kết quả hơi sai khác một chút:

Dĩ nhiên, một bản ghi sẽ không trả về hai lần số lượng tổng.

Lý do bạn thấy kết quả này là vì trong một ngày cùng một lượng sôcôla đã được bán hai lần - như được ghi lại trong bảng Chi tiết đơn đặt hàng. Đây là kết quả truy vấn chọn đơn giản hiển thị cả hai bản ghi trong cơ sở dữ liệu mẫu Northwind:

Trong truy vấn hợp được ghi chú trước, bạn có thể thấy rằng trường ID Đơn hàng không được đưa vào và hai trường không tạo thành hai bản ghi riêng biệt.

Nếu bạn muốn đưa tất cả bản ghi vào, hãy sử dụng UNION ALL thay vì UNION trong SQL của mình. Điều này chủ yếu có thể sẽ gây ảnh hưởng về việc sắp xếp kết quả, vì vậy bạn có thể cũng muốn đưa vào mệnh đề ORDER BY để xác định thứ tự sắp xếp. Đây là cách dựng SQL đã sửa đổi từ ví dụ trước đó:

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];

Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy tất cả các chi tiết ngoài tổng dưới dạng bản ghi cuối cùng:

Mức sử dụng phổ biến về truy vấn hợp đóng vai trò là nguồn bản ghi cho điều khiển hộp tổ hợp trên biểu mẫu. Bạn có thể sử dụng hộp tổ hợp đó để chọn một giá trị cần lọc các bản ghi của biểu mẫu. Ví dụ: lọc bản ghi nhân viên theo thành phố.

Để xem cách hoạt động, đây là một ví dụ khác mà bạn có thể tạo trong cơ sở dữ liệu mẫu Northwind để minh họa kịch bản này.

1. Tạo truy vấn chọn đơn giản bằng cú pháp SQL này:

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

2. Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy kết quả như sau:

   Thành phố	Bộ lọc
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. Đang xem các kết quả mà bạn có thể không thấy nhiều giá trị. Bung rộng truy vấn toàn bộ và biến đổi thành truy vấn hợp bằng cách sử dụng SQL sau đây:

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

4. Chuyển đổi sang dạng xem biểu dữ liệu và bạn sẽ thấy kết quả như sau:

   Thành phố	Bộ lọc
   <Tất cả>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access sẽ thực hiện kết hợp chín bản ghi, được hiển thị trước đó, có các giá trị cố định là <Tất cả> và "*".

   Vì mệnh đề kết hợp này không chứa UNION ALL, Access sẽ chỉ trả về các bản ghi riêng biệt có nghĩa là từng thành phố được trả về chỉ một lần với các giá trị cố định giống nhau.

5. Lúc này, bạn có một truy vấn hợp đã hoàn thành hiển thị mỗi tên thành phố chỉ một lần, cùng với một tùy chọn sẽ chọn tất cả các thành phố một cách hiệu quả, bạn có thể sử dụng truy vấn này làm nguồn bản ghi cho hộp tổ hợp trên biểu mẫu. Sử dụng ví dụ cụ thể này làm mô hình, bạn có thể tạo điều khiển hộp tổ hợp trên biểu mẫu, đặt truy vấn này làm nguồn bản ghi, đặt thuộc tính Column Width của Cột bộ lọc thành 0 (số không) để ẩn một cách trực quan, rồi đặt thuộc tính Bound Column thành 1 để chỉ báo các chỉ mục của cột thứ hai. Trong thuộc tính Filter của bản thân biểu mẫu, bạn có thể thêm vào mã chẳng hạn như mã sau đây để kích hoạt bộ lọc biểu mẫu bằng giá trị của những gì đã được chọn trong điều khiển hộp tổ hợp:

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

   Sau đó, người dùng của biểu mẫu có thể lọc các bản ghi biểu mẫu thành tên thành phố cụ thể hoặc chọn <Tất cả> để liệt kê tất cả bản ghi cho tất cả thành phố.

Đầu Trang