شرح : نبذة عن الاستعلامات الفرعية SubQueries وامثلة عملية عليها

[Amr Ashraf]

السلام عليكم ,, الاخوة الافاضل ,

موضوعنا اليوم فى نطاق الاستعلامات وكما بالعنوان سوف نتطرق الى الاستعلامات الفرعية وطريقة استخدامها وامثلة عملية عليها واستخدامها كمعايير فى الاستعلامات الرئيسية.

وسأقوم باستخدام النصوص فى الجزء الاغلب من الشرح تطبيقا لطلب استاذنا ابو خليل .

بالنسبة للمبتدئين امثالى توضيح بسيط :

ماهى المعايير ؟

المعايير هى شرط او اكثر بناءً عليه يتم استدعاء البيانات من الجداول وفى الرابط التالى الكثير من المعلومات والامثلة لكافة انواع الحقول (نص - رقمى - وقت وتاريخ الخ...)

https://support.microsoft.com/en-us/office/examples-of-query-criteria-3197228c-8684-4552-ac03-aba746fb29d8

نعود لموضوعنا الاستعلامات الفرعية SubQueries , ماهى ؟

كما يوجد نموذج فرعى وتقرير فرعى هناك استعلام فرعى يتم استخدامه داخل الاستعلامات الاساسية بهدف الحصول على نتيجة يصعب الحصول عليها بالطرق العادية , ولها الكثير من الانواع سأتطرق لما استخدمته فعلياً حتى الآن ونأمل ان احد الاساتذة يثرى الموضوع بمشاركات اخرى لتطبيقات عملية أخرى .

جملة الSQL :

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

نبدأ بالمثال الأول :

على افتراض ان عندى مجموعة البيانات التالية مجموعة من المنتجات واسعارها :

Product	UnitPrice
Banana	$1.00
Choclate$5.00
Juice	$3.00
Mango	$7.00
Milk	$4.00
Water	$3.75

وأريد ان اعرف ماهى المنتجات التى تتساوى فى السعر او سعرها اقل من سعر منتج Milk , فى الطريقة العادية سيتم وضع معيار تحت حقل السعر ويكتب فيه <=4 وهو سعر المنتج , ماذا لو لم تعرف سعر المنتج الحالى او السعر يتغير باستمرار ويصعب تتبع التغييرات بالتالى يمكن عمل استعلام يقوم بالحصول على سعر المنتج المطلوب وبالتالى فى حالة تغير السعر نتيجة الاستعلام تتغير معه دون تدخل منك , انظر الى الاستعلام التالى:

SELECT TblProducts.Product, TblProducts.UnitPrice
FROM TblProducts;

هذا استعلام اساسى يقوم باستدعاء المنتجات واسعارها بدون معايير , الق نظرة على الاستعلام التالى :

SELECT TblProducts.Product, TblProducts.UnitPrice
FROM TblProducts
WHERE (((TblProducts.UnitPrice)<=(SELECT TblProducts.[UnitPrice] FROM TblProducts Where TblProducts.[Product]="Milk")));

وهذه صورته فى وضع التصميم

لو تلاحظ تم اضافة معيار تحت السعر عبارة عن استعلام آخر مهمته هوا الحصول على سعر المنتج Milk كما لو انك كتبت بدلا منه <=4 , وبالتالى نتيجته ستكون عرض كافة المنتجات التى سعرها يقل عن او يساوى سعر المنتج Milk انظر الى النتيجة :

Product	UnitPrice
Water	$3.75
Juice	$3.00
Milk	$4.00
Banana	$1.00

نلاحظ عرض المنتجات التى يكون سعرها اقل من او يساوى 4 (وهى نتيجة الاستعلام الفرعى) .

ملاحظات على الاستعلام :

• الاستعلام الفرعى يجب ان يكون بين قوسين ().
• من المعلوم ان اى جملة SQL تنتهى بفاصلة منقوطة ; وهى الطريقة التى تخبر بها قاعدة البيانات بأن الجملة قد انتهت,  ولكن لو لاحظت الاستعلام الفرعى لا يحتوى على فاصلة منقوطة بين الاقواس ولكن تكتفى بغلق الجملة الاساسية فقط وليس الفرعية.
• الاستعلام الفرعى يحضر نتيجة واحدة فقط (على الاقل الاستعلامات التى استخدمتها فعلياً).

فى حالة وجود مسافة فى اسم الحقل مثلا Unit Price يجب تضمينه داخل اقواس Brackets [].

يتبع فى رد يحتوى على مثال آخر .

مرفق قاعدة بها جميع الامثلة المشروحة فى الموضوع ,, نسألكم الدعاء بصلاح الحال .

 

 

Amr - SubQueries.accdb

تم تعديل يناير 20, 2022 بواسطه Amr Ashraf
اضافة مرفق

[Amr Ashraf]

مثال آخر ولكن اكثر تقدما (عن نفسى احتجته فى عملية حسابية ولم اكن اعرف شئ عن الاستعلامات الفرعية واجابة السؤال هى ما دفعنى للبحث والتطبيق عليها) :

 

هذه قائمة العملاء فى شركة ما :

ID	Client
1	Mohamed
2	Amr
3	Ahmed

يقوم كل عميل منهم بعمل 4 طلبيات فى الشهر بمعدل طلبية كل اسبوع مثلا , كل طلبية لها تاريخ بداية توريد وتاريخ نهاية توريد وهو التاريخ الذى سيقوم العميل باستلام البضائع فيه , انظر لهذه البيانات تتضمن طلبيتين لعميلين فى تواريخ مختلفة :

الطلبيات وتواريخها :

OrderID	    Client	StartFrom	EndsOn
2022-Ahmed1	Ahmed	03/01/2022	07/01/2022
2022-Ahmed2	Ahmed	03/02/2022	05/02/2022
2022-Amr1	Amr	 01/01/2022	05/01/2022
2022-Amr2	Amr	 20/01/2022	25/01/2022

تفاصيل المنتجات المطلوبة :

OrderID	   Product	OrderQty
2022-Amr1	Banana	120
2022-Amr1	Choclate	100
2022-Amr1	Juice	130
2022-Amr1	Mango	100
2022-Amr1	Milk	150
2022-Amr1	Water	150
2022-Amr2	Banana	250
2022-Amr2	Choclate	240
2022-Amr2	Juice	210
2022-Amr2	Mango	220
2022-Amr2	Milk	250
2022-Amr2	Water	300
2022-Ahmed1	Banana	250
2022-Ahmed1	Choclate	70
2022-Ahmed1	Juice	250
2022-Ahmed1	Mango	20
2022-Ahmed1	Milk	250
2022-Ahmed1	Water	250
2022-Ahmed2	Banana	300
2022-Ahmed2	Choclate	70
2022-Ahmed2	Juice	300
2022-Ahmed2	Mango	50
2022-Ahmed2	Milk	300
2022-Ahmed2	Water	300

وهذا جدول المبيعات :

Zdate	    Product	 UnitPrice	QtySold	TotalAmount	Client
01/01/2022	Choclate	$5.00	70	350	Amr
02/01/2022	Juice	$3.00	100	300	Amr
02/01/2022	Mango	$7.00	30	210	Amr
04/01/2022	Milk	$4.00	100	400	Amr
04/01/2022	Water	$2.50	100	250	Amr
03/01/2022	Banana	$1.00	30	30	Ahmed
03/01/2022	Choclate	$5.00	60	300	Ahmed
05/01/2022	Mango	$7.00	10	70	Ahmed
07/01/2022	Milk	$4.00	75	300	Ahmed
07/01/2022	Water	$2.50	75	187.5	Ahmed
20/01/2022	Banana	$1.00	70	70	Amr
20/01/2022	Choclate	$5.00	90	450	Amr
22/01/2022	Juice	$3.00	150	450	Amr
22/01/2022	Mango	$7.00	50	350	Amr
25/01/2022	Milk	$4.00	150	600	Amr
25/01/2022	Water	$2.50	150	375	Amr
03/02/2022	Banana	$1.00	35	35	Ahmed
03/02/2022	Choclate	$5.00	67	335	Ahmed
04/02/2022	Juice	$3.00	80	240	Ahmed
04/02/2022	Mango	$7.00	20	140	Ahmed
05/02/2022	Milk	$4.00	80	320	Ahmed
05/02/2022	Water	$2.50	80	200	Ahmed

هناك قاعدة بيانات مرفقة لنظرة افضل Amr - SubQueries.accdb.

المطلوب : متابعة كل طلبية لكل عميل لكل منتج على حدى ماذا تم تسليمه والمتبقى منها فى التواريخ التى تقع بين تاريخ بداية التوريد وتاريخ نهايته .

طبعا كان من السهل الحصول على المبيعات بين تاريخين محددين او يتم كتابتهم يدويا او يتم الاشارة اليهم فى نموذج ما ومن الاسهل اذا كان رقم الطلبية مذكور فى جدول المبيعات وبناء عليه يتم ربط الاستعلامات ببعضها والحصول على النتيجة المرجوة , ولكن الصعوبة ماذا لو كان التاريخين مكتوبين فى عمود داخل نفس الاستعلام مثلا ؟ وطبعا الشروط كثيرة ورقم الطلبية غير مذكور فى المبيعات ويتم استنتاجها من تاريخ البيع انظر الاستعلام التالى QryFollowUp :

SELECT QryOrders.OrderID, QryOrders.Client, QryOrders.Product, QryOrders.OrderQty, Nz((select Sum(QtySold) 
From TblSales AS T Where
T.Product = QryOrders.[Product] And T.Client = QryOrders.[Client] And (T.zDate >= QryOrders.[StartFrom] And T.zDate <= QryOrders.[EndsOn])),0) AS QtyDelivered, [OrderQty]-[QtyDelivered] AS Remains
FROM QryOrders;

فى هذا المثال ستجد ان الاستعلام الفرعى ليس معيار ولكن حقل او عمود فى الاستعلام كما فى الصورة التالية

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

انظر النتيجة

لو تم تغيير تاريخ بداية ونهاية التوريد فى جدول الطلبيات ستتغير النتائج فى هذا الاستعلام تلقائياً طبعا 😆.

ملاحظات على المثال :

• تم استخدام Alias للاستعلام الفرعى يرمز له بالحرف T وذلك لتسهيل الاشارة اليه فيما بعد.
• يلاحظ انه تم استخدام دالة الجمع Sum وبالتالى يمكن استخدام باقى الدوال مثل (Min-Max-Average-First-Last ) الى آخره .

يتبع بمثال آخر .

تم تعديل يناير 20, 2022 بواسطه Amr Ashraf

[ابو جودي]

ماشاء الله شرح مبسط ومنسق ولا اروع 

طيب احيانا بيكون فى استعلام داخل استعلام لكن فى الحقول وليس فى المعيار

هل يمكن التطرق اليه

[Amr Ashraf]

1 دقيقه مضت, ابو جودي said:

طيب احيانا بيكون فى استعلام داخل استعلام لكن فى الحقول وليس فى المعيار

المثال السابق فى حقل وليس فى معيار  😅 انظر لصورته  , انا نسيت اذكر النقطة الهامة دى شكرا على التوضيح يا غالى

[Amr Ashraf]

مثال آخر شبيه بالسابق :

فى هذا المثال افترضت ان المنتج يتم تحميله على مراحل فى نفس الطلبية ومطلوب انى اعرف اول تاريخ تسليم للمنتج داخل طلبية ما أو العكس اخر تاريخ تم تسليم المنتج للعميل فيه , انظر الاستعلام التالى QryOrdersFollowUp(Dates) :

SELECT QryOrders.OrderID, QryOrders.Client, QryOrders.Product, QryOrders.OrderQty, Nz((select Sum(QtySold) From TblSales AS T Where T.Product = QryOrders.[Product] And T.Client = QryOrders.[Client] And (T.zDate >= QryOrders.[StartFrom] And T.zDate <= QryOrders.[EndsOn])),0) AS QtyDelivered, [OrderQty]-[QtyDelivered] AS Remains, Nz((select Min(Zdate) From TblSales AS T Where T.Product = QryOrders.[Product] And T.Client = QryOrders.[Client] And (T.zDate >= QryOrders.[StartFrom] And T.zDate <= QryOrders.[EndsOn])),"None") AS FirstDelivery, Nz((select Max(Zdate) From TblSales AS T Where T.Product = QryOrders.[Product] And T.Client = QryOrders.[Client] And (T.zDate >= QryOrders.[StartFrom] And T.zDate <= QryOrders.[EndsOn])),"None") AS LastDelivery
FROM QryOrders;

والنتيجة كالتالى :

لاحظ انه لا يوجد مشكلة فى حالة عدم وجود تسليمات للمنتج فى طلبية ما لعميل ما سيتم كتابة None مكان التاريخ .

وهناك الكثير من الامثلة ولكن هذا ما طبقته بالفعل وتأكدت من نتائجه .. اتمنى المشاركة من الاساتذة الكبار وخاصة استاذنا @jjafferr له صولات وجولات فى هذا المضمار 😅

انتهى الشرح دمتم بخير .

[Amr Ashraf]

مثال أخر بالمشاركة مع الأخ @ابو جودي وبعد كونسولتو لمعرفة علة المريض 😂 , مثال لاستعلام فرعى لحقل ترقيم تلقائى يعيد ضبط نفسه حتى مع حذف سجلات من وسط السيريال :

لدينا هذه البيانات فى الجدول :

ID	FullName
8	a
13	B
14	c
15	d
16	E
17	D
19	EEE

سيريالات غير منضبطة , انظر للاستعلام التالى :

SELECT A.FullName, A.ID, (SELECT Count("*") FROM tblData AS B WHERE  B.[ID]<A.[ID])+1 AS NewID
FROM tblData AS A;

تم استخدام دالة Count والنتيجة

حاول تحذف اى سجل من المنتصف , ستجد ان الترقيم تم اعادة ضبطه ليتناسب مع السجلات الجديدة .

انظر المرفق .

دمتم بود .

 

Sequence.accdb

تم تعديل يناير 20, 2022 بواسطه Amr Ashraf
اضافة مرفق

[Eng.Qassim]

ماشاء الله عليك استاذ @Amr Ashraf

زادك الله من علمه

[Amr Ashraf]

4 دقائق مضت, Eng.Qassim said:

زادك الله من علمه

جزاك الله خير يا غالى

[أبوبسمله]

ماشاء الله شرح مبسط ورائع وان شاء الله ننتفع به

جزاك الله خيرا استاذ @Amr Ashraf 💐

واضيف تعليق لمعلمنا العزيز @jjafferr جزاه الله عنا خير الجزاء وكل اساتذتنا الذين تعلمنا ونتعلم منهم كل يوم بارك الله لنا فيهم وزادهم الله من فضله وعلمه وجزاهم الله خيرا

ومرجع من ميكروسوفت

https://support.microsoft.com/ar-sa/office/تضمين-استعلام-بداخل-استعلام-آخر-أو-تعبير-باستخدام-استعلامًا-فرعيًا-a8532322-e42f-4f94-bc38-ace7c182916a?ui=ar-sa&rs=ar-sa&ad=sa

ومرجع اخر ايضا

https://wiki.hsoub.com/SQL/subquery

بالتوفيق

[Amr Ashraf]

1 ساعه مضت, الفلاحجى said:

ماشاء الله شرح مبسط ورائع وان شاء الله ننتفع به

جزاك الله خيرا استاذ @Amr Ashraf 💐

جزاك الله خير يا غالى.. وشكرا على مشاركتك