تنظيم الاستعلامات باستخدام تعبيرات الجداول المشتركة

مكتمل

عند العمل مع استعلامات معقدة، غالبا ما تحتاج إلى تقسيم المنطق إلى أجزاء قابلة للإدارة أو الإشارة إلى نفس الاستعلام الفرعي عدة مرات. توفر التعبيرات الشائعة للجداول (CTEs) طريقة لتعريف مجموعات نتائج مؤقتة مسماة توجد فقط أثناء استعلام واحد، مما يجعل كودك أكثر قابلية للقراءة وسهولة في الصيانة.

فهم بناء جملة CTE

يتم تعريف تعبير الجدول المشترك باستخدام الجملة WITH ، يليه اسم CTE، وقائمة أعمدة اختيارية، واستعلام يحدد مجموعة النتائج. يمكن بعد ذلك الإشارة إلى CTE في العبارة التالية SELECT، INSERT، ، UPDATEأو DELETE الجملة.

WITH CTE_Name (Column1, Column2)
AS
(
    -- CTE query definition
    SELECT Column1, Column2
    FROM SomeTable
    WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;

تقدم CTEs عدة مزايا مقارنة بالجداول المشتقة والاستعلامات الفرعية:

• تحسين قابلية القراءة: تصبح الاستعلامات المعقدة أسهل في الفهم عند تقسيمها إلى أقسام منطقية مسماة
• القدرة على الإشارة الذاتية: يمكن لتقنيات CTE التكرارية الإشارة إلى نفسها، مما يمكن من التنقل الهرمي للبيانات
• المراجع المتعددة: الإشارة إلى نفس CTE عدة مرات في الاستعلام الخارجي دون إعادة تعريفه
• التصميم المعياري: بناء استعلامات معقدة تدريجيا عن طريق تعريف عدة CTEs

‏‫ملاحظة‬

CTEs هي مجموعات نتائج مؤقتة توجد فقط أثناء تنفيذ الاستعلام. على عكس الجداول التقليدية، فهي لا تستمر بعد العبارة التي تستخدمها ولا تتطلب تنظيفا صريحا.

إنشاء CTEs غير تكرارية

تعرف CTEs غير التكرارية مجموعة نتائج بناء على استعلام مباشر لا يشير إلى نفسه. هذا النمط مفيد لتبسيط الروابط المعقدة، أو تفكيك الحسابات متعددة الخطوات، أو تحسين تنظيم الكود.

المثال التالي يستخدم CTE لحساب مقاييس المبيعات قبل الانضمام إلى معلومات المنتج:

WITH SalesSummary AS
(
    SELECT 
        ProductID,
        SUM(OrderQty) AS TotalQuantity,
        SUM(LineTotal) AS TotalRevenue,
        COUNT(DISTINCT SalesOrderID) AS OrderCount
    FROM SalesLT.SalesOrderDetail
    GROUP BY ProductID
)
SELECT 
    p.Name AS ProductName,
    p.ProductNumber,
    p.ListPrice,
    ss.TotalQuantity,
    ss.TotalRevenue,
    ss.OrderCount,
    ss.TotalRevenue / NULLIF(ss.TotalQuantity, 0) AS AverageUnitPrice
FROM SalesLT.Product AS p
INNER JOIN SalesSummary AS ss
    ON p.ProductID = ss.ProductID
ORDER BY ss.TotalRevenue DESC;

يقوم هذا الاستعلام أولا بإنشاء CTE يسمى SalesSummary يجمع تفاصيل الطلب حسب المنتج، ويحسب إجمالي الكمية المباعة، إجمالي الإيرادات، وعدد الطلبات. ثم يربط الاستعلام الرئيسي هذا النموذج الاستهلاكي بالجدول Product لعرض أسماء المنتجات إلى جانب مؤشرات المبيعات. تمنع الدالة NULLIF القسمة على الصفر عند حساب متوسط سعر الوحدة.

يمكنك تعريف عدة CTEs في جملة واحدة WITH عن طريق فصلها بفواصل. يمكن لتقنيات CTE اللاحقة الإشارة إلى النماذج السابقة، مما يمكن التحويل التدريجي للبيانات:

WITH CategorySales AS
(
    SELECT 
        p.ProductCategoryID,
        SUM(sod.LineTotal) AS CategoryRevenue
    FROM SalesLT.Product AS p
    INNER JOIN SalesLT.SalesOrderDetail AS sod
        ON p.ProductID = sod.ProductID
    GROUP BY p.ProductCategoryID
),
RankedCategories AS
(
    SELECT 
        ProductCategoryID,
        CategoryRevenue,
        RANK() OVER (ORDER BY CategoryRevenue DESC) AS RevenueRank
    FROM CategorySales
)
SELECT 
    pc.Name AS CategoryName,
    rc.CategoryRevenue,
    rc.RevenueRank
FROM RankedCategories AS rc
INNER JOIN SalesLT.ProductCategory AS pc
    ON rc.ProductCategoryID = pc.ProductCategoryID
WHERE rc.RevenueRank <= 5;

نصيحة

عند بناء استعلامات تحتوي على عدة CTEs، ابدأ بأكثر تحويلات البيانات تفصيلا وقم بتجميع أو تصفية تدريجيا في CTEs التالية. هذا النهج يجعل التصحيح أسهل لأنك تستطيع اختبار كل CTE بشكل مستقل.

إنشاء CTEs تكرارية

تشير CTEs التكرارية إلى هياكل بيانات هرمية أو شبيهة بالرسوم البيانية للعمليات الهرمية. يتكون CTE التكراري من جزأين: عضو مرساة يوفر مجموعة النتائج الأولية، وعضو تكراري يشير إلى CTE للبناء على النتائج السابقة.

النحوية العامة لتعديل CTE التكراري هي:

WITH RecursiveCTE AS
(
    -- Anchor member: starting point
    SELECT columns
    FROM table
    WHERE starting_condition
    
    UNION ALL
    
    -- Recursive member: references the CTE
    SELECT columns
    FROM table
    INNER JOIN RecursiveCTE
        ON join_condition
)
SELECT * FROM RecursiveCTE;

حالة استخدام شائعة هي التنقل في التسلسل الهرمي التنظيمي. خذ جدول موظف حيث لكل موظف مدير:

WITH EmployeeHierarchy AS
(
    -- Anchor: Start with top-level managers (no manager)
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ManagerID,
        0 AS Level,
        CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS HierarchyPath
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive: Find employees who report to previously found employees
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.HierarchyPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
    FROM HumanResources.Employee AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Level,
    HierarchyPath
FROM EmployeeHierarchy
ORDER BY HierarchyPath;

مهم

يمكن أن تسبب CTEs العودية حلقات لا نهائية إذا لم يتم تحقيق شرط الإنهاء أبدا. يحد SQL Server من التكرار إلى 100 مستوى بشكل افتراضي. يستخدم OPTION (MAXRECURSION n) لتغيير هذا الحد، حيث n هو أقصى عمق عودية (0 للغير محدود).

توليد تسلسلات باستخدام CTEs تكرارية

تتفوق CTEs العودية في توليد تسلسلات من الأعداد أو التواريخ دون الحاجة إلى جدول أرقام فعلي. هذه التقنية مفيدة لإنشاء نطاقات تواريخ، أو سد الفجوات في البيانات، أو توليد بيانات الاختبار:

-- Generate a sequence of dates for the current month
WITH DateSequence AS
(
    -- Anchor: Get the first day of the current month
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue
    
    UNION ALL
    
    -- Recursive: Add one day until we reach the end of the month
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < EOMONTH(GETDATE())
)
-- Output each date with its day name
SELECT DateValue, DATENAME(WEEKDAY, DateValue) AS DayName
FROM DateSequence
OPTION (MAXRECURSION 31);  -- Allow up to 31 iterations (max days in a month)

يمكنك دمج التسلسلات المولدة مع البيانات الفعلية لتحديد الفجوات أو إنشاء تقارير ملخصة:

-- Generate a numbers table from 1 to 1000
WITH Numbers AS
(
    -- Anchor: Start with 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive: Increment until we reach 1000
    SELECT n + 1 FROM Numbers WHERE n < 1000
),
-- Convert numbers to dates for the entire year
DateRange AS
(
    SELECT DATEADD(DAY, n - 1, '2024-01-01') AS OrderDate
    FROM Numbers
    WHERE DATEADD(DAY, n - 1, '2024-01-01') <= '2024-12-31'
)
-- Count orders for each date, showing 0 for dates with no orders
SELECT 
    dr.OrderDate,
    COALESCE(COUNT(soh.SalesOrderID), 0) AS OrderCount
FROM DateRange AS dr
LEFT JOIN SalesLT.SalesOrderHeader AS soh
    ON CAST(soh.OrderDate AS DATE) = dr.OrderDate
GROUP BY dr.OrderDate
ORDER BY dr.OrderDate
OPTION (MAXRECURSION 366);  -- Allow up to 366 iterations (leap year)

استخدم CTEs مع بيانات تعديل البيانات

يمكن استخدام CTEs مع INSERT، ، و DELETE العبارات، وليس فقط SELECTUPDATE. هذه القدرة مفيدة عندما يتطلب منطق التعديل تصفية معقدة أو حسابات:

-- Update using a CTE to identify target rows
WITH DiscontinuedProducts AS
(
    SELECT ProductID
    FROM SalesLT.Product
    WHERE SellEndDate < DATEADD(YEAR, -2, GETDATE())
        AND ProductID NOT IN (
            SELECT DISTINCT ProductID 
            FROM SalesLT.SalesOrderDetail
            WHERE ModifiedDate > DATEADD(YEAR, -1, GETDATE())
        )
)
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts);

يستخدم هذا الاستعلام CTE لتحديد المنتجات التي يجب وسمها كمنتجة متوقفة. يحدد اختبار CTE المنتجات التي كان تاريخ انتهاء بيعها قبل أكثر من عامين ولم تظهر في أي تفاصيل طلبية تم تعديلها خلال العام الماضي. UPDATE ثم يحدد البيان ل DiscontinuedDate ل لتلك الضربات. بفصل منطق الاختيار إلى CTE، يصبح الاستعلام أسهل في القراءة والاختبار بشكل مستقل.

لمزيد من المعلومات حول التعبيرات الشائعة للجداول، راجع WITH common_table_expression (Transact-SQL)والاستعلامات العودية باستخدام تعبيرات الجداول الشائعة.