SQL Interview Prep · บทเรียน

ท่องไปในแผนผังองค์กร

ไล่ตามลำดับชั้นพนักงานกับผู้จัดการได้ลึกเท่าใดก็ได้

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

คำถามเกี่ยวกับแผนผังองค์กร

"กำหนดตาราง employees ที่มี id name และ manager_id ให้แสดงทุกคนที่อยู่ใต้ผู้จัดการที่กำหนดไว้ในทุกระดับ" นี่เป็นคำถามสัมภาษณ์เกี่ยวกับ CTE แบบเรียกซ้ำที่พบบ่อยที่สุดข้อหนึ่ง

ตารางนี้อ้างอิงกลับมายังตัวเอง: manager_id ชี้กลับไปยัง id ของอีกแถวหนึ่ง ในบทเรียนนี้คุณจะเดินตามความสัมพันธ์ทั้งลงด้านล่าง (ผู้ใต้บังคับบัญชา) และขึ้นด้านบน (สายการบังคับบัญชา)

ตารางตัวอย่าง

ลองนึกภาพข้อมูลนี้ CEO ไม่มีผู้จัดการเป็น NULL ส่วนคนอื่น ๆ รายงานต่อผู้บังคับบัญชาขึ้นไปตามสาย

1 Ada (ผู้จัดการ NULL)
2 Ben (ผู้จัดการ 1)
3 Cleo (ผู้จัดการ 1)
4 Dan (ผู้จัดการ 2)
5 Eve (ผู้จัดการ 4)

ดังนั้นลำดับความลึกคือ: Ada → Ben → Dan → Eve โปรดจำภาพนี้ไว้ขณะเดินตามโครงสร้าง

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

เดินลงด้านล่างจากผู้จัดการ

หากต้องการแสดงผู้ใต้บังคับบัญชาทั้งหมดใต้ผู้จัดการที่เลือก สมาชิกตั้งต้นจะเลือกผู้จัดการคนนั้น (หรือผู้ใต้บังคับบัญชาโดยตรงของเขา) และสมาชิกแบบเรียกซ้ำจะเดินตาม manager_id ลงด้านล่าง

ในที่นี้เราเริ่มจาก Ben (รหัส 2) แล้วรวบรวมทุกคนที่อยู่ใต้เขา

WITH RECURSIVE subtree AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subtree s ON e.manager_id = s.id
)
SELECT name, depth FROM subtree ORDER BY depth;

การอ่านผลลัพธ์

คิวรีด้านบนคืนค่า Ben ที่ระดับความลึก 1, Dan ที่ระดับความลึก 2 และ Eve ที่ระดับความลึก 3 สมาชิกตั้งต้นสร้าง Ben รอบการทำซ้ำแรกพบ Dan (ซึ่งมีผู้จัดการคือ Ben) รอบที่สองพบ Eve (ซึ่งมีผู้จัดการคือ Dan) รอบที่สามไม่พบใคร การเรียกซ้ำจึงหยุดลง

หากผู้สัมภาษณ์ถามว่า "Eve อยู่ใต้ Ben ลึกกี่ระดับ" คอลัมน์ depth จะตอบได้โดยตรง: 3 ลบ 1 เท่ากับ 2 ระดับ

เดินขึ้นด้านบนไปยัง CEO

คำถามกลับด้านนี้ก็พบบ่อยไม่แพ้กัน: "แสดงสายการบังคับบัญชาทั้งหมดของ Eve ขึ้นไปจนถึง CEO" ให้สลับทิศทางการเชื่อมตาราง โดยสมาชิกแบบเรียกซ้ำจะตาม manager_id ของแถวปัจจุบันขึ้นไปยังแถวแม่

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS lvl
    FROM employees WHERE id = 5
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.lvl + 1
    FROM employees e
    JOIN chain c ON e.id = c.manager_id
)
SELECT name, lvl FROM chain ORDER BY lvl;

ลงกับขึ้น: สลับการเชื่อมตาราง

ความแตกต่างด้านโครงสร้างเพียงอย่างเดียวระหว่างการเดินลงกับการเดินขึ้นคือเงื่อนไขการเชื่อมตาราง:

ลง (ค้นหาผู้ใต้บังคับบัญชา): e.manager_id = cte.id — จับคู่พนักงานที่มีผู้จัดการเป็นแถวที่เรามีอยู่แล้ว
ขึ้น (ค้นหาผู้จัดการ): e.id = cte.manager_id — จับคู่พนักงานที่มีรหัสตรงกับผู้จัดการของแถวปัจจุบัน

การอธิบายการสลับทิศทางนี้ได้อย่างชัดเจนจะสร้างความประทับใจให้ผู้สัมภาษณ์

การสร้างต้นไม้แบบเยื้องระดับ

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

WITH RECURSIVE org AS (
    SELECT id, name, 1 AS depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT REPEAT('  ', depth - 1) || name AS tree
FROM org
ORDER BY depth;

การสะสมเส้นทาง

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

WITH RECURSIVE org AS (
    SELECT id, name, CAST(name AS VARCHAR(500)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, o.path || ' / ' || e.name
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT name, path FROM org ORDER BY path;

การนับผู้ใต้บังคับบัญชาของแต่ละผู้จัดการ

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

ในที่นี้เรานับผู้ใต้บังคับบัญชาโดยอ้อมทั้งหมดใต้ Ada (CEO) ด้วยการเดินทั้งต้นไม้แล้วนับแถวที่อยู่ใต้ราก

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT COUNT(*) - 1 AS total_reports FROM org;

ข้อผิดพลาดที่พบบ่อย

ระวังกับดักเหล่านี้ที่ผู้สัมภาษณ์มักตั้งไว้:

ทิศทางการเชื่อมตารางผิด — การใช้ e.manager_id = cte.id ทั้งที่ต้องการเดินขึ้น จะคืนค่าชุดข้อมูลผิด
ลืมตัวกรองของสมาชิกตั้งต้น — หากละเว้น WHERE id = X ระบบจะสร้างข้อมูลตั้งต้นจากทุกแถวและคืนค่าทั้งป่า
ระดับความลึกคลาดหนึ่ง — ตัดสินใจว่าสมาชิกตั้งต้นเป็นระดับ 0 หรือ 1 แล้วใช้ให้สอดคล้องกัน

ทำไมไม่ใช้การเชื่อมตารางกับตัวเอง

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

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

ตรวจสอบความเข้าใจ

ตรวจสอบว่าคุณสามารถสลับทิศทางการเดินตามโครงสร้างได้

สรุปทบทวน

การไล่ตามแผนผังองค์กรคือโครงสร้างแบบเรียกซ้ำที่นำไปใช้กับตารางซึ่งอ้างอิงตัวเอง:

ลง: เริ่มจากผู้จัดการ แล้วเชื่อม e.manager_id = cte.id.
ขึ้น: เริ่มจากพนักงาน แล้วเชื่อม e.id = cte.manager_id.
เก็บค่า depth สำหรับการเยื้อง และ path สำหรับห่วงโซ่ทั้งหมด
การเรียกซ้ำรองรับความลึกที่ไม่ทราบล่วงหน้า ซึ่งการเชื่อมตารางกับตัวเองทำไม่ได้

ถัดไป: ใช้การเรียกซ้ำเพื่อสร้างชุดตัวเลขและชุดวันที่

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

บทเรียน “ท่องไปในแผนผังองค์กร” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “ท่องไปในแผนผังองค์กร”

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

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

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

บทเรียน “ท่องไปในแผนผังองค์กร” ใช้เวลานานแค่ไหน

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

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

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

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

← กลับไปที่ SQL Interview Prep