قواعد البيانات بين النظرية والتطبيق اعزائي الكرام السلام عليكم ورحمة الله وبركاته سنناقش في هذاالموضوع عدداً من المشاكل التي قد تصادفكم لدى تعاملكم مع البيانات , وسنعرض الحلول الممكنه لها. واقترح أن تحاولوا إيجاد حل قبل قراءة الحل المعطى في النص . وعلينا أن نشير هنا إلي أن هناك عدة طرق لحل مشكلة معينة . في الحقيقه قد نتمكن من الحصول على حل أكثر فعاليه من الحل المقترح هنا إن الهدف الأساسي من هذه المشاكل والحلول هي إعطاؤكم غذاء فكريا نافعا . قبل البدء, دعونا نشير إلي أن الكثير من الحلول المقترحة يعتمد على استخدام الاستعلامات الفرعيه . يسمح Access SQL بأستخدام عبارات SeIectضمن عبارات SeIect أخرى (كما هي الحال في عبارات أخرى مثل عبارات ( INSERT INTO) . تدعى عبارة SeIect الداخليه أو المتشابكه بالاستعلام الفرعي. لاحظ أنه يمكن أن تضمن عبارة SeIect داخليه ضمن عبارة SeIect أساسية فقط إذا كانت عبارة SeIect الداخلية تعيد سجلا واحدا على الأغلب ولبيان ذلك لنأخذ عبارة SQL الأساسية التالية:
SELECT HOUR, (Select Count(Interval) From StartTimes WHERE (StartTime <=Hour)) FROM HOURS
تعيد عبارة Select الداخلية :
Select Count(Interval) From StartTimes WHERE (StartTime <=Hour)
على الأغلب سجلاً واحدا لأنها تعيد عدد (Count) لاحظ أيضاً أن ظرف WHEREفي عبارة SeIect الداخليه يشير إلى حقل HOUR الذي هو جزء من عبارة SQL الرئيسية , وبالتالي فانه يقوم بربط القيمه المسترجعه للعبارة الداخلية إلى السجل الحالي في جدول HOURS المجاميع الجارية : Running Sums يعتبر حساب المجاميع الجارية من العمليات الشائعه ولبيان هذا المفهوم سنأخذ الجدول ( 1-1) الذي يتضمن المدد الزمنيه ( محسوبة بالساعة ) لعدة أحداث . ونريد أن نحسب لكل حدث مجموع المدد الزمنية للآحداث التي تسبقه يدعي هذا المجموع بالمجموع الجاري . الحل : Solution إن إحدى طرق الحل هي بإجراء الجداء السلمي للجدول مع نفسه نستطيع بهذه الطريقه الوصول إلي جميع السجلات التي يسبق رقم حدثها ( Event ) رقم سجل (أوحدث) معين . على سبيل المثال ,من أجل السجل الخامس ، نحتاج الي الوصول إلي السجلات التي تتضمن الاحداث رقم 1 وحتى 4 . أن الجداء السلمي يعطينا هذه السجلات . إليك عبارة SQL التي تقوم بهذا العمل :
SELECT Running.Event, Sum(RunningCopy.Duration) AS StartTime FROM Running, Running AS RunningCopy WHERE (RunningCopy.Event < Running.Event) GROUP BY Running.Event
يقوم ظرف FORM بإنشاء الجداء السلمي للجدول مع نفسه . أما ظرف WHERE فيحدد السجلات التي تحقق المعيار
Running Copy.Event < Running. Event
أي ان السجلات التي ستقدم المعلوكات حول السجلات التي تسبق كل سجل في الجدول ( 1-1 ). أخيرا ، نقوم بتجميع النتائج حسب الحدث ( Group By Event ) وحساب مجموع المدد الزمنية . ان المشكلة هي ان الجداء السلمي غير فعال ويستخدم حجما كبيرا من الموارد ( إذا كان عدد سجلات الجدول ( 1-1 ) 100000 سجل فإن الجداء السلمي سينفذ عملية ضرب على 100000 × 100000 سجل أي 10.000.000.000 سجل . ان الحل الاكثر فعالية هو استخدام عبارة Select متشابكة ( داخلية ) أي استخدام عبارة Select ضمن عبارة Select اساسية . نعود لنذكر هنا بأن Access SQL يسمح بذلك اذا كانت العبارة الداخلية تعيد على الغالب سجلا واحدا . لاحظ استخدمنا لألقاب ( نسخ ) للجدول في عبارة Select التالية ونحتاج الى هذه الالقاب لاننا سنشير الى الجدول ( 1-1 ) في سياقين
Select R1.Event (Select Sum(R2.Duration) From Running As R2 Where R2.Event <R1.Event) As StartTime FROM Running As R1
ان عبارة SQL الداخلية :
Select Sum(R2.Duration) From Running As R2 Where R2.Event <R1.Event
تعيد مجموع المدة لجميع الاحداث التي تسبق الحدث الحالي ، والذي نشير إليه بالعبارة R1.Event يعرض المثال ( 1-1 ) اجرائية VBA لتنفيذ عبارة SQL السابقة . ويستخدم الكائن DoCmd في VBA لتشغيل اجراء Access وبالتالي فأن السطر البرمجي :
DoCmd.OpenQuery
يقوم بفتح استعلام في Access المثال ( 1-1 ) : حساب المجموع الجاري بإستخدام عبارة SQL
Private Sub RunningSumSQL () Dim db As ADO.Database Set db = CurrentDb Dim qry As ADO.QueryDef Dim sSQL As String On Error Resume Next db.QueryDefs.Delete "temp" On Error GoTo 0 sSQL = "SELECT R1.Event," & _ " (SELECT SUM(R2.Duration)" & _ "FROM Running As R2" & _ "WHERE R2.Event < R1.Event)" & _ "AS StartTime" & _ " FROM Running As R1" Set qry = db.CreateQueryDef("temp", sSQL) DoCmd.OpenQuery qry.Nqme End Sub
هناك طريقة أخرى تقوم على استخدام ADO الذي يؤمن طريق أو حل بسيط في هذه الحالة فهو ينشئ جدول نتائج دائم في حين ان الحل السابق يقوم بإنشاء استعلام تحديد ويعرض المثال ( 1-2 ) شفرة ADO التي تنفذ نفس العمل
Private Sub RunningSumDAO( ) Dim db As Database Dim rs As Recordset Dim lRunningSum As Long Set db = CurrentDblRunningSum = 0 Set rs = db.OpenRecordset("SELECT * FROM Running ORDER BY Event") Do While Not rs.EOF rs.Edit rs!RunningSum = lRunningSum rs.Update lRunningSum = lRunningSum + rs!Duration rs.MoveNext Loop rs.Close End Sub
المجالات المتقاطعة : Overlapping Interavl تشير الصفوف في الجدول ( 1-2 ) الى مجالات زمنية ، المشكلة هي تعيين من أجل كل ساعة من اليوم ، عدد المجالات التي تتضمن هذه الساعة . ولحل هذه المشكلة نستخدم جدول HOURS المبين في الجدول ( 1-3 ) الحل : Solution يمكن حل هذه المشكلة بإستخدام عبارة Select متشابكة ( أي بإستخدام استعلام فرعي ) والكود التالي يقوم بالعمل المطلوب :
Private Sub OverlappingIntervals( ) Dim db As Database Set db = CurrentDb Dim qry As QueryDef Dim sSQL As String On Error Resume Next db.QueryDefs.Delete "temp" On Error GoTo 0 sSQL = "SELECT Hours.Hour, " & _ " (SELECT Count(Interval) AS CountOfIntervals" & _ " FROM Overlap" & _ " WHERE (StartTime <= Hours.Hour) And" & _ " (Hours.Hour < EndTime))" & _ " FROM Hours" Set qry = db.CreateQueryDef("temp", sSQL) DoCmd.OpenQuery qry.Name End Sub
المجالات المتقاطعه Overlapping Intervals II : II تستخدم شركه عمالاً ومشرفين ، وخلال يوم عمل عادي ، يعمل كل عامل وكل مشرف ورديه واحده أو ورديتين ، تتألف من ساعات عمل متعاقبه ، يعرض الجدول ( 4-1 ) يوماً تقليدياً من الساعه 12 ظهراً وحتى الساعه 12 في منتصف الليل . نريد هنا حساب العدد الآعظمي للعمال الذين يشرف عليهم مشرف واحد في وقت معين . الحل : Solution يمكن معالجة هذه المشكله بإجراء مؤلف من مرحلتين ، نقوم اولاً بحساب عدد العمال في كل ساعه ونأخذ عبارة SQL التاليه :
SELECT Hours.Hour, (SELECT Count(EmpType) FROM SuperLoad WHERE (Starthour <= Hours.Hour) And (Hours.Hour < EndHour) And (EmpType='Worker')) AS CountOfWorkers FROM Hours
لاحظ هنا اننا نستخدم استعلاماً فرعياً يعيد سجلاً وحيداً يعطي عدد العمال الذين يعملون في ساعه معينه . بإستخدام عبارة SQL هذه نقوم بإنشاء استعلام اسمه qry1 بحيث يمكن استخدامه في المرحله الثانيه . انظر الجدول ( 1-5) من اجل نتائج هذا الإستعلام . الخطوه التاليه هي حساب تحميل المشرف على انه العدد الآعظمي للعمال في كل ورديه للمشرف ، نقوم بذلك باستخدام اسم الآستعلام من المرحله السابقه في عبارة SQL التاليه :
sSQL = "SELECT SuperLoad.EmpID, SuperLoad.EmpType," & _ " (SELECT Max(CountOfWorkers) AS WorkerLoad" & _ " FROM [" & qry1.Name & "]" & _ " WHERE((Hours.Hour >= StartHour) And(Hours.Hour< Endhour)))" & _ " FROM SuperLoad " & _ " WHERE SuperLoad.EmpType = 'Super' "
والنتائج مبينه في الجدول ( 1-6) والكود الكامل لهذا الحل مبينه في المثال ( 1-3) المثال ( 1-3) : حساب العدد الآعظمي للعمال من اجل كل مشرف .
Private Sub SupervisorLoad( ) Dim db As Database Set db = CurrentDbDim qry1 As QueryDef Dim qry2 As QueryDef Dim sSQL1 As String Dim sSQL2 As StringOn Error Resume Next db.QueryDefs.Delete "temp1" db.QueryDefs.Delete "temp2" On Error GoTo 0sSQL = "SELECT Hours.Hour, " & _ " (SELECT Count(Emptype) FROM SuperLoad" & _ " WHERE(StartHour <= Hours.Hour) And (Hours.Hour < EndHour)" & _ " And (Emptype='Worker'))" & _ " AS CountOfWorkers" & _ " FROM Hours"Set qry1 = db.CreateQueryDef("temp1", sSQL1) ' Uncomment to see how this step looks ' DoCmd.OpenQuery qry1.NamesSQL = "SELECT SuperLoad.EmpID, SuperLoad.EmpType," & _ " (SELECT Max(CountOfWorkers) AS WorkerLoad" & _ " FROM [" & qry1.Name & "]" & _ " WHERE((Hours.Hour >= StartHour) And (Hours.Hour< Endhour)))" & _ " FROM SuperLoad " & _ " WHERE SuperLoad.EmpType = 'Super' "Set qry2 = db.CreateQueryDef("temp2", sSQL2)DoCmd.OpenQuery qry2.Name End Sub
إجراءات عمليات الإسناد باستخدام القيمه الافتراضية : Assignment With Default Making تخيل وجود مؤتمر، ومهمتك فيه توزيع الحضور على قاعات المحاضرات ، يعرض الجدول ( 1-7) الحضور المسجل مع ارقام الغرف الموافقه . لاحظ ان الجدول يتضمن عدة خيارات افتراضيه ( Default ) وإذا لم يكن شخص موجوداً في الجدول ، فإنك تريد تخصيصه بغرفه من الغرف الآفتراضيه ، بالإضافه الى ذلك ، ولتجنب الازدحام في الغرف ، تريد تخصيص ارقام الغرف الافتراضيه بشكل عشوائي ، فكيف نقوم بذلك . الحل : Solution يمكن حل هذه المشكله بعدة طرق ، تقوم إحداها على استخدام الاستعلامات الفرعيه مع عبارةUNION لنأخذ اولا عبارة SQL التاليه :
sSQL1="SELECT ROOM From Assignment WHERE (Name=[Enter Name])"
نذكر بأن استخدام عبارة (Enter Narme ) هو عبارة عن وسيط . وعند تشغيل الاستعلام ، يتم الطلب من المستخدم إدخال اسم يتم استبداله بالوسيط المذكور ، ستعيد هذه العباره السجل الموافق لاسم معين إذا كان موجوداً ، وإلا فإنها ستعيد مجموعة سجلات فارغه . لتأخذ الآن عبارة SQL التاليه : يتظمن المثال ( 1-4 ) الكود الكامل لتنفيذ الحل المطلوب . معالجة توزيع الغرف الافتراضية والمحجوزة مسبقا .
sSQL2 = "SELECT Room FROM Assignment" & _ "WHERE (Name = '_default') AND ([Enter Name] NOT IN (SELECT Name FROM Assignment))"
إن عبارة
[Enter Name] NOT IN (SELECT Name FROM Assignment)
ستعيد القيمه True إذا وفقط إذا كان الاسم الذي ادخله المستخدم غير موجود في الجدول وبالتالي فإن عبارة sSQL2 يمكن ان تكتب من جديد استناداً الى حالتين : 1.الاسم في الجدول
sSQL2 = "SELECT Room FROM Assignment WHERE (Name = '_default') AND False"
2. الاسم ليس في الجدول
sSQL1 = "SELECT Room FROM Assignment WHERE (Name = '_default') AND True"
ويمكن اختصار العباره من اجل حالة الاسم في الجدول لتصبح :
sSQL2 = "SELECT Room FROM Assignment WHERE FALSE
والعبارة من اجل حالة الاسم ليس في الجدول :
sSQL1 = "SELECT Room FROM Assignment WHERE (Name = '_default')"
وهكذا فإن العباره ستعيد مجموعة سجلات فارغه اذا كان الاسم في الجدول والسجلات الافتراضيه اذا لم يكن الاسم في الجدول . سنأخذ الآن دمج أو توحيد العبارتين :
sSQL3=sSQL1 & "UNION" & sSQL2
ستعيد هذه العباره رقم الغرفه من اجل اسم اذا كان الاسم في الجدول وإلا فإنها ستعيد السجلات الافتراضيه ان كل ماتحتاجه الان هو استرجاع سجل عشوائي . لاحظ ان ذلك يمكن ان يعمل في كلا الحالتين ، لانه اذا كان الاسم في الجدول ، فهناك سجل واحد وبالتالي فإن الخيار العشوائي هو ذلك السجل . يتضمن الكود التالي الشيفرة الكاملة للحل المطلوب معالجة توزيع الغرف الافتراضية والمحجوزة مسبقا .
Private Sub AssignmentWithDefault( ) Dim db As Database Set db = CurrentDb Dim sName As String Dim qry1 As QueryDef Dim rs As Recordset Dim sSQL1 As String Dim sSQL2 As String Dim sSQL3 As String Dim lRandom As Long Dim lcRecords As Long On Error Resume Next db.QueryDefs.Delete "temp1" On Error GoTo 0 sSQL1 = "SELECT Room FROM Assignment" & _ "WHERE (Name = [Enter Name])" sSQL2 = "SELECT Room FROM Assignment" & _ "WHERE (Name = '_default') AND ([Enter Name] NOT IN (SELECT Name FROM Assignment))" sSQL3 = sSQL1 & " UNION " & sSQL2 Set qry1 = db.CreateQueryDef("temp", sSQL3) sName = InputBox("Enter name") qry1.Parameters(0) = sName ' لمشاهدة النتائج ' 'DoCmd.OpenQuery qry1.NameSet rs = qry1.OpenRecordset ' Populate and get recordCount rs.MoveLast lcRecords = rs.RecordCount ' Random record Randomize Timer ' lRandom is between 0 and lcRecords-1 lRandom = Int(lcRecords * Rnd) rs.MoveFirst rs.Move lRandom MsgBox "Room for " & sName & " is " & rs!Room End Sub
زمن الإنجاز : Time to Completion سنعرض هنا مشكله بسيطه حول تحديد الزمن الكلي لإنجاز مهمه ما . يعرض الجدول ( 1- 8) حالة إنتاج سلعه في الشركه التي تعمل بها . يقوم العمال في المراحل المختلفه لعملية الإنتاج بإدخال سجل في الجدول للاشاره الى الزمن المتبقي لإنها السلعه . نريد تحديد السلع التي لم تنته بعد . الحل : Solution ان عبارة SQL التاليه ستقوم بالعمل المطلوب . لاحظ استخدام نموذج Not In في الاستعلام الفرعي .
SELECT DISTINCT WidgeID FROM Widgets As W1 WHERE 0 NOT IN (SELECT TimeToCompletion FROM Widgets As W2 WHERE W2.WidgetID = W1.WidgetID)
زمن الانجاز2 : Time To Completion2 دعنا نقوم بتعقيد مشكلة الانجاز قليلا . تخيل ثانية أنك تتابع حالة إنتاج السلع في الشركة . تتألف كل سلعة من أربعة أجزاء , يتم تجميع كل منها على حدة . يتضمن الجدول (9-1) عينة من البيانات . سنحاول هنا تعيين أو تحديد السلع التي يكون فيها الجزء الأول (Module 1 ) هو الجزء الوحيد الكامل – أي أن قيمة Time To Completion تساوي الصفر من أجل الجزء الأول , ولكن مختلفة عن الصفر من أجل الأجزاء الأخرى في السلعة . الحل : Solution لنأخذ عبارة SQL التالية :
SELECT WidgetID FROM Widgets AS W1 WHERE (TimeToCompletion = 0) AND 0 NOT IN (SELECT TimeToCompletion FROM Widgets AS W2 WHERE (W2.WidgetID=W1.WidgetID) AND (W2.ModuleID < > 1))
يحدد الاستعلام الفرعي من أجل سلعة معينة , جميع قيم Time To Completion لجميع الأجزاء باستثناء الجزء الأول . تستطيع بعد ذلك إجراء اختبار لرؤية فيما إذا كانت تلك المجموعة من أزمنة الانجاز Time To Completion تتضمن القيمة صفر . ويتضمن المثال (1-5) الكود الكامل . المثال (1-5) : مثال Time To Completion
Private Sub TimeToCompletion( ) Dim db As Database Set db = CurrentDb Dim qry1 As QueryDef Dim sSQL1 As String On Error Resume Next db.QueryDefs.Delete "temp1" On Error GoTo 0 sSQL1 = "SELECT widgetID FROM widgets As W1" &_ " WHERE (TimeToCompletion = 0) AND" & _ " 0 NOT IN" & _ " (SELECT TimeToCompletion FROM Widgets AS W2" & _ "WHERE (W2.WidgetID=W1.WidgetID) AND (W2.ModuleID <> 1) )" Set qry1 = db.CreateQueryDef("temp1", Ssql1) DoCmd.OpenQuery qry1.Name End Sub
زمن الانجاز III مسألة الحد الأعظم والحد الأدنى : Time To Completion III a MaxMin Problem دعنا نضيف مزيداً من التعقيد على مسألة زمن الانجاز . لنفترض أن كل جزء مؤلف من عدة أقسام . وسيقوم العمال المكلفين بقسم معين بإدخال قيد في جدول قاعدة البيانات وبشكل دوري , وذلك كما هو مبين في الجدول (1-10) . ونرغب هنا بحساب زمن انجاز كل قسم , وجزء وسلعة . لاحظ أنه يمكن أن يكون هناك عدة قيود من أجل قسم معين . وزمن الانجاز اللازم لقسم معين هو الحد الأدنى للأزمنة في هذه الصفوف . الحل الاول : Solution1 دعنا نعتمد طريقة تقوم على مبدأ الخطوة خطوة . وتستطيع لاحقاً عرض حل أكثر أناقة ولكنه أكثر صعوبة للفهم والقراءة . نقوم أولا بإنشاء عبارة SQL تعيد فقط صفوف الجدول لكل سلعة / جزء التي لها أصغر زمن انجاز للقسم Time To Completion . ونستطيع القيام بذلك على مرحلتين . تقوم أول عبارة SQL بتحديد حقل Time To Completion لجميع السجلات في المجموعة Widgets والتي لها قيم معينة لكل من Widget ID , Module ID , Part ID .
' Time to completion for given WidgetID/Modul*ID/PartIDsSQL1 = "SELECT TimeToCompletion FROM Widgets2 As W2" & _ " WHERE (W2.WidgetID = W1. WidgetID)" & _" And (W2.ModuleID = W1.ModuleID)" & _" And (W2.PartID = W1.PartID)"
أما عبارة SQL الثانية فتعيد جميع السجلات التي يكون فيها Time To Completion أصغر أو مساوٍ لجميع السجلات التي تمت إعادتها في عبارة SQL الأولى , أي جميع السجلات المتعلقة بقيم معينة Widget ID , Module ID , Part ID .
' Those records that have minimum time to completion for each part sSQL2 = "SELECT WidgetID, ModuleID, PartID," & _ " TimeToCompletion AS TimeToFinishPart FROM Widget2 AS W1" & _ " WHERE TimeToCompletion <= ALL (" &sSQL1&")"
هناك طريقة أخرى تقوم على استخدام عبارة Select متشابكة وحيدة :
sSQL2 = "SELECT WidgetID, ModuleID, PartID," & _ " (SELECT MIN(TimeToCompletion)" & _ " FROM Widgets2 as W2 WHERE" & _ " (W2.WidgetID = W1.WidgetID) And" & _ " (W2.ModuleID = W1.ModuleID) And" & _" (W2.PartID = W1.PartID)" & _ " AS TimeToFinishPart" & _ " FROM Widgets2 AS W1
بتنفيذ هذا الاستعلام :
Set qry1=db.CreateQueryDef("temp1", sSQL2) DoCmd.OpenQuery qry1.Name
سنحصل على الجدول (1-11) . وباستخدام الاستعلام التالي يصبح من السهل الحصول على زمن الانجاز لكل جزء .
' Time to finish each module sSQL3 = "SELECT WidgetID, ModuleID," & _ " Max(TimeToFinishPart) AS TimeToFinishModule FROM " & qry1.Name & _ " GROUP BY WidgetID, ModuleID" Set qry2 = db.CreateQueryDef("temp2", sSQL3) ' Show it DoCmd.OpenQuery qry2.Name
يتضمن الجدول (1-12) نتائج تنفيذ هذا الاستعلام . أخيراً نستطيع حساب زمن الانجاز لكل سلعة :
' Time to finish each Widget sSQL4 = "SELECT WidgetID," & _ " Max(TimeToFinishModule) AS TimeToFinishWidget FROM " & _ qry2.Name & " GROUP BY WidgetID" Set qry3 = db.CreateQueryDef("temp3", sSQL4)
والنتائج مبينة في الجدول (1-13) . الحل الثاني : Solution2 من الممكن الحصول على زمن الانجاز باستخدام عبارة SQL وحيدة , رغم أننا لا ننصح بذلك . قد تكون النتائج أكثر أناقة , ولكنها صعبة القراءة . سيكون لدينا مثلاً من أجل الأجزاء (Modules) :
sSQL1 = "SELECT DISTINCT WidgetID, ModuleID, " & _ " TimeToCompletion FROM Widgets2 AS W1" & _ " WHERE TimeToCompletion = " & _ " (SELECT MAX(TimeToCompletion) FROM Widgets2 As W2" & _ " WHERE TimeToCompletion = " & _ " (SELECT MIN(TimeToCompletion) FROM Widgets2 As W3" & _ " WHERE (W3.WidgetID = W2.WidgetID) " & _ " And (W3.ModuleID = W2.ModuleID) " & _ " And (W3.PartID = W2.PartID)" & _ " Group BY W3.WidgeetID, W3.ModuleID, W3.PartID)" & _ " AND (W2.WidgetID = W1.WidgetID)And(W2.ModuleID = _ " W1.ModuleID)" & GROUP BY W2.WidgetID, W2.ModuleID)"
إن هضم عبارة SQL هذه سيكون صعباً ويتطلب وقتاً أطول , ونرجو أنها ستجعلك تعيد النظر باستخدام مثل هذه العبارات في تطبيقاتك المستقبلية . عامودي الى افقي : Vertical to Horizontal تخيل ان لديك قاعدة بيانات إحصائيات شخصية مع جدولين ( الجدول 1-14 ) والجدول 1-15 ) لاحظ ان للجدول (1-15) صف واحد لكل احصاء . وهكذا فإن البيانات المتعلقة بشخص معين مرتبة عاموديا . ولاحظ ايضا ان بعض البيانات ناقصة على سبيل المثال لا يوجد بيانات من اجل " زهــره " . نريد الان عرض البيانات افقيا كما في الجدول (1-16) الحل : Solution تقدم لنا عبارة SQL التالية حلا لهذه المسألة :
SELECT DISTINCT Employees.EmpID, Name, (SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType 'Age') And (T2.EmpID = Employees.EmpID)) As Age, (SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType Height) And (T2.EmpID = Employees.EmpID)) As Height, (SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType Weight) And (T2.EmpID = Employees.EmpID)) As Weight, FROM (Employees INNER JOIN EmployeesData ON Employees.EmpID= EmployeesData.EmpID
لدينا هنا عدة عبارات استعلام فرعي Select ضمن عبارة Select الرئيسية . وتقوم العبارة التالية :
(Select Value From EmployeesData As T2 WHERE (T2.StatType='Age') And (T2.EmpID= Employees.EmpID)) As Age
بتحديد العمر للشخص المحدد في ظرف أو عبارة Select الرئيسية . وكلما ازداد عدد الاحصائيات تصبح عبارة SQL اكثر تعقيدا . يعرض المثال ( 1-16 ) حلا بديلا بإستخدامADO ولا يتطلب تعديل لدى ازدياد الإحصائيات . المثال ( 1-16 ) مثال : Vertical to Horizontal
Private Sub VerticalToHorizontal2( ) Dim db As Database Set db = CurrentDb Dim rsEmp As Recordset Dim rsData As Recordset Dim rsHor As RecordsetSet rsEmp = db.OpenRecordset("Employees") Set rsHor = db. OpenRecordset("EmployeesOutput") Do While Not rsEmp.EOF Set rsData = db.OpenRecordset( _ "SELECT * FROM EmployeesData WHERE EmpID = " & rsEmp!EmpID) rsHor.AddNew rsHor!EmpID = rsEmp!EmpID rsHor!Name = rsEmp!Name Do While Not rsData.EOF rsHor.Fields(rsData!StatType).Value = rsData!Value rsData.MoveNext Loop rsHor.Update rsEmp.MoveNext Loop rsEmp.Close rsData.Close rsHor.Close End Sub
مسألة تطابق : A Matching Problem يتضمن الجدول ( 1-17 ) مبرمجين ولغات البرمجمة التي يستخدمونها . أما الجدول ( 1-18 ) فيحدد متطلبات اللغة من اجل اعمال مختلفة . نريد هنا عرض قائمة بالاعمال والمبرمجين المؤهلين للقيام بها . الحل : Solution تقدم لنا عبارة SQL التالية أحد الحلول :
SELECT ProgrammingJobs.JobID, Programmers.Name FROM Programmers INNER JOIN ProgrammingJobs ON Programmers.Language = ProgrammingJobs.Language GROUP BY ProgrammingJobs.JobID, Programmers.Name HAVING Count(Programmers.Language)= (SELECT Count([Language]) FROM ProgrammingJobs AS pj WHERE pj.JobID= ProgrammingJobs.JobID)
نبدأ بربط داخلي Inner Join للجدولين بإستخدام حقل Language ومن أجل كل زوج Programmer/Job تنشئ علاقة الربط الداخلي مجموعة من السجلات كما في الشكل
JobID X Language1-ProgrammerName Y JobID X Language2-ProgrammerName Y JobID X Language3-ProgrammerName Y
حيث يتطلب العمل للغة والمبرمج مهارة بتلك اللغة . نحتاج الان ان نضمن لكل زوج عمل / مبرمج بأن يكون عدد من هذه السجلات هو نفس عدد اللغات المطلوبة لذلك العمل . ويتم ذلك عن طريق تحميع السجلات وفق الازواج عمل / مبرمج ومن ثم استخدام ظرف Having الذي يقارن عدد هذه السجلات مع عدد اللغات لذلك العمل .والنتيجة هي ما يتضمنه الجدول الجدول (1-19) تساوي المجموعات : Equality of Sets من المسائل الشائعة تعيين متى تتساوى مجموعتان أي متى يكون لهما نفس العناصر . لنأخذ الجدول ( 1-20 ) الذي يعرض خمس مجموعات وعناصرها . لاختصار المسألة وتبسيطها نقوم بترقيم المجموعات ونفترض انها تتضمن ارقاما بحد ذاتها . ونريد الحصول على قائمة بالمجموعات المتساوية . الحل : Solution لهذه المسألة حل أنيق بإستخدام عبارة SQL وحيدة . بينما لا يسمح SQL بشكل عام بمقارنة مجموعتين مباشرة كما في العبارة :
(Select Members From Equality Where Set=1)= (Select Members From Equality Where Set=2)
لكنه يقبل كثل هذه العبارة اذا كانت عبارات Select تعيد قيمة وحيدة . لنأخذ الان عبارة SQL التالية :
SELECt Equality.Set, E2.Set FROM Equality INNER JOIN Equality AS E2 ON (Equality.Member = E2.Member) And (Equality.Set < E2.Set) GROUP BY Equality.Set, E2.Set HAVING ((SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=Equality.Set) = (SELECT Count(Member)FROM Equality As E3 WHERE E3.Set=E2.Set)) AND (Count(Equality.Set) = (SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))
ان علاقة الربط الداخلي Inner Join هي :
(Equality.Member = E2.Member) And (Equality.Set < E2.Set)
ان الجزء الهام في هذه العبارة هو الجزء الاول . انه يفترض بأننا نريد جميع ازواج المجموعات التي لها عضو مشترك . ويمنع الجزء الثاني من العبارة اعادة ازواج متكررة . على سبيل المثال اذا تضمنت المجموعتان 1 و 2 العضو 3 فإننا لا نريد استرجاع الزوج ( 1-2 ) والزوج ( 2-1 ) . ولبيان ذلك بشكل اوضح بما ان العضو 3 مجوجود في المجموعات 1 ، 2 ، 3 ، 4 فإن السجلات المستعادة للعضو 3 هي على النحو التالي : ( 2-1 ) ( من العضو 3 ) ( 3-1 ) ( من العضو 3 ) ( 4-1 ) ( من العضو 3 ) ( 3-2 ) ( من العضو 3 ) ( 4-2 ) ( من العضو 3 ) ( 4-3 ) ( من العضو 3 ) واذا لم يكن الجزء Equality.Set < E2.Set موجودا فإننا كنا سنحصل على ( 1-1 ) و ( 2-2 ) ..... ( 4-4 ) بالاضافة الى ( 1-2 ) و ( 1-3 ) وهكذا . يمكننا الان ان نطرح السؤال التالي : كم مره سيظهر زوج مجموعة ما ؟ ان زوج المجموعة ( 2-1 ) مثلا سيظهر بعدد المرات التي يوجد فيها عناصر مشتركة بين المجموعتين . أي انه سيظهر بعدد مرات يكافئ حجم التقاطع بين المجموعتين . لذلك اذا قمنا بالتجميع حسب ازواج المجموعات ( Group By ) نستطيع فحص التقاطعات وتحديد حجم النتائج المسترجعة بإستخدام ظرف Having . ان هذا الظرف يقول بأن المجموعتين متساويتين . ولكن تتساوى المجموعتان A و B اذا كان حجمهما وتقاطعهما هو نفسه ان العبارة :
(Select Count(Member) From Equality As E3 WHERE E3.Set=Equality.Set)= Select Count(Member) From Equality As E3 WHERE E3.Set=E2.Set)
تقول انه من اجل زوج مجموعات معين ( Equality.Set , E2.Set ) من العبارة الرئيسية Select فإن حجم Equality.Set مساوي لحجم E2.Set . اما العبارة :
Count(Equality.Set)= (Select Count(Member) From Equality As E3 WHERE E3.Set=E2.Set)
فتقول بأن حجم تقاطع Equality.Set و E2.Set هو نفسه ومماثل لحجم E2.Set وهذا هو المطلوب . في الختام اعزائي الكرام اسأل الله العلي القدير ان يوفقنا واياكم لما يحبه ويرضاه واتمنى من الجميع بعد قراءة هذا الموضوع العمل على تطبيقه عمليا بحيث تأخذ كل مثال وتحاول تطبيقه على قاعدة بيانات لمعرفة كيفية الاستفادة من الحلول المقترحه وقد تخرج بفكره جديده لم تطرح وبهذا تكون قد اصطدت عصفورين بحجر واحد منها الاستفادة ومنها التطبيق . اختكم زهـره المصدر من كتاب : Some Common Data Manipulation Problems