คำสั่ง SQL ที่สามารถให้ผลลัพธ์ข้อมูลในลักษณะ hirachial

การเขียนคำสั่ง SQL เพื่อเรียกข้อมูลจากฐานข้อมูลมาใช้งาน เป็นอีกสิ่งหนึ่งที่คนเขียนโปรแกรมต้องเจอ สำหรับบันทึกนี้ เป็นคำสั่งในการเรียกข้อมูลที่เป็นลักษณะของ hirachical data เช่นข้อมูล โครงสร้างองค์กร หรือ ข้อมูลของโครงสร้างสมาชิกในระบบขายตรง

คำสั่งนี้ได้มาตอนที่กำลังพัฒนาระบบขายตรงให้บริษัทหนึ่ง โดยที่จะต้องเขียนโปรแกรมให้แสดงโครงสร้างของเครือข่ายสมาชิกซึ่งไม่จำกัดชั้นความลึกของลูกข่าย เหมือนรูปด้านล่างนี้

ในระบบก่อน ๆ จะใช้ส่วนของโปรแกรม แต่ลึก ๆ แล้วเชื่อว่ามีคำสั่ง SQL ที่สามารถให้ผลลัพธ์ข้อมูลในลักษณะ hirachial ได้ จึงได้ไปค้นหาในอินเตอร์เน็ต จนไปเจอในเวบของ mircrosoft ตามลิงค์ด้านล่างนี้ค่ะ

http://msdn.microsoft.com/en-us/library/ms186243.aspx

ส่วนนี้เป็น systax ค่ะ ขอ copy and paste เลยล่ะกัน

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

The semantics of the recursive execution is as follows:

  1. Split the CTE expression into anchor and recursive members.
  2. Run the anchor member(s) creating the first invocation or base result set (T0).
  3. Run the recursive member(s) with Ti as an input and Ti+1 as an output.
  4. Repeat step 3 until an empty set is returned.
  5. Return the result set. This is a UNION ALL of T0 to Tn.

ส่วนนี้เป็นตัวอย่างที่นำมาปรับใช้

WITH DirectReports ( memberID, memberName, sponsorID, currentPosition, Level)
AS
(
-- Anchor member definition
    SELECT  e.memberID, e.memberName,e.sponsorID, e.currentPosition, 0 AS Level
    FROM MemberProfile AS e
    where e.memberID = 'sp52000000'
    UNION ALL
-- Recursive member definition
    SELECT  e.memberID, e.memberName,e.sponsorID, e.currentPosition,
        Level + 1
    FROM MemberProfile AS e
   Inner Join DirectReports as d
on e.sponsorID = d.memberID
   )
-- Statement that executes the CTE
SELECT memberID, memberName,sponsorID, currentPosition, Level
FROM DirectReports

เท่านี้ก็จะได้ข้อมูลตามที่ต้องการ