کاربرد تابع DATEDIFF در Query نویسی - قسمت چهارم (آخر)

شنبه 7 آذر 1394

در این مقاله با یک دیتابیس کوچک اما با پتانسیل، برای نوشتن Query آشنا میشویم

در ادامه قسمت سوم این مقاله در این جلسه با Query های زیر و نکات آن آشنا شده

Query دهم : 

هر خانومی چند اتفاق در زندگی خود داشته است ؟ (فرزندان - ازدواج - طلاق - فوت همسر و فرزند)

در این مثال که تشکیل شده از چندین Query میتوان به قسمت های مختلفی تجزیه شود 

تعداد فرزندان با یک Query ساده که از Group By و تابع Count استفاده میکند به دست میاد .

برای قسمت های دیگر هم به همین شکل است . پس ما چندین Query با فرمت های مشابه داریم 

که در ادامه آن را مشاهده میکنید . 

 

SELECT  CurrentWoman.FirstName + ' ' + CurrentWoman.LastName AS Woman ,
        ISNULL(Marriages, 0) AS Marriages ,
        ISNULL(Divorces, 0) AS Divorces ,
        ISNULL(Children, 0) AS Children ,
        ISNULL(HusbandDeaths, 0) AS HusbandDeaths ,
        ISNULL(ChildDeaths, 0) AS ChildDeaths ,
        ISNULL(Marriages, 0) +
		ISNULL(Divorces, 0) +
		ISNULL(Children, 0) +
		ISNULL(HusbandDeaths, 0) + 
		ISNULL(ChildDeaths, 0) AS TotalLifeEvents

FROM    Person CurrentWoman

-----------------------Marriages--------------------------------

        LEFT OUTER JOIN ( SELECT    WifeID ,
                                    COUNT(*) AS Marriages
                          FROM      Marriage
                          GROUP BY  WifeID
                        ) M ON CurrentWoman.PersonID = M.WifeID

-----------------------Children--------------------------------

        LEFT OUTER JOIN ( SELECT    MotherID ,
                                    COUNT(*) AS Children
                          FROM      Person
                          WHERE     MotherID IS NOT NULL
                          GROUP BY  MotherID
                        ) C ON CurrentWoman.PersonID = C.MotherID

-----------------------Divorces--------------------------------

        LEFT OUTER JOIN ( SELECT    WifeID ,
                                    COUNT(*) AS Divorces
                          FROM      Marriage
                          WHERE     DateOfDivorce IS NOT NULL
                          GROUP BY  WifeID
                        ) D ON CurrentWoman.PersonID = D.WifeID

-----------------------ChildDeaths--------------------------------

        LEFT OUTER JOIN ( SELECT    Child.MotherID ,
                                    COUNT(*) AS ChildDeaths
                          FROM      Person Child
                                    INNER JOIN Person M 
									ON Child.MotherID = M.PersonID
                                    AND Child.DateOfDeath < M.DateOfDeath
                          GROUP BY  Child.MotherID
                        ) CD ON CurrentWoman.MotherID = CD.MotherID

-----------------------HusbandDeaths--------------------------------

        LEFT OUTER JOIN ( SELECT    Person.PersonID ,
                                    COUNT(*) AS HusbandDeaths
                          FROM      Person
                                    INNER JOIN Marriage ON Person.PersonID = Marriage.WifeID
                                    INNER JOIN Person H ON Marriage.HusbandID = H.PersonID
                                                     AND H.DateOfDeath < Person.DateOfDeath
                          GROUP BY  Person.PersonID
                        ) HD ON CurrentWoman.PersonID = HD.PersonID

-------------------------------------------------------------------

WHERE   CurrentWoman.Gender = 'F'
ORDER BY TotalLifeEvents DESC;

 

و اینجا نیز از تابع ISNULL که در جلسات قبل توضیح داده شد استفاده شده است

 

Query یازدهم : چه اشخاصی بزرگ خاندان هستند !!
 
برای این مثال اشخاصی را لیست میکنیم که جنسیت آنها مرد باشد و مشخصات ایی برای والدین آنها ثبت نشده باشد همچنین با اشخاصی ازدواج کرده اند که برای آنها نیز مشخصات والدینشان ثبت نشده باشد.
 
قسمت اول راحت است اما برای قسمت دوم راه حل ایی که وجود دارد استفاده از NOT IN میباشد که در ادامه خواهید دید 
 
 
SELECT  
        Person.FirstName + ' ' + Person.LastName AS Patriarch
FROM    Person
WHERE   Person.Gender = 'M'
        AND Person.MotherID IS NULL
        AND Person.FatherID IS NULL
        AND PersonID NOT IN 
      ( SELECT  Person.PersonID
        FROM    Person
                INNER JOIN Marriage ON Person.PersonID = Marriage.HusbandID
                INNER JOIN Person Wife ON Marriage.WifeID = Wife.PersonID
        WHERE   Wife.MotherID IS NOT NULL
                OR Wife.FatherID IS NOT NULL );

 

امیدوارم با این مواردی که در این چند جلسه ی کوتاه عنوان شد تونسته باشم تا حدی توانایی شما دوستان رو در نوشتن Query ها افزایش داده باشم . 

نکته : مطمئنا این مثال ها همه ی مثال هایی نبود که میشد نوشت و شما هم میتونید مثال های خودتون رو تعریف کنید و Query شو بنویسید. 

موفق باشید 

سروش صدر

فایل های ضمیمه

سروش صدر

نویسنده 4 مقاله در برنامه نویسان

کاربرانی که از نویسنده این مقاله تشکر کرده اند

در صورتی که در رابطه با این مقاله سوالی دارید، در تاپیک های انجمن مطرح کنید