الدرس 24 · الوحدة 11: تطوير استعلامات محسّنة باستخدام البرمجة الإجرائية

الاستعلامات الفرعية و CTE

قسّم السؤال الكبير إلى خطوات مسماة، وامشِ في الشجرة بلا حلقات. ⏱ نحو 45 دقيقة.

بعد هذا الدرس ستستطيع أن:

تكتب استعلامات فرعية عددية و IN و EXISTS.
تتجنب الاستعلام المترابط المكلف و NOT IN مع NULL.
تقسم الاستعلام المعقد بـ WITH وتعدّل البيانات بـ RETURNING.
تستعلم عن الهرميات بـ WITH RECURSIVE.

١الاستعلام الفرعي: سؤال داخل سؤال

النوع	يُرجع	مثال
عددي Scalar	قيمة واحدة	`WHERE price > (SELECT avg(price) FROM products)`
قائمة مع IN	عمودًا واحدًا	`WHERE id IN (SELECT customer_id FROM orders)`
`EXISTS`	صحيح/خطأ: هل يوجد صف؟	`WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)`
مترابط Correlated	يُعاد لكل صف خارجي	يشير إلى عمود من الاستعلام الخارجي

query.sql

-- عملاء لم يطلبوا قطSELECT name, city FROM customers cWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

النتيجة · صف واحد

name	city
ماجد	أبها

NOT EXISTS أسلم من NOT IN: إن احتوت القائمة الفرعية NULL واحدًا، أرجع NOT IN لا شيء.

الاستعلام المترابط في SELECT يُنفَّذ مرة لكل صف، وهو «حلقة مخفية». على الجداول الكبيرة استبدله بربط وتجميع (الدرس 22).

٢WITH: سمِّ الخطوات

الاستعلام المعقد يصبح مقروءًا حين تقسمه إلى خطوات مسماة بـ WITH (تعبير الجدول المشترك CTE):

query.sql

WITH revenue AS (                       -- الخطوة 1: إيراد كل طلب  SELECT order_id, sum(qty * unit_price) AS total  FROM order_items GROUP BY order_id),by_customer AS (                         -- الخطوة 2: إيراد كل عميل  SELECT o.customer_id, sum(r.total) AS spent  FROM orders o JOIN revenue r ON r.order_id = o.id  WHERE o.status <> 'cancelled'  GROUP BY o.customer_id)SELECT c.name, b.spent                   -- الخطوة 3: العرضFROM by_customer b JOIN customers c ON c.id = b.customer_idWHERE b.spent > (SELECT avg(spent) FROM by_customer)ORDER BY b.spent DESC;

النتيجة · 4 صفوف

name	spent
سارة	3805
خالد	3575
نورة	3530
فهد	3040

العملاء الذين أنفقوا أكثر من المتوسط. لاحظ أن by_customer استُخدم مرتين.

وتستطيع الخطوة أن تعدّل البيانات وتمرر ما عدّلته إلى الخطوة التالية بـ RETURNING، في أمر واحد ذري:

query.sql

CREATE TABLE orders_archive (LIKE orders); WITH moved AS (  DELETE FROM orders WHERE status = 'cancelled'  RETURNING *)INSERT INTO orders_archive SELECT * FROM moved; SELECT (SELECT count(*) FROM orders) AS في_الطلبات,       (SELECT count(*) FROM orders_archive) AS في_الأرشيف;

✓ أُنشئ الجدول

✓ تم التنفيذ

النتيجة · صف واحد

في_الطلبات	في_الأرشيف
11	1

٣WITH RECURSIVE: الهرميات

الهيكل الإداري شجرة: كل موظف له مدير. لإيجاد كل من تحت مدير، بأي عمق، نستخدم CTE تكراريًّا: جزء بداية، وجزء يُضاف إليه مرة بعد مرة حتى لا يجد جديدًا:

query.sql

CREATE TABLE staff (id int PRIMARY KEY, name text, manager_id int REFERENCES staff(id));INSERT INTO staff VALUES (1, 'المدير العام', NULL), (2, 'مدير المبيعات', 1), (3, 'مدير المستودع', 1), (4, 'مندوب الرياض', 2), (5, 'مندوب جدة', 2), (6, 'أمين مخزن', 3), (7, 'مساعد مندوب', 4); WITH RECURSIVE team AS (  SELECT id, name, 0 AS level              -- البداية: مدير المبيعات  FROM staff WHERE id = 2  UNION ALL  SELECT s.id, s.name, t.level + 1         -- التكرار: من مديرهم في team  FROM staff s JOIN team t ON s.manager_id = t.id)SELECT repeat('— ', level) || name AS الفريق, level FROM team ORDER BY level, id;

النتيجة · 4 صفوف

الفريق	level
مدير المبيعات	0
— مندوب الرياض	1
— مندوب جدة	1
— — مساعد مندوب	2

توقّع وتحقّق

أسلم طريقة لإيجاد عملاء بلا طلبات:

فائدة WITH الأساسية:

جزءا CTE التكراري يُربطان بـ:

RETURNING في DELETE:

الخلاصة

NOT EXISTS أسلم من NOT IN.
المترابط في SELECT حلقة مخفية؛ استبدله بربط وتجميع.
WITH يسمّي الخطوات، و DELETE … RETURNING ينقل الصفوف في أمر واحد.
WITH RECURSIVE: بداية UNION ALL تكرار، حتى لا يُضاف جديد.

تحدٍّ برمجي: سلسلة الإدارة

متقدم

لموظف «مساعد مندوب» (رقم 7)، اعرض سلسلة مدرائه صعودًا حتى المدير العام، مع مستوى كل منهم.

المطلوب:

استخدم WITH RECURSIVE يبدأ من الموظف 7.
في كل تكرار انتقل إلى مدير الصف السابق.
الأعمدة: name و step (0 للموظف نفسه)، مرتبة بـ step.

👀 اعرض النتيجة المطلوبة

النتيجة المطلوبة

name	step
مساعد مندوب	0
مندوب الرياض	1
مدير المبيعات	2
المدير العام	3

✓ التحقق الآلي من حلّك:

الكود يعمل بلا أخطاء
السلسلة: مساعد مندوب ← مندوب الرياض ← مدير المبيعات ← المدير العام
WITH RECURSIVE

💡 تلميح 1

عكس مثال الدرس: هناك s.manager_id = t.id (نزولًا)، وهنا s.id = c.manager_id (صعودًا).

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql

CREATE TABLE staff (id int PRIMARY KEY, name text, manager_id int REFERENCES staff(id));INSERT INTO staff VALUES (1, 'المدير العام', NULL), (2, 'مدير المبيعات', 1), (3, 'مدير المستودع', 1), (4, 'مندوب الرياض', 2), (5, 'مندوب جدة', 2), (6, 'أمين مخزن', 3), (7, 'مساعد مندوب', 4); WITH RECURSIVE chain AS (  SELECT id, name, manager_id, 0 AS step FROM staff WHERE id = 7  UNION ALL  SELECT s.id, s.name, s.manager_id, c.step + 1  FROM staff s JOIN chain c ON s.id = c.manager_id)SELECT name, step FROM chain ORDER BY step;

انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس