יצירת שאילתא רקורסיבית באמצעות CTE

רקרוסיה היא חזרתיות וכשמדברים על רקורסיה בעולם התכנות, הכוונה היא לתוכנית שמזמנת את עצמה או שמכילה התייחסות לעצמה.

פונקציות ופרוצדורות ב-SQL Server יכולות לקרוא לעצמן אבל במקרים שבהם עלול להיווצר לופ אינסופי, המערכת תעצור אותנו אחרי 32 חזרות.

בפוסט הקודם הסברתי מהן CTE ואיך להשתמש בהן. בפוסט הזה אראה כיצד אפשר להשתמש ב-CTE כדי לבצע שאילתא רקורסיבית.

לצורך ההדגמה אשתמש בטבלת העובדים בבסיס הנתונים HR, שנראית ככה:

טבלת עובדים. לחצו להגדלה

טבלת עובדים. לחצו להגדלה

מה שמעניין בטבלה הזאת הוא שהיא מכילה הפנייה לעצמה. אני אציג חלק קטן יותר מהטבלה כדי שיהיה קל יותר להבין:

כל מנהל הוא גם עובד

כל מנהל הוא גם עובד

מספרי המנהלים (MANAGER_ID) מתייחסים למספרי עובדים (EMPLOYEE_ID) באותה טבלה עצמה. נינה (שורה 2) ולקס (שורה 3) כפופים לסטיבן (שורה 1) אבל לקס הוא גם המנהל של אלכסנדר (שורה 4). תופעה כזאת נקראת "טבלה היררכית" משום שאנחנו משתמשים בסוג כזה של הפנייה כדי לייצג היררכייה בתוך טבלה מסויימת (אבות-בנים, מפקדים-חיילים, מנהלים-עובדים…)

כדי להציג את שמות העובדים לצד שמות המנהלים שלהם אני יכול להשתמש ב-self JOIN, בצורה כזאת:


SELECT emps.EMPLOYEE_ID, emps.FIRST_NAME, emps.MANAGER_ID, boss.FIRST_NAME
FROM EMPLOYEES emps JOIN EMPLOYEES boss
ON emps.MANAGER_ID = boss.EMPLOYEE_ID

אבל מה אם אני רוצה להציג את הרמה ההיררכית של כל עובד בטבלה? לצורך העניין נגדיר "רמה היררכית" כמספר דרגות הניהול של מעובד מסויים ועד המנכ"ל. בדוגמא הזאת למשל, אם המנכ"ל סטיבן נמצא בדרגה אחת אז לקס שעובד ישירות תחתיו נמצא בדרגה השנייה אך אלכסנדר, שעובד תחת לקס, כבר נמצא בדרגה השלישית.

בשלב הראשון נפצל את השאילתא שלנו לשאילתת בסיס, ושאילתא רקורסיבית. שאילתת הבסיס תכיל את הרמה הראשונה שעליה נתבסס. במקרה הזה – המנכ"ל. קל להגדיר את זה מפני שהמנכ"ל הוא העובד היחיד שאין לו מנהל מעליו ולכן אין לו מספר בעמודת המנהל:


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL

החלק השני הוא יצירת השאילתא הרקורסיבית שפשוט תכיל את כל השאר:


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL

עכשוי נשתמש ב-CTE כדי לאחד את שתי השאילתות האלה ל"טבלה" אחת. איחוד של שתי השאילתות האלה (בסיס + רקורסיבית) יביא לנו, באופן די צפוי, את כל הטבלה


WITH empsTable AS
-- Base Query
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
UNION ALL
-- Recursive Query
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL)
SELECT *
FROM empsTable

השלב הבא יהיה להוסיף עמודת מספור שתציג את הרמה ההיררכית של כל עובד. שאילתת הבסיס, שמייצגת את הרמה הראשונה, תציג את המספר 1. שאר הטבלה, בינתיים, תציג את המספר 2.


WITH empsTable AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, 1 as 'EmpLevel'
FROM EMPLOYEES boss
WHERE boss.MANAGER_ID IS NULL
UNION ALL
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, 2
FROM EMPLOYEES emps
WHERE emps.MANAGER_ID IS NOT NULL)
SELECT *
FROM empsTable
שאילתת בסיס מציגה את המספר 1. השאר מציגים 2.

שאילתת בסיס מציגה את המספר 1. השאר מציגים 2.

כדי להשיג את התוצאה הרצוייה ננצל את היכולת של ה-CTE להתייחס לעצמה תוך כדי הגדרתה. נחליף את הערך הסטטי "2" בעמודת EmpLevel בחישוב דינאמי על אותה עמודה עצמה.


WITH empsTable AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, 1 as 'EmpLevel'
FROM EMPLOYEES boss
WHERE boss.MANAGER_ID IS NULL
UNION ALL
SELECT emps.EMPLOYEE_ID, emps.FIRST_NAME, emps.LAST_NAME, emps.MANAGER_ID, empsTable.EmpLevel + 1
FROM EMPLOYEES emps
JOIN empsTable
ON emps.MANAGER_ID = empsTable.EMPLOYEE_ID
WHERE emps.MANAGER_ID IS NOT NULL)
SELECT *
FROM empsTable
ORDER BY EMPLOYEE_ID
עמודת EmpLevel מציגה את מיקום העובד בסולם ההיררכי של החברה.

עמודת EmpLevel מציגה את מיקום העובד בסולם ההיררכי של החברה.