Cte là gì

     

Trong lí giải này, các bạn sẽ tìm phát âm về Common Table Expression (CTE) vào SQL Server bằng cách sử dụng mệnh đề WITH.

Bạn đang xem: Cte là gì

Giới thiệu về CTE trong SQL Server

CTE là viết tắt của Common Table Expression (biểu thức bảng chung). Một CTE được cho phép bạn quan niệm một tập kết quả được lấy tên tạm thời mà bao gồm sẵn trong thời điểm tạm thời trong phạm vi triển khai một câu lệnh như SELECT, INSERT, UPDATE, DELETE hoặc MERGE.

Sau đây là cú pháp phổ biến của một CTE vào SQL Server:

WITH expression_name<(column_name <,...>)>AS (CTE_definition)SQL_statement;Trong cú pháp này:

Đầu tiên, chỉ định và hướng dẫn tên biểu thức (expression_name) mà chúng ta cũng có thể tham chiếu đến trong tương lai trong một truy hỏi vấn.Tiếp theo, chỉ định và hướng dẫn danh sách các cột được phân bóc tách bằng vệt phẩy sau expression_name. Số cột đề xuất giống với số cột được khẳng định trong CTE_definition.Sau đó, thực hiện từ khóa AS sau thương hiệu biểu thức hoặc danh sách cột nếu list cột được chỉ định.Sau đó, hãy quan niệm một câu lệnh SELECT trả về tập tác dụng điền đến CTE.Cuối cùng, hãy áp dụng CTE vào một tầm nã vấn (SQL_statement) chẳng hạn như SELECT, INSERT, UPDATE, DELETE hoặc MERGE.

Chúng tôi thích áp dụng CTE hơn là sử dụng những truy vấn con vì CTE dễ đọc hơn. Chúng tôi cũng thực hiện CTE trong các truy vấn có chứa analytic functions (hoặc window functions)

Ví dụ về CTE vào SQL Server

Hãy lấy một số trong những ví dụ về bài toán sử dụng các biểu thức bảng phổ biến.

Ví dụ CTE đơn giản trong SQL Server

Truy vấn này áp dụng CTE nhằm trả lại số tiền bán hàng của nhân viên marketing trong năm 2018:

WITH cte_sales_amounts (staff, sales, year) AS ( SELECT first_name + " " + last_name, SUM(quantity * list_price * (1 - discount)), YEAR(order_date) FROM sales.orders o INNER JOIN sales.order_items i ON i.order_id = o.order_id INNER JOIN sales.staffs s ON s.staff_id = o.staff_id GROUP BY first_name + " " + last_name, year(order_date))SELECT staff, salesFROM cte_sales_amountsWHERE year = 2018;Hình ảnh sau đây cho thấy tập đúng theo kết quả:

*

Trong ví dụ như này:

Đầu tiên, chúng ta định nghĩa cte_sales_amounts là tên gọi của CTE. CTE trả về một tập hiệu quả gồm có tía cột staff, year với sales.Thứ hai, họ tạo một truy vấn trả về con số tổng lệch giá của nhân viên bán sản phẩm và năm bằng phương pháp truy vấn dữ liệu từ bảng orders, order_items cùng staffs.Thứ ba, chúng ta sử dụng CTE trong truy vấn vấn bên phía ngoài và chỉ chọn các bạn dạng ghi có năm là 2018.Lưu ý rằng lấy ví dụ như này chỉ nhằm mục tiêu mục đích trình diễn sẽ giúp bạn từ từ hiểu được giải pháp thức hoạt động vui chơi của các biểu thức bảng thông thường. Bao gồm một phương pháp tối ưu hơn để đạt được công dụng mà không cần thực hiện CTE.

Sử dụng CTE nhằm tạo báo cáo trung bình dựa trên số lượng

Ví dụ này thực hiện CTE nhằm trả về số lượng đơn hàng trung bình trong năm 2018 cho toàn bộ nhân viên cung cấp hàng.

WITH cte_sales AS ( SELECT staff_id, COUNT(*) order_count FROM sales.orders WHERE YEAR(order_date) = 2018 GROUP BY staff_id)SELECT AVG(order_count) average_orders_by_staffFROM cte_sales;Đây là đầu ra:

average_orders_by_staff-----------------------48(1 row affected)Trong lấy ví dụ này:

Đầu tiên, họ sử dụng cte_sales làm cho tên của CTE. Bọn họ đã vứt qua list cột của CTE để nó được rước từ câu lệnh quan niệm CTE. Trong lấy ví dụ này, nó bao gồm các cột staff_id với order_count.

Thứ hai, bọn họ sử dụng truy vấn vấn sau để xác minh tập hiệu quả của cte_sales. Truy vấn trả về số lượng deals trong năm 2018 của nhân viên cấp dưới kinh doanh.

SELECT staff_id, COUNT(*) order_countFROM sales.ordersWHERE YEAR(order_date) = 2018GROUP BY staff_id;Thứ ba, họ sử dụng cte_sales vào câu lệnh phía bên ngoài và thực hiện hàm AVG() để đưa số lượng đơn hàng trung bình của toàn bộ nhân viên.

SELECT AVG(order_count) average_orders_by_staffFROM cte_sales;

Sử dụng nhiều CTE trong một tầm nã vấn duy nhất

Ví dụ sau thực hiện hai CTE cte_category_counts cùng cte_category_sales để trả về số số lượng hàng hóa và lợi nhuận cho từng nhiều loại sản phẩm. Truy tìm vấn bên phía ngoài sẽ join nhị CTE bằng phương pháp sử dụng cột category_id.

Xem thêm: Khám Phá Top 6 Web Game Câu Cá Offline Pc, Webgame Online

WITH cte_category_counts ( category_id, category_name, product_count)AS ( SELECT c.category_id, c.category_name, COUNT(p.product_id) FROM production.products phường INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY c.category_id, c.category_name),cte_category_sales(category_id, sales) AS ( SELECT p.category_id, SUM(i.quantity * i.list_price * (1 - i.discount)) FROM sales.order_items i INNER JOIN production.products phường ON p.product_id = i.product_id INNER JOIN sales.orders o ON o.order_id = i.order_id WHERE order_status = 4 -- completed GROUP BY p.category_id) SELECT c.category_id, c.category_name, c.product_count, s.salesFROM cte_category_counts c INNER JOIN cte_category_sales s ON s.category_id = c.category_idORDER BY c.category_name;Đây là tập kết quả:

*

CTE đệ quy trong SQL Server

CTE đệ quy (recursive common table expression) là một CTE tham chiếu đến bao gồm nó. Bằng phương pháp làm như vậy, CTE lặp đi lặp lại thực thi, trả về các tập bé dữ liệu, cho đến khi nó trả về tập công dụng hoàn chỉnh.

CTE đệ quy bổ ích trong việc truy vấn dữ liệu phân cấp, chẳng hạn như biểu thiết bị tổ chức trong các số ấy một nhân viên báo cáo với người thống trị hoặc hóa đơn nguyên liệu nhiều cấp khi một sản phẩm bao gồm nhiều yếu tố và phiên bản thân mỗi thành phần cũng bao gồm nhiều nhân tố khác.

Sau đấy là cú pháp của một CTE đệ quy:

WITH expression_name (column_list)AS( -- Anchor member initial_query UNION ALL -- Recursive thành viên that references expression_name. Recursive_query )-- references expression nameSELECT *FROM expression_nameNói chung, một CTE đệ quy có cha phần:

Truy vấn ban đầu trả về tập kết quả cơ bản của CTE. Tróc nã vấn thuở đầu được hotline là thành phần neo Anchor member.Một điều kiện xong xuôi được chỉ định trong phần tử đệ quy ngừng việc thực thi của phần tử đệ quy.

Thứ tự tiến hành của một CTE đệ quy như sau:

Đầu tiên, thực thi bộ phận neo để tạo ra tập tác dụng cơ sở (R0), sử dụng hiệu quả này đến lần lặp tiếp theo.Thứ hai, thực thi phần tử đệ quy với tập công dụng đầu vào trường đoản cú lần lặp trước (Ri-1) cùng trả về tập công dụng phụ (Ri) cho đến khi điều kiện chấm dứt được đáp ứng.

Lưu đồ dùng sau minh họa việc thực hiện CTE đệ quy:

*

Ví dụ về CTE đệ quy trong SQL Server

Hãy lấy một trong những ví dụ về việc áp dụng CTE đệ quy:

Ví dụ CTE đệ quy đơn giản dễ dàng trong SQL Server

Ví dụ này áp dụng một CTE đệ quy nhằm trả về ngày vào tuần từ Monday cho Saturday:

WITH cte_numbers(n, weekday) AS ( SELECT 0, DATENAME(DW, 0) UNION ALL SELECT n + 1, DATENAME(DW, n + 1) FROM cte_numbers WHERE n Đây là tập trung quả:

*

Trong ví dụ như này:

Hàm DATENAME() trả về tên của những ngày vào tuần dựa trên một số trong những ngày trong tuần.

Phần tử neo trả về Monday

SELECT 0, DATENAME(DW, 0)Phần tử đệ quy trả về kết quả từ ngày hôm sau bước đầu từ Tuesday mang đến Sunday.

SELECT n + 1, DATENAME(DW, n + 1) FROM cte_numbers WHERE n Điều kiện trong mệnh đề WHERE là điều kiện ngừng việc thực hiện thành phần đệ quy lúc n là 6

n

Sử dụng CTE đệ quy nhằm truy vấn tài liệu phân cấp trong SQL Server

Xem bảng sales.staffs sau từ bỏ cơ sở dữ liệu mẫu:

*

Trong bảng này, một nhân viên báo cáo cho ko hoặc một tín đồ quản lý. Một người cai quản có thể có không hoặc những nhân viên. Người cai quản cấp cao nhất không có người quản lý. Quan hệ được chỉ định trong số giá trị của cột manager_id. Nếu như một nhân viên cấp dưới không báo cáo cho bất kỳ nhân viên như thế nào (trong trường phù hợp là người quản lý cấp cao nhất), cực hiếm trong cột manager_id là NULL.

Ví dụ này áp dụng CTE đệ quy để mang tất cả cấp dưới của người quản lý cấp tối đa không tất cả người quản lý (hoặc giá trị trong manager_id cột là NULL):

WITH cte_org AS ( SELECT staff_id, first_name, manager_id FROM sales.staffs WHERE manager_id IS NULL UNION ALL SELECT e.staff_id, e.first_name, e.manager_id FROM sales.staffs e INNER JOIN cte_org o ON o.staff_id = e.manager_id)SELECT * FROM cte_org;Đây là đầu ra:

*

Trong ví dụ này, thành phần neo nhận thấy người thống trị cấp tối đa và truy vấn vấn đệ quy trả về cấp dưới của người thống trị cấp tối đa và cấp cho dưới của họ, v.v.

Trong khuyên bảo này, chúng ta đã học tập cách thực hiện Common Table Expression (CTE) trong SQL Server để tạo những truy vấn tinh vi theo phương pháp dễ hiểu.


Chuyên mục: Tin Tức