CTE có thể được xem như một bảng chứa dữ liệu tạm thời tương tự như một bảng dẫn xuất (bảng dẫn xuất) tại chỗ nó không được lưu trữ dưới dạng một đối tượng và chỉ kéo dài trong suốt thời gian của câu truy vấn. Không giống như bảng dẫn xuất, CTE có thể tự tham chiếu tới bản thân của nó và có thể tham chiếu nhiều lần trong một câu truy vấn Show Việc lưu trữ dữ liệu một cách tạm thời sẽ giúp chúng ta chia nhỏ một lệnh truy vấn SQL phức tạp thành những phần nhỏ hơn để giải quyết. Sau đó chúng ta sẽ viết lại những phần này để giải quyết một vấn đề tổng thể có thể tốt hơn Cứ hình dung như khi bạn giải các phương trình toán học phức tạp, chúng ta thường sẽ chia nhỏ các bài toán thành nhiều phần nhỏ khác nhau để giải quyết. Và kết quả của mỗi phần nhỏ này sẽ được lưu vào một biến và biến này có thể được sử dụng lại ở các phần sau Nếu các bạn từng học qua lập trình, việc gọi tên bảng CTE cũng khá giống với việc gọi một hàm (hàm) được khai báo sẵn. Khác nhau ở chỗ là bảng CTE sẽ chỉ thực hiện một lần và lưu kết quả vào bộ nhớ tạm thời, còn chức năng trong các ngôn ngữ khác nhau sẽ thực hiện mỗi lần bạn sử dụng Mục đích của CTECTE được sinh ra để giúp giải quyết các vấn đề
Ưu điểm của CTECTE có nhiều ưu điểm như khả năng đọc dữ liệu được cải thiện một cách thiện chí và dễ dàng bảo trì các truy vấn phức tạp. Các truy vấn có thể được phân chia thành các khối nhỏ, đơn giản. Những khối này được sử dụng để xây dựng các CTE phức tạp hơn cho đến khi tập hợp kết quả cuối cùng được tạo ra Những lợi ích của lệnh VỚI CTEViệc mình ra lệnh VỚI là một trong những mệnh lệnh quan trọng nhất dựa trên những lợi ích mà nó mang lại cho chúng ta. Chúng ta sẽ đi phân tích từng lợi ích một Tối ưu hóa bộ nhớ và tốc độNhư mình nói về phía trên, các bảng CTE có thể được sử dụng lại nhiều lần và ở nhiều nơi khác nhau. Chính đặc tính này sẽ giúp chúng ta quản lý tốt hơn bộ nhớ và tốc độ xử lý Để hiểu rõ hơn vấn đề chúng ta cùng xem đoạn mã sau select a.*,b.total_sales from superstore.orders as a left join (select Order_ID, sum(Sales) as total_sales from superstore.orders group by Order_ID) as b -- lệnh lồng ghép on a.Order_ID = b.order_id Limit 100; Chúng ta có thể thấy rằng mình hoàn toàn có thể đưa ra kết quả tương tự nhưng không cần đến CTE. Nhưng nếu nhìn kỹ hơn chúng ta sẽ thấy rằng ở câu lệnh này hệ thống sẽ kết nối với máy chủ 2 lần để lấy dữ liệu. Một lần ở dòng thứ 2 và một lần còn lại ở dòng thứ 4 Việc tạo nhiều kết nối đến cùng một bảng dữ liệu sẽ tiêu tốn khá nhiều tài nguyên. Nếu chúng ta có một bảng dữ liệu lên đến hàng Terabyte (TB) và truy xuất chúng nhiều lần thì tổng lượng tài nguyên tiêu thụ sẽ rất lớn. Hơn nữa, việc kết nối với máy chủ nhiều lần cũng tiêu tốn khá nhiều thời gian Trong trường hợp này, việc sử dụng bảng CTE sẽ giúp lưu trữ toàn bộ dữ liệu cần thiết trong bộ nhớ tạm thời và sử dụng lại bất cứ lúc nào. Chúng giúp giảm thiểu rất nhiều tài nguyên và thời gian thực thi lệnh truy vấn Giảm độ phức tạp của truy vấnNhìn lại câu lệnh số 2. Ở phần LEFT JOIN chúng ta sử dụng một Sub-query để lấy dữ liệu. Trong các lệnh đơn giản, việc sử dụng truy vấn phụ sẽ khá thuận tiện. Nhưng trong những trường hợp phức tạp, chúng ta khó có thể kiểm soát được những tiểu truy vấn này Nhưng vấn đề sẽ dễ dàng hơn nhiều nếu chúng ta xử lý 10 bảng dữ liệu này và lưu và 10 bảng CTE khác nhau. Việc xử lý các bảng dữ liệu riêng có thể sẽ luôn dễ dàng hơn là chia lại một nơi. Đến cuối cùng chúng ta chỉ cần tham gia các bảng dữ liệu đã qua xử lý. Nếu có phát sinh lỗi, chúng ta cũng sẽ dễ dàng kiểm tra và chỉnh sửa hơn Dễ đọc và dễ hiểuViệc tách bài toán lớn thành những phần nhỏ sẽ giúp mình hay người khác dễ đọc và hiểu mục đích mình muốn làm. Trên thực tế, sẽ không ít lần bạn được yêu cầu đọc mã của người khác hoặc mã đọc của người khác của bạn Sử dụng CTE sẽ giúp chúng ta dễ dàng hơn trong việc truyền tải mục đích của mình cũng như logic của cả câu lệnh. Thông qua việc đặt tên bảng và ghi chú cho từng bảng, chúng ta sẽ giúp người khác hiểu ý nghĩa của nó dễ dàng hơn. Ngoài ra chỉ là để chính bạn đọc mã của mình sau 2-3 năm Tăng tính linh hoạt mà truy vấn phụ không cóKhông phải tất cả các trường hợp truy vấn phụ đều có thể thay thế bằng. Sẽ có nhiều trường hợp mà bạn buộc phải sử dụng with để xử lý vì truy vấn phụ không hỗ trợ điều này. Một trong những trường hợp này là khi bạn muốn xử lý dữ liệu trùng lặp (duplicate) phát sinh do quá trình tham gia các bảng Trong trường hợp hợp lệ trên, chúng ta có thể cần đến hàm row_number() trong MySQL để chọn ra giá trị muốn giữ lại. Những công việc này gần như rất khó hoặc bất khả thi nếu chỉ sử dụng sub_query. Mình sẽ hướng dẫn sử dụng hàm Row_number() và các ví dụ sau của bài viết Khả năng lồng ghép câu lệnh dưới dạng Truy vấn phụCó một điều khá thú vị ở CTE là các bạn có thể lồng ghép chúng với nhau theo nhiều cấp độ khác nhau. Mặc dù rằng mình không khuyến khích việc làm này trên thực tế vì nó sẽ làm tăng mức độ phức tạp của câu lệnh. Các bạn hoàn toàn có thể sử dụng nếu thấy phù hợp CTE start upChúng ta có thể định nghĩa một CTE với mệnh đề VỚI [WITH [, ...]] ::= cte_name [(column_name [, ...])] AS (cte_query) Ví dụ USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GOTạo biểu thức bảng chung đệ quyMột CTE đệ quy là một CTE tự tham chiếu đến chính nó. CTE đệ quy hữu ích khi làm việc với dữ liệu phân cấp vì CTE tiếp tục thực thi cho đến khi truy vấn trả về toàn bộ phân cấp Ví dụ tạo một bảng Nhân viên CREATE TABLE Employees ( EmployeeID int NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, ManagerID int NULL ) INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL) INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1) INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1) INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2) INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2) INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3) INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3) INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5) INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6) INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6) Sau khi tạo bảng Nhân viên, sau câu lệnh CHỌN, đứng trước mệnh đề VỚI bao gồm một CTE có tên cteReports được tạo WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports Vì vậy, CTE có thể là một công cụ hữu ích khi bạn cần tạo các tệp kết quả tạm thời có thể truy cập được bằng lệnh SELECT, INSERT, UPDATE, DELETE hoặc MERGE |
