SQL Server: Mệnh đề WITH
Mệnh đề SQL WITH được Oracle giới thiệu trong cơ sở dữ liệu Oracle 9i phiên bản 2. Mệnh đề SQL WITH cho phép bạn đặt tên cho khối truy vấn phụ (một quy trình còn được gọi là tái cấu trúc truy vấn phụ), có thể được tham chiếu ở một số nơi trong truy vấn SQL chính.
- Mệnh đề được sử dụng để xác định một quan hệ tạm thời sao cho đầu ra của quan hệ tạm thời này có sẵn và được sử dụng bởi truy vấn được liên kết với mệnh đề WITH.
- Các truy vấn có mệnh đề WITH được liên kết cũng có thể được viết bằng cách sử dụng các truy vấn con lồng nhau nhưng làm như vậy sẽ làm tăng thêm độ phức tạp để đọc/gỡ lỗi truy vấn SQL.
- Mệnh đề WITH không được hỗ trợ bởi tất cả hệ thống cơ sở dữ liệu.
- Tên được gán cho truy vấn phụ được coi như thể nó là một bảng hoặc một dạng xem nội tuyến (inline view).
Cú pháp:
WITH temporaryTable (averageValue) as (
SELECT avg(Attr1)
FROM Table
)
SELECT Attr1
FROM Table, temporaryTable
WHERE Table.Attr1 > temporaryTable.averageValue;
Trong cú pháp trên, mệnh đề WITH được sử dụng để định nghĩa một quan hệ tạm thời temporaryTable là bảng chỉ có 1 thuộc tính averageValue. averageValue lưu giá trị trung bình của cột Attr1 của Table. Câu lệnh SELECT theo sau mệnh đề WITH sẽ chỉ tạo ra các bộ giá trị mà giá trị của Attr1 trong bảng Table lớn hơn giá trị trung bình thu được từ mệnh đề WITH.
Lưu ý: Khi một truy vấn với mệnh đề WITH được thực thi, trước tiên truy vấn được đề cập trong mệnh đề được đánh giá và kết quả của đánh giá này được lưu trữ trong một quan hệ tạm thời. Sau đó, truy vấn chính được liên kết với mệnh đề WITH cuối cùng được thực thi sẽ sử dụng quan hệ tạm thời được tạo ra.
Ví dụ 1: Tìm tất cả các nhân viên có mức lương lớn hơn mức lương trung bình của tất cả các nhân viên.
Ta có bảng Employee như sau:
| EmployeeID | Name | Salary |
|---|---|---|
| 100011 | Smith | 50000 |
| 100022 | Bill | 94000 |
| 100027 | Sam | 70550 |
| 100845 | Walden | 80000 |
| 115585 | Erik | 60000 |
| 1100070 | Kate | 69000 |
Tiến hành truy vấn:
WITH temporaryTable(averageValue) as (
SELECT avg(Salary)
FROM Employee
)
SELECT EmployeeID, Name, Salary
FROM Employee, temporaryTable
WHERE Employee.Salary > temporaryTable.averageValue;
Kết quả:
| EmployeeID | Name | Salary |
|---|---|---|
| 100022 | Bill | 94000 |
| 100845 | Walden | 80000 |
Giải thích: Mức lương trung bình của tất cả các nhân viên là 70591. Do đó, tất cả các nhân viên có mức lương cao hơn mức trung bình đều thỏa mãn.
Ví dụ 2: Tìm tất cả các hãng hàng không mà tổng mức lương của tất cả các phi công trong hãng hàng không đó cao hơn mức trung bình của tổng mức lương của tất cả các phi công trong cơ sở dữ liệu.
Ta có bảng Pilot như sau:
| EmployeeID | Airline | Name | Salary |
|---|---|---|---|
| 70007 | Airbus 380 | Kim | 60000 |
| 70002 | Boeing | Laura | 20000 |
| 10027 | Airbus 380 | Will | 80050 |
| 10778 | Airbus 380 | Warren | 80780 |
| 115585 | Boeing | Smith | 25000 |
| 114070 | Airbus 380 | Katy | 78000 |
Tiến hành truy vấn:
WITH totalSalary(Airline, total) as (
SELECT Airline, sum(Salary)
FROM Pilot
GROUP BY Airline
), airlineAverage(avgSalary) as (
SELECT avg(Salary) FROM Pilot
)
SELECT Airline FROM totalSalary, airlineAverage
WHERE totalSalary.total > airlineAverage.avgSalary;
Kết quả:
| Airline |
|---|
| Airbus 380 |
Giải thích: Tổng lương của tất cả các phi công của Airbus 380 = 298.830 và của Boeing = 45000. Lương trung bình của tất cả các phi công trong bảng Pilot = 57305. Vì chỉ có tổng lương của tất cả các phi công của Airbus 380 là lớn hơn mức lương trung bình, vì vậy Airbus 380 là kết quả đầu ra.
Điểm quan trọng:
- Mệnh đề SQL WITH phù hợp khi được sử dụng với các câu lệnh SQL phức tạp hơn là các câu lệnh đơn giản
- Nó cũng cho phép bạn chia nhỏ các truy vấn SQL phức tạp thành các truy vấn nhỏ hơn, giúp dễ dàng gỡ lỗi và xử lý các truy vấn phức tạp.
- Mệnh đề SQL WITH về cơ bản được hiểu là một truy vấn con có tên.