Trong SQL, mệnh đề WITH còn được gọi là CTE. Đây là một tính năng mạnh mẽ cho phép chúng tôi tạo các tập kết quả tạm thời trong một truy vấn. Một vai trò chính của CTE là đơn giản hóa các truy vấn phức tạp thành các truy vấn con nhỏ hơn và có thể tái sử dụng. Điều này giúp làm cho mã dễ đọc hơn và dễ bảo trì hơn về lâu dài.
Hãy tham gia cùng chúng tôi trong hướng dẫn này khi chúng tôi khám phá hoạt động của Biểu thức bảng chung bằng cách sử dụng mệnh đề WITH và chức năng được hỗ trợ.
Yêu cầu:
Đối với mục đích trình diễn, chúng tôi sẽ sử dụng như sau:
- Phiên bản MySQL 8.0 trở lên
- Cơ sở dữ liệu mẫu Sakila
Khi đã đáp ứng các yêu cầu nhất định, chúng ta có thể tiến hành tìm hiểu thêm về CTE và mệnh đề WITH.
Mệnh đề SQL VỚI
Mệnh đề WITH cho phép chúng ta định nghĩa một hoặc nhiều tập kết quả tạm thời được gọi là Biểu thức bảng chung.
Chúng ta có thể tham chiếu các CTE kết quả trong truy vấn chính giống như bất kỳ bảng hoặc tập kết quả nào khác. Điều này đóng một vai trò quan trọng trong việc tạo các truy vấn SQL mô-đun.
Mặc dù cú pháp của CTE có thể thay đổi một chút tùy theo yêu cầu của bạn, nhưng phần sau đây trình bày cú pháp cơ bản của CTE trong SQL:
VỚI cte_name (cột1, cột2, ...) NHƯ (
-- Truy vấn CTE
LỰA CHỌN ...
TỪ ...
Ở ĐÂU ...
)
-- Truy vấn chính
LỰA CHỌN ...
TỪ ...
THAM GIA cte_name TRÊN ...
Ở ĐÂU ...
Chúng tôi bắt đầu với từ khóa VỚI cho cơ sở dữ liệu SQL biết rằng chúng tôi muốn tạo và sử dụng CTE.
Tiếp theo, chúng tôi chỉ định tên cho CTE để cho phép chúng tôi tham chiếu nó trong các truy vấn khác.
Chúng tôi cũng chỉ định danh sách tên cột tùy chọn nếu CTE bao gồm bí danh cột.
Tiếp theo, chúng tôi tiến hành xác định truy vấn CTE. Phần này chứa tất cả các tác vụ hoặc dữ liệu mà CTE thực hiện được đặt trong một cặp dấu ngoặc đơn.
Cuối cùng, chúng tôi chỉ định truy vấn chính tham chiếu CTE.
Cách sử dụng ví dụ:
Một trong những cách tốt nhất để hiểu cách sử dụng và làm việc với CTE là xem xét một ví dụ thực tế.
Lấy ví dụ cơ sở dữ liệu mẫu Sakila. Giả sử chúng ta muốn tìm 10 khách hàng có số lượng thuê nhiều nhất.
Hãy xem CTE được hiển thị sau đây.
Sử dụng mệnh đề WITH trong SQL để tìm ra 10 khách hàng có số lượng thuê cao nhất:
VỚI Khách hàngRentals NHƯ (CHỌN c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) NHƯ Rent_count
TỪ khách hàng c
THAM GIA thuê r TRÊN c.customer_id = r.customer_id
NHÓM THEO c.customer_id, c.first_name, c.last_name
)
LỰA CHỌN *
TỪ Khách hàngCho thuê
ĐẶT HÀNG THEO Rent_count DESC
GIỚI HẠN 10;
Trong ví dụ đã cho, chúng tôi bắt đầu bằng cách xác định CTE mới bằng từ khóa VỚI, theo sau là tên mà chúng tôi muốn gán cho CTE. Trong trường hợp này, chúng tôi gọi nó là “CustomerRentals”.
Bên trong nội dung CTE, chúng tôi tính toán số lượng tiền thuê cho từng khách hàng bằng cách kết hợp bảng khách hàng và bảng tiền thuê.
Cuối cùng, trong truy vấn chính, chúng tôi chọn tất cả các cột từ CTE, sắp xếp kết quả dựa trên số lượng thuê (thứ tự giảm dần) và giới hạn đầu ra chỉ ở 10 hàng trên cùng.
Điều này cho phép chúng tôi tìm nạp những khách hàng có số lượng thuê cao nhất như được hiển thị trong kết quả sau:
CTE đệ quy
Trong một số trường hợp khác, bạn có thể phải xử lý các cấu trúc dữ liệu phân cấp. Đây là lúc các CTE đệ quy phát huy tác dụng.
Hãy lấy ví dụ về trường hợp chúng tôi muốn điều hướng tổ chức phân cấp hoặc thể hiện một cấu trúc dạng cây. Chúng ta có thể sử dụng từ khóa WITH RECURSIVE để tạo CTE đệ quy.
Vì không có dữ liệu phân cấp mà chúng ta có thể sử dụng trong cơ sở dữ liệu Sakila để chứng minh CTE đệ quy, nên chúng ta hãy thiết lập một ví dụ cơ bản.
Bộ phận TẠO BẢNG (bộ phận_id INT KHÓA CHÍNH AUTO_INCREMENT,
tên khoa_VARCHAR(255) KHÔNG NULL,
parent_department_id INT,
KHÓA NGOÀI (parent_department_id) TÀI LIỆU THAM KHẢO bộ phận(department_id)
);
XÁC NHẬN VÀO bộ phận (tên bộ phận, parent_department_id)
GIÁ TRỊ
('Công ty', KHÔNG),
('Tài chính', 1),
('HR', 1),
('Kế toán', 2),
'Tuyển dụng', 3),
(“Bảng lương”, 4);
Trong trường hợp này, chúng ta có một bảng “bộ phận” mẫu với một số dữ liệu ngẫu nhiên. Để tìm cấu trúc phân cấp của các phòng ban, chúng ta có thể sử dụng CTE đệ quy như sau:
VỚI Hệ thống phân cấp bộ phận đệ quy NHƯ (CHỌN khoa_id, tên khoa, parent_department_id
TỪ bộ phận
Ở ĐÂU parent_department_id LÀ NULL
ĐOÀN TẤT CẢ
CHỌN d.department_id, d.department_name, d.parent_department_id
TỪ khoa d
THAM GIA DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
LỰA CHỌN *
TỪ Hệ thống phân cấp bộ phận;
Trong trường hợp này, CTE đệ quy bắt đầu với các khoa có “parent_department_id” NULL (các khoa gốc) và truy xuất đệ quy các khoa con.
Phần kết luận
Trong hướng dẫn này, chúng ta đã tìm hiểu về các tính năng cơ bản và hữu ích nhất trong cơ sở dữ liệu SQL như Biểu thức bảng chung bằng cách hiểu cách làm việc với từ khóa VỚI.