SQL Server : CTEs (Common Table Expressions)

Common Table Expressions หรือ CTEs เริ่มถูกนำมาใช้ใน SQL Server 2005 เป็นครั้งแรก โดยที่ CTE นั้นเป็นผลลัพท์ที่เป็นตารางชั่วคราว ซึ่งสามารถนำไปอ้างหรือใช้งานภายใต้คำสั่งคิวรี่ SELECT INSERT UPDATE หรือ DELETE ใน SQL Server 2008 ได้เพิ่มความสามารถให้ใช้ CTE ในคำสั่ง MERGE ได้ การใช้งาน CTE เริ่มด้วยการใช้ WITH

WITH  ชื่อตาราง_CTE
    AS
    (
          คำสั่ง Query ผลลัพธ์ของคิวรีที่ได้จะไปเก็บใน ชื่อตาราง_CTE
     )
คำสั่ง Query ที่เรียกใช้งานตาราง CTE

กรณีที่สร้างหลาย CTE  ก็ให้เพิ่มโดยใส่ , (คอมม่า) แล้วก็ชื่อตารางตามตัวอย่าง

WITH  ชื่อตาราง_CTE_1
    AS
    (
          คำสั่ง Query ผลลัพธ์ของคิวรีที่ได้จะไปเก็บใน ชื่อตาราง_CTE_1
     ) ,
ชื่อตาราง_CTE_2
    AS
    (
          คำสั่ง Query ผลลัพธ์ของคิวรีที่ได้จะไปเก็บใน ชื่อตาราง_CTE_2
         และสามารถเรียกใช้ตาราง CTE_1 ได้ด้วย
     )
คำสั่ง Query ที่เรียกใช้งานตาราง CTE

รูปแบบคำสั่งแค่นี้ก็เราก็สามารถแปรกระบวนท่าในการเขียนจัดการคำสั่งคิวรีได้มากมาย

ตัวอย่าง

WITH CTE AS (
            SELECT dd.Driver_no, COUNT(dd.Do_Date) Total_day
                FROM (SELECT DISTINCT Driver_no, Do_Date
                                     FROM DoDriver WHERE MYear = '2022' AND MPeriod = '12') dd
                GROUP BY dd.Driver_no
        )
 SELECT d.*, c.Total_Day FROM CTE c
       LEFT JOIN (
                SELECT d.Driver_no, d.Driver_name, SUM(d.Qty_do) Tot_do
                    ,SUM(d.Z_Q_tot) Total_QTY_G
                    ,SUM(d.Z_T_tot + d.Z_Trip_E1 ) as Total_WAGE
                    ,COUNT(d.Do_no) Total_trip, SUM(d.Z_T_tot) Total_T_tot
                    ,SUM(d.Z_Trip_E1) AS Total_E1, SUM(d.Z_Trip_E2) AS Total_E2
                    ,SUM(d.Z_Trip_E3) AS Total_E3, SUM(d.Z_Trip_E4) AS Total_E4
                    , SUM(d.Z_Trip_E5) AS Total_E5
                FROM DoDriver d WHERE d.MYear = '2022' AND d.MPeriod = '12'
                GROUP BY d.Driver_no, d.Driver_name
                ) AS d
         ON c.Driver_no = d.Driver_no

ตัวอย่าง 

WITH CTE AS (
         SELECT  RowOrder, MYear, MPeriod, Driver_no, Do_date, Do_Time
                      , ROW_NUMBER() OVER(PARTITION BY Driver_no, Do_Date
                               ORDER BY Driver_no, Do_Date, Do_time) AS Trip_of_D
                      , ROW_NUMBER() OVER(PARTITION BY Driver_no
                               ORDER BY Driver_no,Do_Date, Do_time) AS Trip_of_M
              FROM DoDriver
              WHERE MYear = '2022' AND MPeriod = '20212'
                    AND Driver_no = '0001' )
SELECT c.*,c1.Trip_sum_M FROM CTE c
     LEFT JOIN (SELECT Driver_no, COUNT(*) AS Trip_sum_M
                                    FROM CTE
                                    GROUP BY Driver_no) c1
     ON c.Driver_no = c1.Driver_no

จริง ๆ มัน เหมือน ๆ กับทำ subquery แต่มันจะเอามายำ ๆ ๆ ๆ ยำไปยำมาก่อน แล้วสุดท้ายเอาไปใช้งาน ได้ผลตามต้องการ ดูง่ายไล่สะดวก

เอาไว้เป็นไอเดีย การทดลองทำจะทำให้เข้าใจมากยิ่งขึ้น