SQL Server - CTE Recursive Query

ตั้งแต่ SQL Server 2005 เป็นต้นมา ได้มีการนำ CTE (Common Table Expression's) เข้ามาสริม ซึ่ง CTE ถ้าจะเปรียบก็เหมือนกับตารางชั่วคราวตัวนึง  ซึ่งสามารถนำไปทำอะไรได้เยอะ  สิ่งนึงที่เป็นความสามารถของ CTE คือการทำรีเคอซีฟ (recursive) ตัวมันเองเรียกตัวมันเอง ดังนั้นตารางใดที่มีการต้องคิวรีเพื่อหาข้อมูลตัวมันเองซ้ำไปซ้ำมานั้นแหละ CTE เหมาะสุด
หลายท่านคงงง แล้วจะใช้งานได้อย่างไร ?
เพื่อให้เห็นภาพ ขอยกตัวอย่างข้อมูลสายการบังคับบัญชาของหน่วยงาน โดยการสร้างตารางเก็บข้อมูลตำแหน่งงาน ด้วยคำสั่ง ดังนี้

CREATE TABLE [dbo].[tblPosition](
[PositionID] [nchar](10) NOT NULL PRIMARY KEY,
[Description] [nvarchar](100) NULL,
[DependOn] [nchar](10) NULL
);
GO

ทำการเพิ่มข้อมูลตำแหน่งงาน และสายการบังคับบัญชา ดังนี้

INSERT INTO tblPosition VALUES('00000', 'President', NULL);
INSERT INTO tblPosition VALUES('10000', 'Managing Director', '00000');
INSERT INTO tblPosition VALUES('11000', 'Account Manager', '10000');
INSERT INTO tblPosition VALUES('12000', 'Marketing Manager', '10000');
INSERT INTO tblPosition VALUES('13000', 'IT Manager', '10000');
INSERT INTO tblPosition VALUES('11001', 'Accountant', '11000');
INSERT INTO tblPosition VALUES('12001','Marketing Representative','12000');
INSERT INTO tblPosition VALUES('13001', 'System Analyst', '13000');
INSERT INTO tblPosition VALUES('13002', 'Senior Programmer', '13000');
INSERT INTO tblPosition VALUES('13003', 'Programmer', '13000');

เมื่อเราเรียกแสดงข้อมูลด้วยคำสั่ง SELECT * FROM tblPosition จะได้ข้อมูลดังภาพ

ถ้าหากเราต้องการแสดงโครงสร้างตามลำดับขั้นของตำแหน่งต่าง ๆ ว่าตำแหน่งใดอยู่ภายใต้ตำแหน่งใด เราสามารถเขียนคำสั่งคิวรีโดยอาศัย CTE ได้ดังนี้

WITH CTE (DependOn, PositionID, [Description], LevelNumber)
  AS (
SELECT DependOn, PositionID, [Description], 1 LevelNumber
   FROM tblPosition  WHERE DependOn IS NULL
UNION ALL
SELECT E.DependOn, E.PositionID, E.[Description], LevelNumber + 1 AS LevelNumber
   FROM tblPosition E
INNER JOIN CTE ON E.DependOn=CTE.PositionID
        )
SELECT * FROM CTE ORDER BY  PositionID, LevelNumber;

เมื่อเรียกคำสั่งข้างต้น จะปรากฎข้อมูลดังภาพ

จากภาพจะเห็นว่ามีคอลัมน์ LevelNumber ปรากฎขึ้นมา ซึ่งจะแสดงถึงระดับของตำแหน่ง เช่น President อยู่ Level ที่ 1 ส่วน Programmer อยู่ Level ที่ 4 เป็นต้น

หมายเหตุ 
WITH CTE (DependOn, PositionID, [Description], LevelNumber)
เป็นการประกาศตารางชั่วคราวชื่อ CTE และภายใต้ตารางนี้มีคอลัมน์ต่าง ๆ ประกอบด้วย DependOn, PositionID, [Description], LevelNumber

จากตัวอย่างข้างต้น อาจจะเห็นภาพไม่ชัด ถ้าเราต้องการแสดงรายละเอียดของตำแหน่งที่ขึ้นตรงเป็นลำดับขั้นออกมาด้วย ก็สามารถทำได้โดยใช้คำสั่ง ดังนี้

WITH CTE(PositionID, [Narration], [Description], LevelNumber ,DependOn)
  AS (
SELECT PositionID, CONVERT(nvarchar(max) ,[Description]) AS [Narration], [Description],
   1 AS LevelNumber, DependOn
   FROM tblPosition  WHERE DependOn IS NULL
UNION ALL
SELECT E.PositionID, CONVERT(nvarchar(max) ,REPLICATE(SPACE(2), LevelNumber) + E.[Description]) AS [Narration],
   E.[Description], LevelNumber + 1 AS LevelNumber, E.DependOn
   FROM tblPosition E
INNER JOIN CTE ON E.DependOn=CTE.PositionID
)
SELECT * FROM CTE ORDER BY  PositionID, LevelNumber;

เมื่อเรียกคำสั่งข้างต้นจะได้ผลลัพธ์ดังภาพ

จากตัวอย่างที่แสดงมาทั้งหมด ลองทำความเข้าใจซึ่งไม่ยากจนเกินไปนัก เราสามารถนำไปประยุกต์ในการแสดงผลอย่างอื่นได้อีก เช่น ผังบัญชี พนักงานที่อยู่ภายใต้หัวหน้า เป็นต้น

แต่จากทั้ง 2 ตัวอย่างข้างต้น ยังมีข้อผิดพลาดอยู่ ซึ่งผู้เขียนตั้งใจให้มันเกิด
ติ๊ก ต๊อก ติ๊ก ต๊อก หาดูว่าผิดตรงไหน

บอกเลยดีกว่า ผิดตรงการเรียงลำดับ ถ้ากรณีเราตั้งรหัส PositionID สะเปะสะปะ การเรียงลำดับจะทำให้มั่ว ดังนั้น หากต้องการจัดลำดับที่ถูกต้อง จำเป็นต้องเพิ่มคอลัมน์ภายใต้ CTE ขึ้นมาอีก 1 คอลัมน์ แล้วเขียนคำสั่ง ตามตัวอย่าง แล้วลองเรียกใช้งานดู

WITH CTE(PositionID, [Narration], [Description], LevelNumber ,DependOn, Trace)
  AS (
SELECT PositionID, CONVERT(nvarchar(max) ,[Description]) AS [Narration], [Description],
   1 AS LevelNumber, DependOn , CONVERT(nvarchar(max),PositionID) AS Trace
   FROM tblPosition  WHERE DependOn IS NULL
UNION ALL
SELECT E.PositionID, CONVERT(nvarchar(max) ,REPLICATE(SPACE(2), LevelNumber) + E.[Description]) AS [Narration],
   E.[Description], LevelNumber + 1 AS LevelNumber, E.DependOn ,
   CONVERT(nvarchar(max),CTE.Trace + E.PositionID) AS Trace
   FROM tblPosition E
   INNER JOIN CTE ON E.DependOn=CTE.PositionID
)
SELECT * FROM CTE ORDER BY  Trace;

ผลจากการปรับคำสั่งข้างต้น จะได้ผลลัพธ์ออกมาดังภาพ

จะเห็นว่าคอลัมน์ Trace จะทำการจัดเก็บข้อมูลรหัสที่อยู่ก่อนหน้าไว้ทั้งหมด ซึ่งเราจะใช้ข้อมูลนี้มาทำการจัดเรียงเพื่อให้การแสดงผลข้อมูลถูกต้อง

แค่ตาราง tblPosition เพียงตารางเดียว ทำไมมันช่างงงงวย วกวนเช่นนี้ ทาเกชิ สู้ ๆ

ยุคสมัยเปลี่ยนไปเดี๋ยวทำงานที่ไหนก็ได้ โชคดี  =  (โชค + อธิบดี) มีเงินใช้

เพื่อนผมชื่อโชค ครับ  ย้ำ โชค นะครับ