Trong bài viết này, chúng ta sẽ cùng học cách sử dụng MySQL CTE hoặc biểu thức bảng thông thường (common table expression) để xây dựng các truy vấn phức tạp theo cách dễ đọc hơn.
// Chú ý: MySQL đã giới thiệu biểu thức bảng chung hoặc tính năng CTE kể từ phiên bản 8.0, vì vậy bạn nên có MySQL 8.0+ để thực hành với các câu lệnh trong hướng dẫn này.
common table expression or CTE là gì ?
common table expression là một tập kết quả tạm thời được đặt tên chỉ tồn tại trong phạm vi thực thi của một câu lệnh SQL đơn lẻ, ví dụ: SELECT
, INSERT
, UPDATE
, hoặc DELETE
.
Tương tự như bảng dẫn xuất , CTE không được lưu trữ dưới dạng đối tượng và chỉ tồn tại trong khi thực hiện truy vấn.
Không giống như bảng dẫn xuất, CTE có thể tự tham chiếu ( CTE đệ quy ) hoặc có thể được tham chiếu nhiều lần trong cùng một truy vấn. Ngoài ra, CTE cung cấp khả năng đọc và hiệu suất tốt hơn so với bảng dẫn xuất.
Cú pháp MySQL CTE
Cấu trúc của CTE bao gồm tên, danh sách cột tùy chọn và truy vấn xác định CTE. Sau khi CTE được xác định, bạn có thể sử dụng nó làm dạng xem trong , , SELECT
, INSERT
hoặc câu lệnh.UPDATE
DELETE
CREATE VIEW
Sau đây minh họa cú pháp cơ bản của CTE:
WITH cte_name (column_list) AS ( query ) SELECT * FROM cte_name;
Lưu ý rằng số cột trong tệp query
phải giống với số cột trong tệp column_list
. Nếu bạn bỏ qua column_list
, CTE sẽ sử dụng danh sách cột của truy vấn xác định CTE
Các ví dụ MySQL CTE đơn giản
Chúng tôi sẽ sử dụng bảng customers
từ cơ sở dữ liệu mẫu để trình diễn:
Ví dụ sau minh họa cách sử dụng CTE để truy vấn dữ liệu từ bảng customers
trong cơ sở dữ liệu mẫu .
Lưu ý rằng ví dụ này chỉ nhằm mục đích trình diễn để giúp bạn dễ hiểu khái niệm CTE.
WITH customers_in_usa AS ( SELECT customerName, state FROM customers WHERE country = 'USA' ) SELECT customerName FROM customers_in_usa WHERE state = 'CA' ORDER BY customerName;
Trong ví dụ này, tên của CTE là customers_in_usa
, truy vấn xác định CTE trả về hai cột customerName
và state
. Do đó,CTE customers_in_usa
trả lại tất cả các khách hàng ở Hoa Kỳ.
Sau khi xác định CTE customers_in_usa
, chúng tôi đã tham chiếu nó trong SELECT
để chọn những khách hàng ở California.
Xem ví dụ sau:
WITH topsales2003 AS ( SELECT salesRepEmployeeNumber employeeNumber, SUM(quantityOrdered * priceEach) sales FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN customers USING (customerNumber) WHERE YEAR(shippedDate) = 2003 AND status = 'Shipped' GROUP BY salesRepEmployeeNumber ORDER BY sales DESC LIMIT 5 ) SELECT employeeNumber, firstName, lastName, sales FROM employees JOIN topsales2003 USING (employeeNumber);
Trong ví dụ này, CTE trả về 5 đại diện bán hàng hàng đầu trong năm 2003. Sau đó, chúng tôi đã tham chiếu đến CTE topsales2003
để có thêm thông tin về đại diện bán hàng bao gồm tên và họ.
Một ví dụ MySQL CTE nâng cao hơn
Xem ví dụ sau:
WITH salesrep AS ( SELECT employeeNumber, CONCAT(firstName, ' ', lastName) AS salesrepName FROM employees WHERE jobTitle = 'Sales Rep' ), customer_salesrep AS ( SELECT customerName, salesrepName FROM customers INNER JOIN salesrep ON employeeNumber = salesrepEmployeeNumber ) SELECT * FROM customer_salesrep ORDER BY customerName;
Trong ví dụ này, chúng tôi có hai CTE trong cùng một truy vấn. CTE đầu tiên ( salesrep
) lấy các nhân viên có chức danh công việc là đại diện bán hàng. CTE thứ hai ( customer_salesrep
) tham chiếu CTE đầu tiên trong mệnh đề INNER JOIN
để biết đại diện bán hàng và khách hàng mà mỗi đại diện bán hàng phụ trách.
Sau khi có CTE thứ hai, chúng tôi truy vấn dữ liệu từ CTE đó bằng cách sử dụng một câu lệnh SELECT
đơn giản với mệnh dề ORDER BY
.
Cách sử dụng mệnh đề WITH
Có một số ngữ cảnh mà bạn có thể sử dụng mệnh đề WITH
để tạo các biểu thức bảng chung:
Đầu tiên, một mệnh đề WITH
có thể được sử dụng ở đầu các câu lệnh SELECT
, UPDATE
, và DELETE
:
WITH ... SELECT ... WITH ... UPDATE ... WITH ... DELETE ...
Thứ hai, một mệnh đề WITH
có thể được sử dụng ở đầu truy vấn con hoặc truy vấn con bảng dẫn xuất:
SELECT ... WHERE id IN (WITH ... SELECT ...); SELECT * FROM (WITH ... SELECT ...) AS derived_table;
Thứ ba, một mệnh đề WITH
có thể được sử dụng ngay trước SELECT
các mệnh đề bao gồm một mệnh đề SELECT
:
CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ... INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... DECLARE CURSOR ... WITH ... SELECT ... EXPLAIN ... WITH ... SELECT ...
Tổng kết
Trong hướng dẫn này, bạn đã học cách sử dụng MySQL CTE để đơn giản hóa các truy vấn phức tạp.
Các bạn có thể tham khảo: https://www.mysqltutorial.org/mysql-cte/
Các bài viết liên quan: