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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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