آموزش SQL Server – قسمت سیزدهم

در قسمت دوازدهم آموزش SQL Server ،مبحث Stored Procedure ها را در قالب مثال به شما آموزش دادیم. دراین قسمت قصد داریم Aggregate Functions هایی در رابطه با زمان را به شما آموزش بدیم.

آموزش SQL Server – قسمت سیزدهم

توجه : برای مشاهده ی تصاویر در سایز اصلی بر روی آنها کلیک کنید.

Aggregate Functions

در این قسمت میخواهیم Aggregate functions هایی در رابطه با زمان را برایتان مشخص کنیم برای مثال عبارت Select GetDate() تاریخ و زمان جاری سیستم را برمیگرداند یا برای ریزتر کردن نتیجه میتوان از عبارات YEAR(GetDate()) برای برگرداندن سال تاریخ جاری سیستم و MONTH(GetDate()) برای برگرداندن ماه و DAY(GetDate()) برای برگرداندن روز تاریخ جاری سیستم استفاده کرد.
حال میخواهیم مثالی با استفاده از این عملکردها بنویسیم. به مثال زیر دقت کنید :

Select * From
Tbl_Amanat,Tbl_Ketab

در تکه کد بالا تمام اطلاعات هردو جدول امانت و کتاب را انتخاب میکند اما در اموزش های اولیه گفته شده که این نوع join کردن
بین دو جدول که ارتباط ضربدری نامند اصلا مناسب و صحیح نیست به دلایلی که در اموزش های قبل مفصل توضیح داده شده به همبن دلیل
کد هارا بدین صورت مینویسیم

Select * From
Tbl_Amanat,Tbl_Ketab
Where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab

بدین صورت نتیجه بهتر و بهینه تر نمایش داده میشود حال میخواهیم نتیجه را کمی ریزتر و مشخص تر کنیم برای مثال تنها اطلاعات یک گیرنده را نشان دهد پس بدین صورت مینویسیم

Select * From
Tbl_Amanat,Tbl_Ketab
Where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab
And Tbl_Amanat.Code_Girande=3

در کدهای فوق تنها اطلاعات امانتی فردی با کد ۳ را نماش میدهد که در ابتدا میتوان کاری کرد که کد فرد گیرنده را توسط پروسیجرها از ورودی گرفت اما حال میخواهیم تغییری در کدها ایجاد کنیم که ماه های هر کتاب امانت گرفته شده را به همراه جمع حاصل ضرب قیمت و تعداد کتاب مورد نظر را محاسبه و در نتیجه نمایش دهد پس کدها را بدین صورت تغییر میدهیم

select SUM(Qeymat*tedad) as total,MONTH(Tbl_Amanat.Zaman_Daryaft) As MonthNo
from Tbl_Ketab,Tbl_Amanat
where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab And
Tbl_Amanat.Code_Girande=3
Group By MONTH(Tbl_Amanat.Zaman_Daryaft)

کدهای بالا همانگونه مشخص است از دو Aggregate Function نوع SUM و MONTH استفاده کرده ایم که SUM برای بدست اوردن حاصل جمع ضرب قیمت در تعداد و MONTH برای بدست اوردن ماه زمان دریافت

یک کتاب به عنوان امانت که هردو برای فهم بیشتر نام گزاری نیز شده اند و چون از Aggregate ها استفاده کرده ایم باید در انتها از Group By نیز استفاده کنیم پس ما نیز Group By را براساس ماه بدست اماده از زمان دریافت کتاب تنظیم کرده ایم.

پس از اجرای این دستورات با نتییجه ای مانند زیر مواجه میشویم :

آموزش SQL Server – قسمت سیزدهم

همانگونه که مشاهده میکنید در نتیجه مشخص شده که مثلا در برج ۸ کتابی که حاصل جمع ضرب فیلد قیمت و تعداد ان برابر ۳۷۵۰۰۰۰ میباشد امانت گرفته شده توسط فردی با کد ۳ و در برج ۹ و۱۱ نیز به همین ترتیب .همانگونه که مشخص است نتیجه فقط در برجهایی که کتاب به امانت گرفته شده به ما نمایش داده شده اما حال شاید ما بخواهبم در برج های دیگر نیز به ما نمایش داده شود و مقدار فیلد total را برایمان صفر بگزارد چون در واقع در ان ماهها کتابی به فرد مورد نظر امانت داده نشده پس مقدار total برابر Null میباشد اما ما میخواهیم به ما مقدار صفر را نمایش دهد پس برای این منظور باید از جدول های موقت استفاده کنیم به صورت زیر

create Table #t
(
MonthNum int
)
insert #t
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

select SUM(Qeymat*tedad) as total,MONTH(Tbl_Amanat.Zaman_Daryaft) As MonthNo
from Tbl_Ketab,Tbl_Amanat
where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab And
Tbl_Amanat.Code_Girande=@code
Group By MONTH(Tbl_Amanat.Zaman_Daryaft)>

همانگونه که مشاهده میکنید در بالا ما ابتدا یک جدول موقت با نام #t با یک ستون به نام MonthNum تعریف کرده ایم سپس مقادیر ۱ تا ۱۲ را که به تعداد ماه ها اشاره دارند را درونش Insert کرده ایم سپس برای ایجاد ارتباطی بین جدول موقت و کوئری که نوشته شده باید کوئری را نیز به نوعی تبدیل کنیم که مانند جداول رفتار کند پس کدهارا به صورت زیر تغییر میدهیم

create Table #t
(
MonthNum int
)
insert #t
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

select MonthNum , ISNULL(total,0) as total from(select SUM(Qeymat*tedad) as
 total,MONTH(Tbl_Amanat.Zaman_Daryaft) As MonthNo
from Tbl_Ketab,Tbl_Amanat
where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab And
Tbl_Amanat.Code_Girande=3
Group By MONTH(Tbl_Amanat.Zaman_Daryaft)
)as temp right outer join #t on temp.MonthNo=#t.MonthNum

drop table #t

همانگونه که مشاهده میکنید برای تبدیل کوئری به جدول از Select قبل از کوئری استفاده کرده ایم و کوئری را درون این Selevt from() قرار داده ایم حال برای اینکه بگوییم چه چیز هایی را از این کوئری انتخاب کن و به ما نمایش بده از MonthNum که همان ستون ایجاد شده درون جدول موقت میباشد و ISNULL(total,0) استفاده شده که این بدین معنی میباشد که هر جا که total برابر NULL بود را صفر قرار ده و در انتها نیز نامی برای جدول انتخاب کرده ایم و از Right Outer Join (در اموزش های قبل به طور مفصل توضیح داده شده) برای اتصال بین جدول موقت و کوئری استفاده شده که همانگونه که مشاهده میکنید از فیلد MonthNum و MonthNo برای فیلد مشترک استفاده شده ودر اخر نیز جدول را drop کرده ایم.
حال میتوان تمامی این کدها را درون یک پروسیجر قرار داد و کد گیرنده را به عنوان ورودی در یافت کرد پس کدهارا بدین صورت تغییر میدهیم

create procedure test4 (@code int)
as
begin

create Table #t
(
MonthNum int
)
insert #t
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

select MonthNum,ISNULL(total,0) as total from(select SUM(Qeymat*tedad) as
 total,MONTH(Tbl_Amanat.Zaman_Daryaft) As MonthNo
from Tbl_Ketab,Tbl_Amanat
where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab And
Tbl_Amanat.Code_Girande=@code
Group By MONTH(Tbl_Amanat.Zaman_Daryaft)
)as temp right outer join #t on temp.MonthNo=#t.MonthNum

drop table #t
end
go
execute test4 3

در بالا یک پروسیجر ساده تعریف کرده ایم با یک ورودی از نوع int و در انتها نیز با دستورات Go دستور به اجرای ان با مقدار ورودی ۳ داده ایم در نتیجه نیز Result زیر را مشاهده میکنید

آموزش SQL Server – قسمت سیزدهم

همانگونه که واضح میباشد درماه های ۸ و ۹ و ۱۱ مقادیری وجود دارد چون کتابی به امانت گرفته شده توسط فرد مورد نظر اما در ماه های دیگر مقادیر ۰ میباشد چون فرد مورد نظر کتابی به امانت نبرده.

مدرس : خانم مهندس داودی نژاد

آموزش SQL Server – قسمت چهاردهم

آموزش SQL Server – قسمت دوازدهم