ב-SQL, סעיף ה-WITH ידוע גם בשם CTE. זוהי תכונה רבת עוצמה המאפשרת לנו ליצור ערכות תוצאות זמניות בתוך שאילתה. תפקיד מרכזי אחד של CTEs הוא פישוט השאילתות המורכבות לשאילתות משנה קטנות יותר וניתנות לשימוש חוזר. זה עוזר להפוך את הקוד ליותר קריא וניתן לתחזוקה בטווח הארוך.
הצטרפו אלינו למדריך זה כשאנו חוקרים את פעולתם של ביטויי הטבלה הנפוצים באמצעות סעיף WITH והפונקציונליות הנתמכת.
דרישות:
למטרות הדגמה, נשתמש בדברים הבאים:
- MySQL גרסה 8.0 ומעלה
- מסד נתונים לדוגמה של Sakila
עם עמידה בדרישות הנתונות, נוכל להמשיך ללמוד עוד על CTEs וסעיף WITH.
SQL עם סעיף
פסקת ה-WITH מאפשרת לנו להגדיר קבוצת תוצאות זמנית אחת או יותר אשר ידועות כביטויי טבלה נפוצים.
אנו יכולים להתייחס ל-CTEs המתקבלים בשאילתה הראשית כמו כל טבלה או ערכת תוצאות אחרת. זה ממלא תפקיד מכריע ביצירת שאילתות SQL מודולריות.
למרות שהתחביר של CTE עשוי להשתנות מעט בהתאם לדרישות שלך, התחביר הבא מציג את התחביר הבסיסי של CTE ב-SQL:
WITH cte_name (עמודה1, עמודה2, ...) AS (
-- שאילתת CTE
בחר ...
מ ...
איפה ...
)
-- שאילתה ראשית
בחר ...
מ ...
JOIN cte_name ON...
איפה ...
אנו מתחילים עם מילת המפתח WITH אשר אומרת למסד הנתונים של SQL שאנו רוצים ליצור ולהשתמש ב-CTE.
לאחר מכן, אנו מציינים את השם עבור ה-CTE המאפשר לנו להפנות אליו בשאילתות אחרות.
אנו מציינים גם רשימה אופציונלית של שמות עמודות אם ה-CTE כולל את כינויי העמודות.
לאחר מכן, נמשיך להגדיר את שאילתת ה-CTE. זה מכיל את כל המשימות או הנתונים שה-CTE מבצע מוקפים בזוג סוגריים.
לבסוף, אנו מציינים את השאילתה הראשית המתייחסת ל-CTE.
שימוש לדוגמה:
אחת הדרכים הטובות ביותר להבין כיצד להשתמש ולעבוד עם CTEs היא להסתכל על דוגמה מעשית.
קחו למשל את מסד הנתונים לדוגמה של Sakila. נניח שאנו רוצים למצוא את 10 הלקוחות המובילים עם המספר הגבוה ביותר של השכרות.
תסתכל על ה-CTE המוצג הבא.
שימוש בסעיף SQL WITH כדי למצוא את 10 הלקוחות המובילים עם ספירת השכירות הגבוהה ביותר:
עם CustomerRentals AS (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
מלקוח ג
JOIN השכרה r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
בחר *
מ-CustomerRentals
ORDER BY rental_count DESC
LIMIT 10;
בדוגמה הנתונה, אנו מתחילים בהגדרת CTE חדש באמצעות מילת המפתח WITH ואחריה השם שברצוננו להקצות ל-CTE. במקרה זה, אנו קוראים לזה 'השכרת לקוחות'.
בתוך גוף CTE, אנו מחשבים את ספירת השכירות עבור כל לקוח על ידי הצטרפות לטבלת הלקוחות וההשכרות.
לבסוף, בשאילתה הראשית, אנו בוחרים את כל העמודות מה-CTE, מזמינים את התוצאות על סמך ספירת ההשכרה (סדר יורד), ומגבילים את הפלט רק ל-10 השורות העליונות.
זה מאפשר לנו להביא את הלקוחות עם המספר הגבוה ביותר של השכרות כפי שמוצג בפלט הבא:
CTEs רקורסיביים
במקרים אחרים, ייתכן שאתה מתמודד עם מבני נתונים היררכיים. כאן נכנסים לתמונה ה-CTEs הרקורסיב.
ניקח לדוגמה מקרה שבו אנו רוצים לנווט בארגון ההיררכי או לייצג מבנה דמוי עץ. אנו יכולים להשתמש במילת המפתח WITH RECURSIVE כדי ליצור CTE רקורסיבי.
מכיוון שאין נתונים היררכיים שאנו יכולים להשתמש בהם במסד הנתונים של Sakila כדי להדגים CTE רקורסיבי, הבה נגדיר דוגמה בסיסית.
מחלקת יצירת טבלה (department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(255) NOT NULL,
parent_department_id INT,
FOREIGN KEY (parent_department_id) REFERENCES department(department_id)
);
INSERT INTO המחלקה (שם_מחלקה, מזהה_מחלקה_אב)
ערכים
('תאגיד', NULL),
('פיננסים', 1),
('HR', 1),
('חשבונאות', 2),
'גיוס', 3),
('שכר', 4);
במקרה זה, יש לנו טבלת 'מחלקה' לדוגמה עם כמה נתונים אקראיים. כדי למצוא את המבנה ההיררכי של המחלקות, נוכל להשתמש ב-CTE רקורסיבי באופן הבא:
עם ReCURSIVE DepartmentHierarchy AS (SELECT department_id, department_name, parent_department_id
מהמחלקה
WHERE parent_department_id הוא NULL
איחוד הכל
בחר d.department_id, d.department_name, d.parent_department_id
ממחלקה ד
JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
בחר *
FROM DepartmentHierarchy;
במקרה זה, ה-CTE הרקורסי מתחיל במחלקות שיש להן NULL 'parent_department_id' (מחלקות שורש) ומחזיר באופן רקורסיבי את מחלקות הילד.
סיכום
במדריך זה, למדנו על התכונות הבסיסיות והשימושיות ביותר במסדי נתונים של SQL כמו ביטויי הטבלה הנפוצים על ידי הבנה כיצד לעבוד עם מילת המפתח WITH.