قواعد البيانات بين النظرية والتطبيق دروس متقدمة في قواعد البيانات اكسيس

قواعد البيانات بين النظرية والتطبيق
اعزائي الكرام
السلام عليكم ورحمة الله وبركاته
سنناقش في هذاالموضوع عدداً من المشاكل التي قد تصادفكم لدى تعاملكم مع البيانات , وسنعرض الحلول الممكنه لها. واقترح أن تحاولوا إيجاد حل قبل قراءة الحل المعطى في النص . وعلينا أن نشير هنا إلي أن هناك عدة طرق لحل مشكلة معينة . في الحقيقه قد نتمكن من الحصول على حل أكثر فعاليه من الحل المقترح هنا إن الهدف الأساسي من هذه المشاكل والحلول هي إعطاؤكم غذاء فكريا نافعا .
قبل البدء, دعونا نشير إلي أن الكثير من الحلول المقترحة يعتمد على استخدام الاستعلامات الفرعيه .
يسمح 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 = CurrentDb
lRunningSum = 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 = CurrentDb
Dim qry1 As QueryDef 
Dim qry2 As QueryDef 
Dim sSQL1 As String 
Dim sSQL2 As String
On Error Resume Next 
db.QueryDefs.Delete "temp1" 
db.QueryDefs.Delete "temp2" 
On Error GoTo 0
sSQL = "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.Name
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' "
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.Name
Set 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/PartID
sSQL1 = "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 Recordset
Set 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