Common Table Expression CTE trong SQL Server

Giới thiệu về CTE trong SQL Server – CTE trong SQL là gì?

CTE SQL là gì?

CTE là gì? With CTE sql server là viết tắt của Common Table Expression (biểu thức bảng chung). Một CTE in sql server cho phép bạn định nghĩa một tập kết quả được đặt tên tạm thời mà có sẵn tạm thời trong phạm vi thực hiện một câu lệnh như SELECT, INSERT, UPDATE, DELETE hoặc MERGE.

Sau đây là cú pháp chung của một CTE SQL Server:

WITH expression_name[(column_name [,...])]
AS
    (CTE_definition)
SQL_statement;

Trong cú pháp này:

• Đầu tiên, chỉ định tên biểu thức (expression_name) mà bạn có thể tham chiếu đến sau này trong một truy vấn.
• Tiếp theo, chỉ định danh sách các cột được phân tách bằng dấu phẩy sau expression_name. Số cột phải giống với số cột được xác định trong CTE_definition.
• Sau đó, sử dụng từ khóa AS sau tên biểu thức hoặc danh sách cột nếu danh sách cột được chỉ định.
• Sau đó, hãy định nghĩa một câu lệnh SELECT trả về tập kết quả điền cho CTE.
• Cuối cùng, hãy sử dụng CTE trong sql server trong một truy vấn (SQL_statement) chẳng hạn như SELECT, INSERT, UPDATE, DELETE hoặc MERGE.

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

Ví dụ về CTE trong SQL Server

Một số ví dụ về việc sử dụng các biểu thức bảng phổ biến như sau:

Ví dụ CTE sql đơn giản

Truy vấn này sử dụng CTE để trả lại số tiền bán hàng của nhân viên kinh doanh 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, 
    sales
FROM 
    cte_sales_amounts
WHERE
    year = 2018;

Trong ví dụ này:

• Đầu tiên, chúng ta định nghĩa cte_sales_amounts là tên của CTE. CTE trả về một tập kết quả gồm có ba cột staff, year và sales.
• Thứ hai, chúng ta tạo một truy vấn trả về số lượng tổng doanh thu của nhân viên bán hàng và năm bằng cách truy vấn dữ liệu từ bảng orders, order_items và staffs.
• Thứ ba, chúng ta sử dụng CTE trong truy vấn bên ngoài và chỉ chọn các bản ghi có năm là 2018.

Lưu ý rằng ví dụ này chỉ nhằm mục đích trình diễn để giúp bạn dần dần hiểu được cách thức hoạt động của các biểu thức bảng thông thường. Có một cách tối ưu hơn để đạt được kết quả mà không cần sử dụng CTE.

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

Ví dụ này sử dụng CTE để trả về số lượng đơn hàng trung bình trong năm 2018 cho tất cả nhân viên bán 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_staff
FROM 
    cte_sales;

Đây là đầu ra:

average_orders_by_staff
-----------------------
48

(1 row affected)

Trong ví dụ này:

Đầu tiên, chúng ta sử dụng cte_sales làm tên của with CTE in SQL server. Chúng ta đã bỏ qua danh sách cột của CTE để nó được lấy từ câu lệnh định nghĩa CTE. Trong ví dụ này, nó bao gồm các cột staff_id và order_count.

Thứ hai, chúng ta sử dụng truy vấn sau để xác định tập kết quả của cte_sales. Truy vấn trả về số lượng đơn hàng trong năm 2018 của nhân viên kinh doanh.

SELECT    
    staff_id, 
    COUNT(*) order_count
FROM    
    sales.orders
WHERE 
    YEAR(order_date) = 2018
GROUP BY 
    staff_id;

Thứ ba, chúng ta sử dụng cte_sales trong câu lệnh bên ngoài và sử dụng hàm AVG() để lấy số lượng đơn hàng trung bình của tất cả nhân viên.

SELECT
    AVG(order_count) average_orders_by_staff
FROM 
    cte_sales;

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

Ví dụ sau sử dụng 2 SQL server with CTE cte_category_counts và cte_category_sales để trả về số lượng sản phẩm và doanh số cho từng loại sản phẩm. Truy vấn bên ngoài sẽ join hai CTE bằng cách sử dụng cột category_id.

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 p
        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 p 
            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.sales
FROM
    cte_category_counts c
    INNER JOIN cte_category_sales s 
        ON s.category_id = c.category_id
ORDER BY 
    c.category_name;

CTE đệ quy trong SQL Server

CTE đệ quy (recursive common table expression) là một CTE tham chiếu đến chính nó. Bằng cách làm như vậy, CTE lặp đi lặp lại thực thi, trả về các tập con dữ liệu, cho đến khi nó trả về tập kết quả hoàn chỉnh.

SQL CTE đệ quy hữu ích trong việc truy vấn dữ liệu phân cấp, chẳng hạn như biểu đồ tổ chức trong đó một nhân viên báo cáo với người quản lý hoặc hóa đơn nguyên vật liệu nhiều cấp khi một sản phẩm bao gồm nhiều thành phần và bản thân mỗi thành phần cũng bao gồm nhiều thành phần 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 member that references expression_name.
    recursive_query  
)
-- references expression name
SELECT *
FROM   expression_name

CTE đệ quy có mấy phần?

Một CTE đệ quy có 3 phần:

• Truy vấn ban đầu trả về tập kết quả cơ bản của CTE. Truy vấn ban đầu được gọi là phần tử neo Anchor member.
• Một truy vấn đệ quy tham chiếu đến CTE, do đó, nó được gọi là phần tử đệ quy Recursive member. Thành viên đệ quy được liên kết với thành viên neo bằng cách sử dụng toán tử UNION ALL.
• Một điều kiện kết thúc được chỉ định trong phần tử đệ quy kết thúc việc thực thi của phần tử đệ quy.

Thứ tự thực hiện CTE đệ quy là gì?

Thứ tự thực thi của một CTE đệ quy như sau:

• Đầu tiên, thực thi phần tử neo để tạo tập kết quả cơ sở (R0), sử dụng kết quả này cho lần lặp tiếp theo.
• Thứ hai, thực thi phần tử đệ quy với tập kết quả đầu vào từ lần lặp trước (Ri-1) và trả về tập kết quả phụ (Ri) cho đến khi điều kiện kết thúc được đáp ứng.
• Thứ ba, kết hợp tất cả các tập kết quả R0, R1,… Rn bằng cách sử dụng toán tử UNION ALL để tạo ra tập kết quả cuối cùng.

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

Ví dụ này sử dụng một CTE đệ quy để trả về ngày trong tuần từ Monday đến 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 < 6
)
SELECT 
    weekday
FROM 
    cte_numbers;

Trong ví dụ này:

Hàm DATENAME() trả về tên của các ngày trong tuần dựa trên một số 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ắt đầu từ Tuesday đến Sunday.

SELECT    
        n + 1, 
        DATENAME(DW, n + 1)
    FROM    
        cte_numbers
    WHERE n < 6

Điều kiện trong mệnh đề WHERE là điều kiện kết thúc việc thực hiện phần tử đệ quy khi n là 6

n < 6