Tóm tắt về Common Table Expressions (CTE)

Truy vấn phân cấp và đệ quy trong SQL Server

Jasmin Ludolf

Content Developer

Đề cương khóa học

Đệ quy là gì và dùng thế nào?
Cách dùng Common Expression Tables (CTE) cho truy vấn phân cấp và đệ quy?
Cách biểu diễn phân cấp bằng SQL và truy vấn nó?
Ví dụ thực tế (tổ chức công ty, bill of materials, kế hoạch bay)

Ảnh búp bê Nga và tính chất đệ quy của chúng.

CTE là gì?

Định nghĩa CTE

Xác định một tập kết quả tạm thời có tên, gọi là common table expression (CTE). Nó được dẫn xuất từ một truy vấn đơn giản và được định nghĩa trong phạm vi thực thi của một câu lệnh đơn

Cấu trúc CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Ứng dụng của CTE

Quản lý truy vấn phức tạp
Dùng trong câu lệnh SELECT, INSERT, UPDATE hoặc DELETE
Có thể định nghĩa nhiều CTE trong một câu WITH
Kết hợp nhiều CTE bằng UNION hoặc JOIN
Thay cho view
Tự tham chiếu bảng
Truy vấn đệ quy

Định nghĩa CTE cho tổ chức IT

Chọn các quản lý bằng CTE:

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

WITH AS để định nghĩa CTE
SELECT trên bảng Employee

Định nghĩa CTE cho tổ chức IT

Chọn nhân viên có lương trên 10.000

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

Định nghĩa CTE cho tổ chức IT

Toàn bộ truy vấn:

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

Ayo berlatih!

Truy vấn phân cấp và đệ quy trong SQL Server