Common Table Expression (CTE) trong cơ sở dữ liệu

Common Table Expression (CTE) là một khái niệm trong SQL (Structured Query Language), được sử dụng để định nghĩa một tập hợp các kết quả truy vấn tạm thời, mà có thể được tham chiếu nhiều lần trong cùng một truy vấn chính. CTE thường giúp làm cho các truy vấn phức tạp trở nên dễ đọc, dễ hiểu và dễ bảo trì hơn.

CTE được khai báo bằng từ khóa WITH và có thể được sử dụng trong các câu lệnh như SELECT, INSERT, UPDATE, hoặc DELETE.

Cú pháp cơ bản

WITH cte_name (column1, column2, ...) AS (

    -- Truy vấn con

    SELECT column1, column2, ...

    FROM table_name

    WHERE conditions

)

-- Truy vấn chính

SELECT column1, column2, ...

FROM cte_name

WHERE conditions;

Đặc điểm chính:

1. Dễ đọc và bảo trì: CTE giúp chia nhỏ truy vấn lớn thành các phần dễ hiểu hơn.

2. Có thể đệ quy: CTE hỗ trợ truy vấn đệ quy (recursive CTE), cho phép xử lý dữ liệu lặp đi lặp lại như duyệt cây hoặc tính toán lặp.

3. Không tạo bảng tạm: Dữ liệu trong CTE không lưu trữ tạm thời trong cơ sở dữ liệu mà chỉ tồn tại trong quá trình truy vấn.

Ví dụ 1: CTE đơn giản

Tính tổng số lượng sản phẩm bán ra từ bảng Sales:

WITH TotalSales AS (

    SELECT ProductID, SUM(Quantity) AS TotalQuantity

    FROM Sales

    GROUP BY ProductID

)

SELECT *

FROM TotalSales

WHERE TotalQuantity > 100;

Ví dụ 2: CTE đệ quy

Tính cấp bậc trong một cấu trúc cây (ví dụ: bảng nhân viên với quản lý):

WITH RecursiveCTE AS (

    -- Truy vấn cơ sở (Base case)

    SELECT EmployeeID, ManagerID, 1 AS Level

    FROM Employees

    WHERE ManagerID IS NULL

    UNION ALL

    -- Truy vấn đệ quy (Recursive case)

    SELECT e.EmployeeID, e.ManagerID, r.Level + 1

    FROM Employees e

    INNER JOIN RecursiveCTE r

    ON e.ManagerID = r.EmployeeID

)

SELECT *

FROM RecursiveCTE;

Khi nào nên dùng CTE?

• Khi cần tái sử dụng kết quả của một truy vấn con nhiều lần.

• Khi cần làm cho truy vấn phức tạp dễ đọc hơn.

• Khi làm việc với các vấn đề đệ quy, như cấu trúc cây.