CTE در SQL Server چیست؟
Common Table Expression (CTE) در SQL Server یک عبارت موقت (شبیه جدول موقت) است که تنها در طول اجرای یک کوئری معتبر است. این ابزار قدرت ساختن کوئری‌های پیچیده را ساده و قابل‌فهم می‌کند و می‌توان از آن در دستورات SELECT، INSERT، UPDATE یا DELETE استفاده کرد.
ساختار کلی یک CTE
WITH YourCTEName (Column1, Column2, ...)
AS (
-- یک SELECT که نتیجه آن CTE را می‌سازد
SELECT ...
FROM ...
WHERE ...
)
-- سپس:
SELECT * FROM YourCTEName;
WITHبرای شروع CTEYourCTEName: نام، مانند جدول موقتفهرست ستون‌ها: انتخاب در داخل CTE
داخل پرانتز، یک SELECT اصلی که نتیجه را تامین می‌کند.
انواع CTE
1. نا-بازگشتی (Non-Recursive)
برای شکستن کوئری‌های متوسط و خواناتر کردن آن‌ها استفاده می‌شود:
WITH ITStaff AS (
SELECT EmployeeID, FirstName, Salary
FROM Employees
WHERE Department = 'IT'
)
SELECT * FROM ITStaff;
این کوئری تمام پرسنل IT را فیلتر کرده و در CTE ذخیره می‌کند .
2. بازگشتی (Recursive)
برای داده‌های سلسله‌مراتبی، مانند ساختار مدیر–کارمند:
WITH RecursiveEmployeeCTE AS (
SELECT EmployeeID, FirstName, LastName, ManagerID
FROM Employees
WHERE ManagerID IS NULL -- نقطه آغاز
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
FROM Employees e
JOIN RecursiveEmployeeCTE r
ON e.ManagerID = r.EmployeeID
)
SELECT * FROM RecursiveEmployeeCTE;
ابتدا مدیر ریشه را انتخاب کرده، سپس کارکنان زیرمجموعه وی را بازگشتی واکشی می‌کند .
مثال‌های پیشرفته
شمارش کارکنان به‌ازای هر دپارتمان
WITH DepartmentEmployeeCountCTE AS (
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
)
SELECT * FROM DepartmentEmployeeCountCTE;
اینجا از CTE برای گروه‌بندی و شمارش استفاده شده است.
میانگین حقوق کارکنان هر دپارتمان
WITH DepartmentAvgSalaryCTE AS (
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
)
SELECT * FROM DepartmentAvgSalaryCTE;
متوسط حقوق هر بخش را محاسبه کرده و در CTE ذخیره می‌کند .
نکات کاربردی و محدویت‌ها
CTE فقط برای یک SELECT/INSERT/UPDATE/DELETE معتبر است و نتایج آن مجدداً اجرا می‌شوند (غیر مادی‌سازی) Microsoft Learn.
عدم وجود ORDER BY بدون TOP یا OFFSET/FETCH در تعریف CTE Microsoft Learn.
اگر بازگشتی دارید، باید حداقل یک anchor و یک قسمت Recursive تعریف کنید و UNION ALL بین‌شان باشد .
برای کنترل عمق recursion، از OPTION (MAXRECURSION n) استفاده کنید تا حلقه بی‌نهایت جلوگیری شود.
مزایا و کاربردهای اصلی
خوانایی و نگهداشت آسان: کوئری‌های پیچیده قابل تقسیم‌بندی.
تشخیص سریع خطا: با تست جداگانه هر CTE راحت‌تر خطاها را پیدا می‌کنیم.
پشتیبانی از داده سلسله‌مراتبی: بسیار مناسب برای ساختارهای tree.
جایگزین view یا subquery: بدون نیاز به ساخت اشیا دائمی.
قابلیت بازگشتی: امکان نوشتن جوین‌های سلسله‌مراتبی قوی.
دیدگاهتان را بنویسید