Common Table Expression (CTE) trong SQL Server

• Trung Nguyen

• 18/03/2021

• 8 min read

Trong hướng dẫn này, bạn sẽ tìm hiểu về Common Table Expression (CTE) trong SQL Server bằng cách sử dụng mệnh đề WITH.

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 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.

Bạn đang đọc: Common Table Expression (CTE) trong SQL Server

Sau đây là cú pháp chung của một CTE trong 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 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 những truy vấn con vì CTE dễ đọc hơn. Chúng tôi cũng sử dụng CTE trong những truy vấn có chứa analytic functions ( hoặc window functions )

Ví dụ về CTE trong SQL Server

Hãy lấy một số ít ví dụ về việc sử dụng những biểu thức bảng thông dụng .

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

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 cấp dưới kinh doanh thương mại 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;

Hình ảnh sau đây cho thấy tập hợp hiệu quả :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 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 toàn bộ nhân viên cấp dưới 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 CTE. 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 hai 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;

Đâ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 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ề những tập con tài liệu, cho đến khi nó trả về tập kết quả hoàn hảo .

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

Nói chung, một CTE đệ quy có ba phần :

1. 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.
2. 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.
3. 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 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.

Lưu đồ sau minh họa việc thực thi CTE đệ quy :

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

Hãy lấy một số ít ví dụ về việc sử dụng CTE đệ quy :

Ví dụ CTE đệ quy đơn giản trong 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;

Đây là tập kết quả :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

Sử dụng CTE đệ quy để truy vấn dữ liệu phân cấp trong SQL Server

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

Trong bảng này, một nhân viên báo cáo cho không hoặc một người quản lý. Một người quản lý có thể có không hoặc nhiều nhân viên. Người quản lý cấp cao nhất không có người quản lý. Mối quan hệ được chỉ định trong các giá trị của cột manager_id. Nếu một nhân viên không báo cáo cho bất kỳ nhân viên nào (trong trường hợp là người quản lý cấp cao nhất), giá trị trong cột manager_id là NULL.

Ví dụ này sử dụng CTE đệ quy để lấy tất cả cấp dưới của người quản lý cấp cao nhất không 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, phần tử neo nhận được người quản lý cấp cao nhất và truy vấn đệ quy trả về cấp dưới của người quản lý cấp cao nhất và cấp dưới của họ, v.v.

Trong hướng dẫn này, bạn đã học cách sử dụng Common Table Expression ( CTE ) trong SQL Server để tạo những truy vấn phức tạp theo cách dễ hiểu .

Nếu Comdy hữu ích và giúp bạn tiết kiệm thời gian

Bạn hoàn toàn có thể vui vẻ tắt trình chặn quảng cáo ❤ ️ để tương hỗ chúng tôi duy trì hoạt động giải trí của website .