ทบทวน Common Table Expressions (CTE)

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

โครงร่างของคอร์ส

Recursion คืออะไร และใช้งานอย่างไร?
ใช้ Common Table Expressions (CTE) สำหรับ hierarchical และ recursive queries ได้อย่างไร?
แสดงลำดับชั้นด้วย SQL และเขียนคิวรีอย่างไร?
ตัวอย่างจริง (เช่น โครงสร้างองค์กร, bill of materials, แผนการบิน)

ภาพตุ๊กตา Russian Doll และคุณสมบัติเชิง recursive

CTE คืออะไร?

นิยามของ CTE

ระบุชุดผลลัพธ์ที่มีชื่อชั่วคราว เรียกว่า common table expression (CTE) ซึ่งได้มาจากคิวรีอย่างง่าย และกำหนดขอบเขตการใช้งานภายในคำสั่งเดียว

โครงสร้างของ CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

การใช้งาน CTE

จัดการคิวรีที่ซับซ้อนได้ง่ายขึ้น
ใช้ได้ในคำสั่ง SELECT, INSERT, UPDATE, หรือ DELETE
กำหนด CTE หลายตัวได้ในคำสั่ง WITH เดียว
รวม CTE หลายตัวด้วย UNION หรือ JOIN
ใช้แทน view ได้
อ้างอิงตัวเองได้
ใช้สำหรับ recursion query

กำหนด CTE สำหรับองค์กร IT

เลือกข้อมูลผู้จัดการด้วย CTE:

WITH JOBS (id, SurName, JobTitle) AS

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

ใช้ WITH AS เพื่อกำหนด CTE
ใช้ SELECT กับตาราง Employee

กำหนด CTE สำหรับองค์กร IT

เลือกพนักงานที่มีเงินเดือนมากกว่า 10,000

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

กำหนด CTE สำหรับองค์กร IT

คิวรีทั้งหมด:

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

มาฝึกกันเถอะ!

Hierarchical and Recursive Queries in SQL Server