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

מה זה CTE ב-SQL Server?

CTE (Common Table Expression) הוא ערכת תוצאות חד פעמית שקיימת רק למשך השאילתה . זה מאפשר לנו להתייחס לנתונים בתוך היקף ביצוע של משפט SELECT, INSERT, UPDATE, DELETE, CREATE VIEW או MERGE אחד. היא זמנית מכיוון שלא ניתן לאחסן את התוצאה שלה בשום מקום והיא תאבד ברגע שביצוע השאילתה יושלם. זה הגיע לראשונה עם גרסת SQL Server 2005. DBA תמיד העדיף להשתמש ב-CTE כחלופה לשאילתת משנה/תצוגה. הם עומדים בתקן ANSI SQL 99 ותואמי SQL.

תחביר CTE ב-SQL Server

תחביר CTE כולל שם CTE, רשימת עמודות אופציונלית והצהרה/שאילתה המגדירים את ביטוי הטבלה המשותף (CTE). לאחר הגדרת ה-CTE, נוכל להשתמש בו כתצוגה בשאילתת SELECT, INSERT, UPDATE, DELETE ו-MERGE.

להלן התחביר הבסיסי של CTE ב-SQL Server:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name; 

בתחביר הזה:

• ציינו תחילה את שם ה-CTE שאליו יפנה מאוחר יותר בשאילתה.
• השלב הבא הוא יצירת רשימה של עמודות מופרדות בפסיקים. זה מבטיח שמספר העמודות בארגומנטים של הגדרת CTE ומספר העמודות בשאילתה חייבים להיות זהים. אם לא הגדרנו את עמודות הארגומנטים של CTE, הוא ישתמש בעמודות השאילתה שמגדירות את ה-CTE.
• לאחר מכן, נשתמש במילת המפתח AS אחרי שם הביטוי ולאחר מכן נגדיר משפט SELECT שקבוצת התוצאות שלו מאכלסת את ה-CTE.
• לבסוף, נשתמש בשם CTE בשאילתה כגון SELECT, INSERT, UPDATE, DELETE ומשפט MERGE.

זה צריך לזכור בעת כתיבת הגדרת השאילתה CTE; איננו יכולים להשתמש בסעיפים הבאים:

1. ORDER BY אלא אם כן אתה משתמש גם כסעיף TOP
2. לְתוֹך
3. סעיף OPTION עם רמזים לשאילתה
4. לעיון

התמונה למטה היא הייצוג של הגדרת השאילתה CTE.

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

דוגמא

תן לנו להבין כיצד CTE עובד ב-SQL Server באמצעות דוגמאות שונות. כאן, אנחנו הולכים להשתמש בטבלה ' צרכן ' להפגנה. נניח שטבלה זו מכילה את הנתונים הבאים:

בדוגמה זו, שם ה-CTE הוא לקוחות_בניו יורק , שאילתת המשנה המגדירה את ה-CTE מחזירה את שלוש העמודות שם לקוח, אימייל, ו מדינה . כתוצאה מכך, לקוחות CTE_in_newyork יחזירו את כל הלקוחות המתגוררים במדינת ניו יורק.

לאחר הגדרת ה-CTE customers_in_newyork, התייחסנו אליו ב- בחר הצהרה כדי לקבל את הפרטים של אותם לקוחות שנמצאים בניו יורק.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork; 

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

CTE מרובה

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

CTE מרובים עוזרים לנו לפשט שאילתות מורכבות שבסופו של דבר מצטרפות יחד. לכל יצירה מורכבת היה CTE משלו, אשר לאחר מכן ניתן היה להתייחס אליו ולהצטרף אליו מחוץ לסעיף WITH.

הערה: ניתן להגדיר את הגדרת ה-CTE המרובה באמצעות UNION, UNION ALL, JOIN, INTERSECT או EXCEPT.

התחביר שלהלן מסביר זאת בצורה ברורה יותר:

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name; 

דוגמא

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

בדוגמה זו, הגדרנו את שני שמות ה-CTE לקוחות_בניו יורק ו לקוחות_בקליפורניה . אז ערכת התוצאות של שאילתות המשנה של CTEs אלה מאכלסת את ה-CTE. לבסוף, נשתמש בשמות CTE בשאילתה שתחזיר את כל הלקוחות שנמצאים ב ניו יורק ו מדינת קליפורניה .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California; 

ניו יורק ומדינת קליפורניה.

למה אנחנו צריכים CTE?

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

כמה ממקרי השימוש בו מובאים להלן:

• זה שימושי כאשר אנו צריכים להגדיר טבלה נגזרת מספר פעמים בתוך שאילתה אחת.
• זה שימושי כאשר אנחנו צריכים ליצור חלופה לתצוגה במסד הנתונים.
• זה שימושי כאשר אנחנו צריכים לבצע את אותו חישוב מספר פעמים על מספר רכיבי שאילתה בו זמנית.
• זה שימושי כשאנחנו צריכים להשתמש בפונקציות דירוג כמו ROW_NUMBER(), RANK() ו-NTILE().

כמה מיתרונותיו מובאים להלן:

נינה גופטה

• CTE מקל על תחזוקת הקוד.
• CTE מגביר את קריאות הקוד.
• זה מגדיל את ביצועי השאילתה.
• CTE מאפשר ליישם שאילתות רקורסיביות בקלות.

סוגי CTE ב-SQL Server

SQL Server מחלק את ה-CTE (ביטויי טבלה נפוצים) לשתי קטגוריות רחבות:

1. CTE רקורסיבי
2. CTE לא רקורסיבי

CTE רקורסיבי

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

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

דוגמא

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

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

CTE זה ייתן את הפלט הבא שבו נוכל לראות את ההיררכיה של נתוני העובדים:

CTE לא רקורסיבי

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

חסרונות של CTE

להלן המגבלות של השימוש ב-CTE ב-SQL Server:

• חברי CTE אינם יכולים להשתמש בסעיפים של מילות מפתח כמו Distinct, Group By, Having, Top, Joins וכו'.
• החבר הרקורסיב יכול להפנות ל-CTE פעם אחת בלבד.
• איננו יכולים להשתמש במשתני הטבלה וב-CTE כפרמטרים בפרוצדורות מאוחסנות.
• אנחנו כבר יודעים שניתן להשתמש ב-CTE במקום תצוגה, אבל לא ניתן לקנן CTE, בעוד ש-Views כן.
• מכיוון שזהו רק קיצור דרך לשאילתה או לשאילתת משנה, לא ניתן לעשות בו שימוש חוזר בשאילתה אחרת.
• מספר העמודות בארגומנטים של CTE ומספר העמודות בשאילתה חייבים להיות זהים.