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 แต่มันจะเอามายำ ๆ ๆ ๆ ยำไปยำมาก่อน แล้วสุดท้ายเอาไปใช้งาน ได้ผลตามต้องการ ดูง่ายไล่สะดวก
เอาไว้เป็นไอเดีย การทดลองทำจะทำให้เข้าใจมากยิ่งขึ้น
ไม่มีความคิดเห็น:
แสดงความคิดเห็น