שאילתות CTE ב-SQL Server

CTE הן ראשי התיבות של Common Table Expression, ביטויים המאפשרים לנו ליצור מעין טבלה זמנית שמתקיימת רק לפרק זמן הריצה של אותה שאילתא המגדירה אותה. צורת הכתיבה הזאת נכנסה לשימוש החל מגרסת SQL Server 2005 והמבנה הבסיסי שלה נראה ככה:

    
WITH [CTE_Name]
AS
(SELECT statement)
SELECT [SomeColumn]
FROM [CTE_Name]

ניתן לפנות אל CTE כמו לטבלה רגילה ו-CTE אף יכולה לפנות לעצמה אך הפנייה חייבת לבוא מייד אחרי הגדרת ה-CTE עצמה כחלק מאותו בלוק של T-SQL, שכן הטבלה הזמנית אינה נשמרת בשום מקום. CTE יכולה להיות שימושית במגוון רחב של מקרים כמו למשל פישוט של שאילתא והפיכתה לקריאה יותר, פריסה מובנית של כמה פעולות חישוביות, התייחסות לעמודה באופן חיצוני או יצירת רקורסיה.

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

    
SELECT [EMPLOYEE_ID], 
[SALARY], 
RANK() OVER (PARTITION BY [DEPARTMENT_ID] ORDER BY [SALARY] DESC ) AS 'SalRank'
FROM [EMPLOYEES]

אבל אם נרצה למיין את 3 המרוויחים הגבוהים מכל מחלקה ניתקל בבעייה מפני שאסור לשים פונקציות אנליטיות בתוך סעיף ה-WHERE ובגלל הסדר שבו SQL Server מנתח את השאילתות שהוא מריץ, לא נוכל גם לסנן ע"פ הכינוי של העמודה. CTE יכול להיות פתרון טוב במקרה כזה, שכן הוא מאפשר לנו להתייחס לכינוי עמודת הדירוג "מבחוץ":

    
WITH SalRankCTE
AS
(SELECT [EMPLOYEE_ID], 
[SALARY], 
RANK() OVER (PARTITION BY [DEPARTMENT_ID] ORDER BY [SALARY] DESC ) AS 'SalRank'
FROM [EMPLOYEES])
SELECT *
FROM SalRankCTE
WHERE [SalRankCTE].[SalRank] < 4

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

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