Oracle select ...... connect by

[jataz2]

ผมคัดลอกมาจาก narisa ครับ credit คุณ siamnobi เคยเจอกระทู้ วงจรชีวิตของเว็บฟอร์มที่ narisa เเต่นานๆ กลับไปเปิดดู link แต่ดูไม่ได้เเล้ว เลยขอคัดลอกกระทู้ connect by ไม่อยากให้กระทู้ๆความรู้ดีๆหายไปครับ

connect by เป็น keyword สำหรับ hierarchy query ( query แบบเป็นลำดับชั้น) ครับ
ตัวอย่างที่น่าจะเข้าใจง่าย ๆ ก็คือ table employees ซึ่งเก็บรหัสพนักงานของหัวหน้าหนึ่งระดับ
(หัวหน้าโดยตรง) ไว้ที่ manager_id หมายความว่าที่เก็บในฟิลด์ manager_id แต่ละค่าจะต้องมี
employee_id ที่เท่ากันอยู่ (หัวหน้าแต่ละท่านก็จะต้องเป็นพนักงานคนหนึ่งเช่นเดียวกัน ซึ่งอาจจะมี
หัวหน้าขึ้นไปอีกชั้นด้วยก็ได้)

SELECT .....
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

จะได้ผลลัพธ์ เป็น tree diagram ของพนักงานแต่ละคน ก็จะมีชื่อลูกน้องต่อท้าย
ถ้าลูกน้อง มี ลูกน้องก็จะต่อท้ายไปเรื่อย ๆ

หัวหน้าส่วน
.....หัวหน้าแผนก1
..........ลูกน้อง1
..........ลูกน้อง2
.....หัวหน้าแผนก2
..........ลูกน้อง3
หัวหน้าแผนก1
.....ลูกน้อง1
.....ลูกน้อง2
..........

โดย PRIOR employee_id = manager_id จะหมายถึงว่าค่า manager_id จะมีค่าเท่ากับ
employee_id ของบรรทัดก่อนหน้า

สำหรับ start with จะเป็นการกำหนดเงื่อนไขสำหรับ root ของ tree ครับ ถ้าดูจากตัวอย่างข้างบน
ไม่กำหนด start with ก็หมายถึงทุกแถวถูกนำมาใช้เป็น root ทั้งหมด หัวหน้าแผนก1 ก็จะถูกแสดง
สองครั้งอย่างที่เห็น ถ้าเรากำหนด start with เช่น START WITH manager_id is null
ก็หมายถึงเริ่มต้น จากพนักงานที่ไม่มีหัวหน้าเลย (หัวหน้าใหญ่) เท่านั้น แล้วไล่ลงไปเรื่อย ๆ
ผลลัพธ์ ก็จะเหลือเพียง

หัวหน้าส่วน
.....หัวหน้าแผนก1
..........ลูกน้อง1
..........ลูกน้อง2
.....หัวหน้าแผนก2
..........ลูกน้อง3

ถ้าเป็น RDBMS ที่ไม่มีคำสั่ง connect by ( database อื่น ๆ หรือ Oracle ก่อน 9i )
query แบบนี้ก็ต้องใช้การ self join และถ้าจะให้สมบูรณ์ ก็ต้องเป็น recursive self join ด้วย

------------------------------------------------------------------------------------------------------------
order siblings by จะใช้แทน order by ในกรณี hierarchy query ครับ
เนื่องจาก order by จะทำงานหลังสุดเสมอ เพราะฉะนั้นหากเราใช้ order by
ผลลัพธ์ที่จัดเรียงตาม hierarchy ก็จะถูกจัดเรียงใหม่อีกครั้งทั้งหมด จนดูไม่เป็น
hierarchy การใช้ order siblings by จะทำให้การจัดเรียง จะจัดเรียงเฉพาะ
แถวที่อยู่ใน level เดียวกันเท่านั้น

นอกจากนี้เกี่ยวข้องกับ hierarchy query ก็จะมี level ซึ่งเป็น pseudo column
แสดงระดับว่าตอนที่อยู่ลึกลงมากี่ระดับ ตั้งแต่ 1 2 3 เรียงกันไป และก็มี function
SYS_CONNECT_BY_PATH ที่แสดง path ตั้งแต่ root ลงมาจนถึง node ปัจจุบัน
เช่น หัวหน้าส่วน/หัวหน้าแผนก2/ลูกน้อง3 เป็นต้น ครับ

สนใจเรื่องนี้ หาอ่านข้อมูลเพิ่มเติมได้จาก SQL References และถ้าจะทดลองเล่นดู
table emp ใน schema scott เหมาะสมมากครับ
-----------------------------------------------------------------------------------------------------------
เพิ่มเติมอีกเล็กน้อย สำหรับการใช้งานที่น่าสนใจของ connect by
ในแง่ของการ recursive

ทุกคนคงคุ้นเคยกับการใช้ table dual เป็นอย่างดี ว่าเป็น table ที่มี
เพียง 1 แถว 1 column สมมติว่าเราต้องการ table ที่มีจำนวนเท่ากับ
N แถว เราจะทำได้อย่างไร ทำได้ด้วยวิธีนี้ครับ

SQL> select * from ( select level l from dual connect by level <= 8 );

L
----------
1
2
3
4
5
6
7
8

8 rows selected.

ถ้าเป็น database 9i จะต้องซ่อน select level l from dual connect by level <= :N
ไว้ใน subquery แต่ถ้า เป็น 10g ใช้แค่ select level l from dual connect by level <= :N
ก็พอครับ

[jataz2]

credit คุณ siamnobi จากเว็บ narisa