0Pricing
SQL Academy · บทเรียน

การทำงานของ CTE แบบเรียกซ้ำ

กรณีฐานและขั้นตอนเรียกซ้ำ

การทำงานของ CTE แบบเรียกซ้ำ เป็นบทเรียน SQL Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 1 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

CTE แบบเรียกซ้ำคืออะไร

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

CTE แบบเรียกซ้ำกำหนดด้วยคีย์เวิร์ด WITH RECURSIVE และเหมาะอย่างยิ่งสำหรับการไล่สำรวจข้อมูลแบบลำดับชั้นหรือคล้ายกราฟ เช่น แผนผังองค์กร ต้นไม้โฟลเดอร์ และโครงสร้างรายการชิ้นส่วนประกอบ

โครงสร้างสองส่วน

CTE แบบเรียกซ้ำทุกชุดมีสองส่วนพอดี โดยคั่นด้วย UNION ALL:

1. กรณีฐาน — SELECT ที่ไม่เรียกซ้ำและส่งคืนแถวเริ่มต้น

2. ขั้นตอนแบบเรียกซ้ำ — SELECT ที่เชื่อม CTE กลับเข้ากับตัวเอง และสร้างแถวในระดับถัดไป

กลไกประมวลผลจะทำขั้นตอนแบบเรียกซ้ำต่อไปและสะสมผลลัพธ์ จนกว่าจะไม่สามารถสร้างแถวใหม่ได้เลย

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

การนับจาก 1 ถึง 5

CTE แบบเรียกซ้ำที่ง่ายที่สุดคือการนับตัวเลข กรณีฐานกำหนดค่าเริ่มต้นเป็น 1 ส่วนขั้นตอนแบบเรียกซ้ำจะเพิ่มค่า 1 ในแต่ละรอบการทำงาน ส่วนคำสั่ง WHERE ภายในขั้นตอนแบบเรียกซ้ำทำหน้าที่เป็น เงื่อนไขสิ้นสุด หากไม่มีส่วนนี้ คิวรีจะทำงานตลอดไป

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

การทำงานทีละขั้น

นี่คือวิธีที่กลไกประมวลผลจัดการ CTE ตัวนับในแต่ละรอบการทำงาน:

รอบการทำงานที่ 0 (กรณีฐาน): ส่งคืน {1}

รอบการทำงานที่ 1: ใช้ขั้นตอนแบบเรียกซ้ำกับ {1} และส่งคืน {2}

รอบการทำงานที่ 2: ใช้ขั้นตอนแบบเรียกซ้ำกับ {2} และส่งคืน {3}

รอบการทำงานที่ 3 และ 4: ส่งคืน {4} แล้วจึงส่งคืน {5}

รอบการทำงานที่ 5: WHERE n < 5 เป็นเท็จสำหรับ n=5 จึงส่งคืนศูนย์แถว คิวรีจบการทำงาน

แถวทั้งหมดที่สะสมไว้ — 1, 2, 3, 4, 5 — คือผลลัพธ์สุดท้าย

การตั้งค่าตารางลำดับชั้น

CTE แบบเรียกซ้ำทำงานได้อย่างโดดเด่นกับตารางที่อ้างอิงตัวเอง มาสร้างตาราง employees ซึ่งพนักงานแต่ละคนมี manager_id ที่ไม่จำเป็นต้องมีค่า และชี้กลับไปยังตารางเดียวกัน

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

การไล่สำรวจลำดับชั้น

ตอนนี้เราสามารถไล่ดูสายการรายงานทั้งหมดโดยเริ่มจาก CEO (Alice, รหัส=1) ได้แล้ว กรณีฐานเลือก Alice ส่วนขั้นตอนแบบเรียกซ้ำจะค้นหาพนักงานทั้งหมดที่มี manager_id ตรงกับรหัสที่มีอยู่ใน CTE แล้ว

ผลลัพธ์จะรวมพนักงานทุกคนที่สามารถเข้าถึงได้จาก Alice ไม่ว่าต้นไม้จะมีความลึกเท่าใด

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

ติดตามเส้นทาง

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

วิธีนี้ช่วยให้แสดงการนำทางแบบแสดงเส้นทางย้อนกลับ หรือตรวจแก้ปัญหาลำดับชั้นที่ลึกได้ง่าย

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

จำกัดความลึกของการเรียกซ้ำ

ข้อมูลที่ลึกหรือมีวงจรอาจทำให้ CTE แบบเรียกซ้ำทำงานเป็นเวลานานมาก แนวทางที่ปลอดภัยสองประการคือ:

1. ติดตามความลึกและเพิ่มส่วนคำสั่ง WHERE — WHERE depth < 10 ช่วยให้มั่นใจได้ว่าจะไม่ลงไปเกิน 10 ระดับ

2. ใช้คอลัมน์ตรวจจับวงจร — ฐานข้อมูลบางระบบ (PostgreSQL 14+) มีรูปแบบไวยากรณ์ CYCLE สำหรับตรวจจับการเยี่ยมชมโหนดซ้ำโดยอัตโนมัติ

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

UNION เทียบกับ UNION ALL ใน CTE แบบเรียกซ้ำ

ขั้นตอนแบบเรียกซ้ำมักใช้ UNION ALL แทน UNION เกือบทุกครั้ง เหตุผลมีดังนี้:

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

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

การสร้างลำดับวันที่

CTE แบบเรียกซ้ำยังมีประโยชน์สำหรับการสร้างลำดับวันที่ ตัวอย่างนี้สร้างวันที่ทุกวันในสัปดาห์ที่กำหนด ซึ่งเป็นรูปแบบที่มักใช้สร้างรายงานปฏิทินหรือเติมช่องว่างในข้อมูลอนุกรมเวลา

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

ค้นหาผู้ใต้บังคับบัญชาทั้งหมดของผู้จัดการหนึ่งคน

คุณสามารถกำหนดกรณีฐานด้วยโหนดใดโหนดหนึ่งโดยเฉพาะได้ ไม่จำเป็นต้องเป็นรากเท่านั้น ในที่นี้เราเริ่มจาก Bob (รหัส=2) และค้นหาทุกคนที่รายงานตรงหรือโดยอ้อมต่อเขา

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

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

ตรวจสอบความเข้าใจอย่างรวดเร็ว

ตรวจสอบความเข้าใจของคุณเกี่ยวกับการทำงานของ CTE แบบเรียกซ้ำ

ทบทวนบทเรียน

ในบทเรียนนี้ คุณได้เรียนรู้วิธีการทำงานของ CTE แบบเรียกซ้ำ:

โครงสร้าง: CTE แบบเรียกซ้ำทุกชุดมี กรณีฐาน (แถวเริ่มต้น) ที่เชื่อมกับ ขั้นตอนแบบเรียกซ้ำ (SELECT ที่อ้างอิงตัวเอง) ด้วย UNION ALL

การสิ้นสุด: กลไกประมวลผลจะทำขั้นตอนแบบเรียกซ้ำซ้ำไปเรื่อย ๆ และสะสมผลลัพธ์ จนกว่าขั้นตอนนั้นจะส่งคืนศูนย์แถว

การใช้งานทั่วไป: ไล่ดูแผนผังองค์กรและต้นไม้โฟลเดอร์ สร้างลำดับตัวเลขหรือวันที่ คำนวณเส้นทาง และค้นหาโหนดทั้งหมดในต้นไม้ย่อย

เคล็ดลับด้านความปลอดภัย: ใส่เงื่อนไขสิ้นสุด (ขีดจำกัดความลึกหรือเงื่อนไขป้องกันวงจร) เสมอ และเลือกใช้ UNION ALL แทน UNION เพื่อประสิทธิภาพที่ดีกว่า

คำถามที่พบบ่อย

บทเรียน “การทำงานของ CTE แบบเรียกซ้ำ” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “การทำงานของ CTE แบบเรียกซ้ำ” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “การทำงานของ CTE แบบเรียกซ้ำ”

กรณีฐานและขั้นตอนเรียกซ้ำ คุณปฏิบัติ SQL Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Academy หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 1 จากทั้งหมด 4 บทเรียน

บทเรียน “การทำงานของ CTE แบบเรียกซ้ำ” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Academy นี้ได้ไหม

ได้ บทเรียน SQL Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. การทำงานของ CTE แบบเรียกซ้ำ
  2. การเดินผ่านต้นไม้หมวดหมู่
  3. การสร้างชุดและลำดับ
  4. การหลีกเลี่ยงลูปไม่รู้จบ
← กลับไปที่ SQL Academy