CTE: استعلامات متعددة الخطوات باستخدام NOT

تميل الاستعلامات الحقيقية إلى النمو: ثلاثة JOINs، وGROUP BY، واستعلام فرعي، استعلام فرعي آخر... ويصبح غير قابل للقراءة. CTEs ("الجدول المشترك التعبيرات"، تتيح لك جملة WITH) إعطاء اسم إلى الخطوة المتوسطة واستخدمها كما لو كانت جدولاً في الاستعلام الرئيسي.

بناء الجملة الأساسي

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

CTE صالح ** فقط للاستعلام الذي يتبعه **: فهو غير موجود التنفيذ.

مثال

ابحث عن العملاء الثلاثة الذين أنفقوا أكبر قدر (مجموع الطلبات التي تم تسليمها):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

اقرأ باللغة الإنجليزية: "قم أولاً بحساب الإنفاق لكل عميل، ثم انضم إليه العملاء واتخاذ أعلى 3". بدون CTE ستحتاج إلى مضمنة استعلام فرعي في FROM (قانوني، ولكنه أكثر ضجيجًا).

CTEs متعددة في سلسلة

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

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average لأن average يحتوي دائمًا على صف واحد: يُلحق المنتج الديكارتي total_average بكل صف من النتيجة.

CTE مقابل الاستعلام الفرعي: متى يتم استخدام أي منها

تتألق CTEs عندما:

• يتم استخدام نفس الاستعلام الفرعي مرتين أو أكثر في الاستعلام الرئيسي؛
• تريد جعل البنية واضحة في الخطوات المسماة (التوثيق الذاتي)؛
• إنها الشرط الأساسي لـ CTEs العودية (WITH RECURSIVE، الدرس التالي في هذه الوحدة).

يكون الاستعلام الفرعي المضمن أكثر إيجازًا بالنسبة للخطوة المستخدمة مرة واحدة فقط.

جربه

تمرين المراجعة