GROUP BY بند(عبارت SELECT) به شما امکان می دهد داده ها (ردیف ها) را بر اساس مقدار یک ستون یا چندین ستون یا عبارت گروه بندی کنید. نتیجه مجموعه ای از ردیف های خلاصه خواهد بود.
هر ستون در لیست انتخاب باید در بند GROUP BY وجود داشته باشد، تنها استثناها ثابت ها و ستون ها هستند - عملوندهای توابع جمع.
یک جدول را می توان با هر ترکیبی از ستون های آن گروه بندی کرد.
توابع مجموعبرای به دست آوردن یک مقدار کل واحد از یک گروه از ردیف ها استفاده می شود. همه توابع انبوه محاسبات را بر روی یک آرگومان انجام می دهند که می تواند یک ستون یا یک عبارت باشد. هر تابع مجموع به یک مقدار ثابت ارزیابی می شود که در یک ستون جداگانه در نتیجه نمایش داده می شود.
توابع انبوه در لیست ستونی یک عبارت SELECT مشخص می شوند که می تواند شامل یک عبارت GROUP BY نیز باشد. اگر دستور SELECT حاوی یک بند GROUP BY نباشد و لیست ستون انتخاب حداقل یک تابع مجموع داشته باشد، نباید شامل ستون های ساده باشد. از سوی دیگر، یک لیست انتخابی از ستون‌ها می‌تواند حاوی نام ستون‌هایی باشد که آرگومان‌هایی برای تابع انبوه نیستند، اگر آن ستون‌ها به عنوان آرگومان‌های عبارت GROUP BY استفاده شوند.
اگر پرس و جو حاوی یک عبارت WHERE باشد، توابع جمع مقداری را برای نتایج انتخاب محاسبه می کنند.
توابع جمع MIN و MAXکوچکترین و بزرگترین مقادیر ستون به ترتیب محاسبه می شود. آرگومان ها می توانند اعداد، رشته ها و تاریخ باشند. تمام مقادیر NULL قبل از محاسبه حذف می شوند (یعنی در نظر گرفته نمی شوند).
تابع مجموع SUMمجموع کل مقادیر ستون ها را محاسبه می کند. فقط اعداد می توانند آرگومان باشند. استفاده از پارامتر DISTINCT هر مقدار تکراری در ستون را قبل از اعمال تابع SUM حذف می کند. به همین ترتیب، قبل از اعمال این تابع جمع، تمام مقادیر NULL را حذف کنید.
عملکرد جمع AVGمیانگین تمام مقادیر یک ستون را برمی گرداند. آرگومان ها همچنین می توانند فقط اعداد باشند و تمام مقادیر NULL قبل از ارزیابی حذف می شوند.
تابع مجموع COUNTدارای دو شکل مختلف:
- COUNT (col_name) - تعداد مقادیر موجود در ستون col_name را می شمارد، مقادیر NULL شمارش نمی شوند.
- COUNT (*) - تعداد ردیف های جدول را می شمارد، مقادیر NULL نیز در نظر گرفته می شود.
اگر پرس و جو از کلمه کلیدی DISTINCT استفاده کند، تمام مقادیر ستون تکراری قبل از استفاده از تابع COUNT حذف می شوند.
COUNT_BIG تابععملکرد مشابه COUNT. تنها تفاوت بین این دو در نوع نتیجه ای است که برمی گردانند: COUNT_BIG همیشه مقادیر BIGINT را برمی گرداند، در حالی که COUNT مقادیر INTEGER داده را برمی گرداند.
V داشتن پیشنهادشرطی را تعریف می کند که برای گروهی از ردیف ها اعمال می شود. برای گروه‌های ردیف به معنای عبارت WHERE برای محتویات جدول مربوطه است (WHERE قبل از گروه‌بندی، HAVING بعد اعمال می‌شود).
این درس مبحث sql تغییر نام یک ستون (فیلدها) را با استفاده از کلمه تابع AS پوشش می دهد. همچنین به مبحث توابع انبوه در sql پرداخت. نمونه های خاصی از درخواست ها تجزیه و تحلیل خواهند شد
می توانید نام ستون ها را در پرس و جوها تغییر نام دهید. این باعث می شود نتایج خواناتر شوند.
در SQL، تغییر نام فیلدها با استفاده از کلمه کلیدی ASکه برای تغییر نام فیلدها در مجموعه نتایج استفاده می شود
نحو:
انتخاب کنید<имя поля>مانند<псевдоним>از جانب ...
بیایید به مثالی از تغییر نام در SQL نگاه کنیم:
نمونه DB "Institute":نمایش نام معلمان و حقوق آنها، برای معلمانی که حقوق آنها زیر 15000 است، نام فیلد زارپلاتا را به "دستمزد کم"
✍ راه حل:
تغییر نام ستون ها در SQL اغلب ضروری است هنگام محاسبه مقادیر مرتبط با چندین فیلدجداول بیایید یک مثال را در نظر بگیریم:
نمونه DB "Institute":از جدول معلمان، فیلد نام را خروجی بگیرید و میزان حقوق و پاداش را محاسبه کنید و نام فیلد را نام ببرید "حقوق_جایزه"
✍ راه حل:
| 1 2 | نام را انتخاب کنید، (zarplata + premia) AS zarplata_premia FROM معلمان; |
نام را انتخاب کنید، (zarplata + premia) AS zarplata_premia FROM معلمان;
نتیجه:
توابع جمع در SQL
توابع جمع در sql برای بدست آوردن مجموع و محاسبه عبارات استفاده می شود:
همه توابع مجموع یک مقدار واحد را برمی‌گردانند.
توابع COUNT، MIN و MAX برای هر نوع داده ای اعمال می شود.
توابع SUM و AVG فقط برای فیلدهای عددی استفاده می شوند.
بین توابع COUNT (*) و COUNT () تفاوت وجود دارد: دومی مقادیر NULL را هنگام شمارش در نظر نمی گیرد.
مهم:هنگام کار با توابع جمع در SQL، از یک کلمه خاص استفاده می شود مانند
نمونه DB "Institute":دریافت ارزش بالاترین حقوق در بین معلمان، نمایش نتیجه به عنوان "Max_zp"
✍ راه حل:
| SELECT MAX (zarplata) AS max_zp FROM معلمان; |
SELECT MAX (zarplata) AS max_zp FROM معلمان;
نتایج:
بیایید یک مثال پیچیده تر از استفاده از توابع جمع در sql را در نظر بگیریم.
✍ راه حل:
GROUP BY clause در SQL
گروه با بند در sql معمولاً همراه با توابع جمع استفاده می شود.
توابع انبوه بر روی تمام ردیف های پرس و جو به دست آمده انجام می شود. اگر پرس و جو حاوی یک عبارت GROUP BY باشد، هر مجموعه از ردیف های مشخص شده در عبارت GROUP BY یک گروه را تشکیل می دهد و توابع جمع اجرا می شوند. برای هر گروه جداگانه.
مثالی را با جدول درس ها در نظر بگیرید: 
مثال:
مهم:بنابراین، در نتیجه استفاده از GROUP BY، تمام ردیف های خروجی پرس و جو به گروه هایی تقسیم می شوند که با ترکیبات یکسانی از مقادیر در این ستون ها مشخص می شوند (یعنی توابع جمع برای هر گروه به طور جداگانه انجام می شود).
باید در نظر داشت که وقتی توسط یک فیلد حاوی مقادیر NULL گروه بندی می شوند، همه این رکوردها در یک گروه قرار می گیرند.
برای انواع مختلف چاپگرها، میانگین هزینه و مقدار آنها را تعیین کنید (یعنی به طور جداگانه برای لیزر، جوهر افشان و ماتریس). از توابع جمع استفاده کنید. نتیجه باید به این صورت باشد:
داشتن بیانیه SQL
عبارت HAVING در SQL برای اعتبارسنجی مقادیر مورد نیاز است. که با استفاده از تابع تجمیع پس از گروه بندی به دست می آیند(پس از استفاده از GROUP BY). چنین چکی نمی توان در یک بند WHERE گنجانده شود.
مثال: فروشگاه کامپیوتر دی بی... میانگین قیمت کامپیوترهایی با سرعت پردازنده یکسان را محاسبه کنید. محاسبه فقط برای گروه هایی که میانگین قیمت آنها کمتر از 30000 است.
- توابع مجموعمانند نام فیلدها در دستور SELECT استفاده می شود، با یک استثنا: آنها نام فیلد را به عنوان آرگومان می گیرند. با توابع جمعو AVGفقط از فیلدهای عددی می توان استفاده کرد. با توابع COUNT، MAX و MINهر دو فیلد عددی و کاراکتری را می توان استفاده کرد. هنگامی که با فیلدهای کاراکتر استفاده می شود حداکثرو MINآنها را به معادل ASCII ترجمه می کند و آنها را به ترتیب حروف الفبا پردازش می کند. برخی از DBMS ها اجازه استفاده از توده های تودرتو را می دهند، اما این یک انحراف از استاندارد ANSI با تمام عواقب بعدی است.
به عنوان مثال، می توانید تعداد دانش آموزانی را که در هر رشته امتحانات قبول شده اند محاسبه کنید. برای انجام این کار، باید یک پرس و جو گروه بندی شده توسط فیلد "Discipline" را اجرا کنید و در نتیجه نام رشته و تعداد خطوط گروه را برای این رشته نمایش دهید. استفاده از کاراکتر * به عنوان آرگومان برای تابع COUNT به این معنی است که تمام خطوط گروه شمارش می شوند.
R1 را انتخاب کنید. نظم و انضباط، COUNT (*)
GROUP BY R1.
نتیجه:
رشته R1 SELECT، COUNT (*)
WHERE R1. نمره NULL نیست
GROUP BY R1.
نتیجه:
قبل از گروه بندی در مجموعه تاپل ها قرار نخواهد گرفت، بنابراین تعداد تاپل های گروه برای رشته "نظریه اطلاعات" 1 عدد کمتر خواهد بود.
اگر درخواست را به روش زیر بنویسید می توان نتیجه مشابهی به دست آورد:
R1 را انتخاب کنید. نظم و انضباط، COUNT (R1. ارزیابی)
گروه توسط R1. انضباط؛
عملکرد COUNT (ATTRIBUTE NAME)تعداد مقادیر تعریف شده را در یک گروه بر خلاف یک تابع می شمارد شمردن (*)،که تعداد خطوط گروه را می شمارد. در واقع، در گروه با رشته "نظریه اطلاعات" 4 خط وجود دارد، اما فقط 3 مقدار خاص از ویژگی "ارزیابی".
قوانین مدیریت مقادیر تهی در توابع انبوه
اگر هر یک از مقادیر در ستون برابر باشد خالیآنها هنگام محاسبه نتیجه تابع حذف می شوند.
اگر همه مقادیر در یک ستون برابر باشند خالی، سپس حداکثر حداقل میانگین مجموع = تهی، شمارش = 0 (صفر).
اگر جدول خالی است، شمارش (*) = 0 .
توابع تجمیع را می توان بدون عملیات گروه بندی اولیه نیز استفاده کرد، در این حالت کل رابطه به عنوان یک گروه در نظر گرفته می شود و برای هر گروه می توان یک مقدار را برای این گروه محاسبه کرد.
قوانینی برای تفسیر توابع کل
توابع مجموع را می توان در لیست خروجی گنجاند و سپس آنها را در کل جدول اعمال کرد.
SELECT MAX (امتیاز) از R1حداکثر امتیاز را در جلسه می دهد.
جمع را از R1 انتخاب کنیدمجموع تمام ارزیابی ها را در هر جلسه ارائه می دهد.
AVG (امتیاز) را از R1 انتخاب کنیدنمره متوسط را برای کل جلسه می دهد.
2 نتیجه: "width = 640" با مراجعه مجدد به پایگاه داده Session (جدول R1)، تعداد امتحانات با موفقیت را خواهیم یافت:
COUNT (*) را انتخاب کنید اجاره شده است _ امتحانات
WHERE درجه 2;
نتیجه:
آرگومان توابع جمع می تواند ستون های جداول جداول باشد. برای محاسبه، به عنوان مثال، تعداد مقادیر متمایز یک ستون خاص در یک گروه، لازم است از کلمه کلیدی DISTINCT همراه با نام ستون استفاده شود. بیایید تعداد نمرات دریافتی مختلف برای هر رشته را محاسبه کنیم:
رشته R1 SELECT، COUNT (ارزیابی متمایز R1)
WHERE R1. نمره NULL نیست
GROUP BY R1.
نتیجه:
اگر شرط صریح را در قسمت WHERE حذف کنید، همین نتیجه به دست می آید، در این صورت پرس و جو به شکل زیر خواهد بود:
R1 را انتخاب کنید. نظم و انضباط، COUNT (DISTINCT R1. ارزیابی)
گروه توسط R1. انضباط؛
عملکرد COUNT (R1.Evaluation متمایز)فقط قطعی در نظر می گیرد مختلفارزش های.
برای به دست آوردن نتیجه مطلوب در این مورد، لازم است یک تبدیل اولیه از نوع داده ستون "امتیاز" انجام شود و آن را به یک نوع واقعی برساند، سپس نتیجه محاسبه میانگین یک عدد نخواهد بود. عدد صحیح در این صورت درخواست به صورت زیر خواهد بود:
2 گروه توسط R2. گروه، R1. انضباط؛ در اینجا، تابع CAST () ستون "امتیاز" را به یک نوع داده معتبر تبدیل می کند. "عرض = 640" R2.Group، R1.Discipline، شمارش (*) به عنوان مجموع، AVG (بازیگر (امتیاز به صورت اعشاری (3،1))) را به عنوان میانگین_نقطه انتخاب کنید.
از R1، R2
جایی که R1. نام کامل = R2. نام و R1. نمره صفر نیست
و R1. درجه 2
گروه بر اساس R2. گروه، R1. انضباط؛
در اینجا تابع قالب ()ستون Score را به یک نوع داده معتبر تبدیل می کند.
شما نمی توانید از توابع جمع در عبارت WHERE استفاده کنید زیرا شرایط در این بخش بر حسب یک ردیف و توابع جمع بر اساس گروه های ردیف ارزیابی می شوند.
عبارت GROUP BY به شما این امکان را می دهد که زیرمجموعه ای از مقادیر یک فیلد خاص را بر حسب فیلد دیگر تعریف کنید و یک تابع تجمیع را به زیر مجموعه اعمال کنید. این امکان ترکیب فیلدها و توابع انباشته در یک عبارت SELECT را فراهم می کند. توابع جمع را می توان هم در عبارت برای خروجی نتایج ردیف SELECT و هم در عبارت پردازش گروه های HAVING تولید شده استفاده کرد. در این حالت، هر تابع مجموع برای هر گروه انتخاب شده محاسبه می شود. مقادیر به دست آمده در هنگام محاسبه توابع کل می تواند برای نمایش نتایج مربوطه یا برای شرایط انتخاب گروه ها استفاده شود.
بیایید یک پرس و جو بسازیم که گروه هایی را که در آنها بیش از یک دس در یک رشته در امتحانات به دست آمده را نشان دهد:
1 نتیجه: "width = 640" R2 را انتخاب کنید. گروه
از R1، R2
WHERE R1. نام کامل = R2. نام و
امتیاز R1 = 2
GROUP BY R2. گروه، R1. نظم و انضباط
داشتن تعداد (*) 1;
نتیجه:
ما یک DB "Bank" داریم که از یک جدول F تشکیل شده است که رابطه F را ذخیره می کند و حاوی اطلاعات حساب های موجود در شعب یک بانک خاص است:
کل موجودی حساب را در شعب پیدا کنید. می توان با انتخاب SUM از جدول برای هر شاخه، یک پرس و جو جداگانه برای هر یک از آنها ایجاد کرد، اما عملیات GROUP BY همه آنها را در یک دستور قرار می دهد:
انتخاب کنید شاخه , مجموع ( باقی مانده )
گروه به شاخه;
دسته بندی بر اساستوابع مجموع را به طور مستقل برای هر گروهی که با مقدار فیلد Branch مشخص شده است اعمال می کند. این گروه از خطوطی با همان مقدار فیلد Branch و تابع تشکیل شده است جمعبرای هر گروه به طور جداگانه اعمال می شود، یعنی کل موجودی حساب به طور جداگانه برای هر شعبه محاسبه می شود. مقدار فیلدی که برای آن اعمال می شود دسته بندی بر اساس، طبق تعریف، تنها یک مقدار در هر گروه خروجی دارد، همانطور که نتیجه یک تابع جمع دارد.
5000; آرگومان های موجود در عبارت HAVING از همان قوانینی پیروی می کنند که در عبارت SELECT که از GROUP BY استفاده می کند. آنها باید یک مقدار در هر گروه خروجی داشته باشند. "عرض = 640" فرض کنید فقط آن شعبه‌ها را انتخاب می‌کنید که مجموع موجودی حساب‌های آنها بیش از 5000 دلار است و همچنین کل موجودی شعب انتخاب‌شده. برای نمایش شعب با کل موجودی بیش از 5000 دلار، باید از بند HAVING استفاده کنید. بند HAVING معیارهایی را که برای حذف گروه‌های خاص از خروجی استفاده می‌شود، تعریف می‌کند، درست همانطور که عبارت WHERE برای ردیف‌های جداگانه انجام می‌دهد.
دستور صحیح به صورت زیر خواهد بود:
SELECT شعبه، SUM (موجودی)
دسته بندی بر اساس شاخه
داشتن مجموع ( باقی مانده ) 5 000;
استدلال در یک جمله داشتناز همان قوانینی که در جمله است پیروی کنید انتخاب کنیدکجا استفاده می شود دسته بندی بر اساس... آنها باید یک مقدار در هر گروه خروجی داشته باشند.
دستور زیر ممنوع خواهد بود:
SELECT شعبه، SUM (موجودی)
گروه به شعبه
تاریخ افتتاح = 12/27/2004 ;
رشته تاریخ افتتاحیهنمی توان در یک جمله استفاده کرد داشتنزیرا در هر گروه نمایش می تواند بیش از یک مقدار داشته باشد. برای جلوگیری از چنین وضعیتی، پیشنهاد داشتنفقط باید به مجموع ها و فیلدهای انتخاب شده اشاره داشته باشد دسته بندی بر اساس... یک راه درست برای ایجاد پرس و جو بالا وجود دارد:
SELECT شعبه، SUM (موجودی)
WHERE تاریخ افتتاح = '27/12/2004'
گروه به شاخه;
معنای این استعلام به شرح زیر است: مقدار موجودی هر شعبه از حساب های افتتاح شده در 27 دسامبر 2004 را بیابید.
همانطور که قبلاً گفته شد، HAVING فقط می تواند از آرگومان هایی استفاده کند که در هر گروه خروجی مقدار یکسانی دارند. در عمل، ارجاعات به توابع انبوه عمومی ترین هستند، اما فیلدهایی که با GROUP BY انتخاب شده اند نیز معتبر هستند. به عنوان مثال، ما می خواهیم کل موجودی حساب شعب در سن پترزبورگ، پسکوف و اوریوپینسک را ببینیم:
SELECT شعبه، SUM (موجودی)
از F، Q
WHERE F. Branch = Q. Branch
گروه به شعبه
داشتن شعبه در («سن پترزبورگ»، «پسکوف»، «اوریوپینسک»)؛
100000; اگر کل موجودی بیش از 100000 دلار باشد، آن را در نسبت حاصل می بینیم، در غیر این صورت نسبت خالی دریافت می کنیم. "عرض = 640"
بنابراین، در عبارات حسابی محمول های موجود در شرط انتخاب عبارت HAVING، می توانید مستقیماً فقط از مشخصات ستون هایی که به عنوان ستون های گروه بندی در بند GROUP BY مشخص شده اند استفاده کنید. بقیه ستون ها را می توان فقط در مشخصات توابع جمعی COUNT، SUM، AVG، MIN و MAX مشخص کرد، که در این مورد مقداری مجموع را برای کل گروه ردیف ها محاسبه می کند. نتیجه اجرای عبارت HAVING یک جدول گروه بندی شده است که فقط شامل گروه های ردیفی است که نتیجه ارزیابی شرط انتخاب در قسمت HAVING برای آنها TRUE است. به طور خاص، اگر یک عبارت HAVING در پرس و جوی وجود داشته باشد که حاوی GROUP BY نباشد، نتیجه اجرای آن یا یک جدول خالی یا نتیجه بخش های قبلی عبارت جدول است که به عنوان یک گروه بدون در نظر گرفته می شود. گروه بندی ستون ها بیایید به یک مثال نگاه کنیم. فرض کنید می‌خواهیم کل موجودی‌ها را برای همه شعبه‌ها نمایش دهیم، اما فقط در صورتی که بیش از 100000 دلار باشد. در این صورت، کوئری ما شامل عملیات گروه‌بندی نخواهد بود، بلکه شامل یک بخش HAVING خواهد بود و به شکل زیر خواهد بود:
انتخاب جمع ( باقی مانده )
داشتن مجموع ( باقی مانده ) 100 000;
اگر کل موجودی بیش از 100000 دلار باشد، آن را در نسبت حاصل می بینیم، در غیر این صورت نسبت خالی دریافت می کنیم.
بخش‌های فرعی زیر سایر بندهای SELECT را که می‌توان در پرس‌و‌جوها استفاده کرد، و همچنین توابع جمع‌آوری و مجموعه دستورات را توضیح می‌دهد. یادآوری می کنم که تا اینجا به استفاده از عبارت WHERE پرداخته ایم و در این مقاله به بندهای GROUP BY، ORDER BY و HAVING می پردازیم و نمونه هایی از استفاده از این بندها را در ترکیب با توابع جمع ارائه می کنیم. که در Transact-SQL پشتیبانی می شوند.
GROUP BY بند
پیشنهاد دسته بندی بر اساسمجموعه ردیف انتخاب شده را گروه بندی می کند تا مجموعه ای از ردیف های خلاصه را بر اساس مقادیر یک یا چند ستون یا عبارت بدست آورد. یک مورد استفاده ساده برای بند GROUP BY در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. SELECT Job FROM Works_On GROUP BY Job.
در این مثال، موقعیت های کارمند نمونه گیری و گروه بندی می شوند.
در مثال بالا، عبارت GROUP BY یک گروه جداگانه برای تمام مقادیر ممکن (از جمله NULL) برای ستون Job ایجاد می کند.
استفاده از ستون ها در بند GROUP BY باید شرایط خاصی را داشته باشد. به طور خاص، هر ستون در لیست انتخابی پرس و جو باید در عبارت GROUP BY نیز ظاهر شود. این الزام برای ثابت ها و ستون هایی که بخشی از یک تابع جمع هستند اعمال نمی شود. (توابع مجموع در زیر بخش بعدی مورد بحث قرار می گیرند.) این منطقی است زیرا فقط ستون‌های عبارت GROUP BY یک مقدار برای هر گروه تضمین می‌شود.
یک جدول را می توان با هر ترکیبی از ستون های آن گروه بندی کرد. مثال زیر گروه بندی ردیف ها در جدول Works_on را با دو ستون نشان می دهد:
از SampleDb استفاده کنید. SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;
نتیجه این پرس و جو:
بر اساس نتایج پرس و جو، می توانید ببینید که 9 گروه با ترکیب های مختلف از شماره پروژه و عنوان وجود دارد. توالی نام ستون ها در بند GROUP BY نباید مانند لیست ستون SELECT باشد.
توابع مجموع
از توابع جمع برای بدست آوردن مقادیر مجموع استفاده می شود. تمام توابع کل را می توان به دسته های زیر تقسیم کرد:
توابع جمع معمولی؛
توابع جمع آماری;
توابع جمعی تعریف شده توسط کاربر.
توابع مجموع تحلیلی
در اینجا ما به سه نوع اول توابع جمع نگاه خواهیم کرد.
توابع جمع منظم
Transact-SQL از شش تابع جمع زیر پشتیبانی می کند: MIN, حداکثر, جمع, AVG, شمردن, COUNT_BIG.
همه توابع انبوه محاسبات را بر روی یک آرگومان انجام می دهند که می تواند یک ستون یا یک عبارت باشد. (تنها استثنا شکل دوم دو تابع، COUNT و COUNT_BIG، به ترتیب COUNT (*) و COUNT_BIG (*) است.) هر تابع جمعی به یک مقدار ثابت ارزیابی می شود که در یک ستون نتیجه جداگانه ظاهر می شود.
توابع انبوه در لیست ستونی یک عبارت SELECT مشخص می شوند که می تواند شامل یک عبارت GROUP BY نیز باشد. اگر دستور SELECT شامل یک بند GROUP BY نباشد و لیست ستون انتخاب حداقل یک تابع تجمعی داشته باشد، نباید شامل ستون‌های ساده باشد (به غیر از ستون‌هایی که به عنوان آرگومان‌های تابع تجمع استفاده می‌شوند). بنابراین، کد موجود در مثال زیر نادرست است:
از SampleDb استفاده کنید. نام خانوادگی، MIN (Id) FROM Employee را انتخاب کنید.
در اینجا، ستون LastName جدول Employee نباید در لیست ستون انتخاب باشد زیرا آرگومان تابع جمع نیست. از سوی دیگر، یک لیست انتخابی از ستون‌ها می‌تواند حاوی نام ستون‌هایی باشد که آرگومان‌هایی برای تابع انبوه نیستند، اگر آن ستون‌ها به عنوان آرگومان‌های عبارت GROUP BY استفاده شوند.
یک آرگومان تابع مجموع را می توان با یکی از دو کلمه کلیدی ممکن قبل از آن بیان کرد:
همهنشان می دهد که محاسبات روی تمام مقادیر موجود در ستون انجام می شود. این پیش فرض است.
متمایزمشخص می کند که فقط از مقادیر ستون منحصر به فرد برای محاسبات استفاده می شود.
توابع جمع MIN و MAX
توابع مجموع MIN و MAX به ترتیب کوچکترین و بزرگترین مقادیر را در یک ستون محاسبه می کنند. اگر پرس و جو حاوی یک عبارت WHERE باشد، توابع MIN و MAX کوچکترین و بزرگترین مقادیر ردیفی را که با شرایط مشخص شده مطابقت دارد، برمی گرداند. مثال زیر استفاده از تابع جمع آوری MIN را نشان می دهد:
از SampleDb استفاده کنید. - 2581 SELECT MIN (Id) AS "Minimum ID value" FROM Employee را برمی گرداند.
نتیجه ای که در مثال بالا برگردانده شده است چندان آموزنده نیست. به عنوان مثال، نام خانوادگی کارمندی که این شماره را دارد نامشخص است. اما شما نمی توانید این نام خانوادگی را به روش معمول دریافت کنید، زیرا همانطور که قبلا ذکر شد، نمی توانید به صراحت ستون LastName را مشخص کنید. برای به دست آوردن نام خانوادگی این کارمند به همراه کمترین تعداد پرسنل کارمند از یک استعلام فرعی استفاده می شود. مثال زیر استفاده از چنین پرس و جوی فرعی را نشان می دهد، که در آن پرس و جوی فرعی حاوی عبارت SELECT از مثال قبلی است:
نتیجه اجرای پرس و جو:
استفاده از تابع تجمع MAX در مثال زیر نشان داده شده است:
توابع MIN و MAX همچنین می توانند رشته ها و تاریخ ها را به عنوان آرگومان بپذیرند. در مورد آرگومان رشته ای، مقادیر با استفاده از ترتیب مرتب سازی واقعی مقایسه می شوند. برای همه آرگومان‌های داده موقت نوع تاریخ، پایین‌ترین مقدار ستون اولین تاریخ و بالاترین مقدار آخرین تاریخ است.
کلمه کلیدی DISTINCT را می توان با توابع MIN و MAX استفاده کرد. تمام مقادیر NULL از ستون های آرگومان خود قبل از استفاده از توابع جمع MIN و MAX حذف می شوند.
تابع مجموع SUM
تجمیع تابع SUMمجموع کل مقادیر ستون ها را محاسبه می کند. آرگومان این تابع تجمیع همیشه باید از نوع داده عددی باشد. استفاده از تابع جمع SUM در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. SELECT SUM (Budget) "Total Budget" FROM Project.
این مثال کل بودجه را برای همه پروژه ها محاسبه می کند. نتیجه اجرای پرس و جو:
در این مثال، تابع مجموع تمام مقادیر بودجه پروژه را گروه بندی می کند و کل آنها را تعیین می کند. به همین دلیل، پرس و جو حاوی یک تابع گروه بندی ضمنی است (مانند تمام پرس و جوهای مشابه). تابع گروه بندی ضمنی از مثال بالا را می توان به صراحت مشخص کرد، همانطور که در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. SELECT SUM (Budget) "Total Budget" FROM Project GROUP BY ();
استفاده از پارامتر DISTINCT هر مقدار تکراری در ستون را قبل از اعمال تابع SUM حذف می کند. به همین ترتیب، قبل از اعمال این تابع جمع، تمام مقادیر NULL را حذف کنید.
عملکرد جمع AVG
تجمیع عملکرد AVGمیانگین حسابی تمام مقادیر یک ستون را برمی گرداند. آرگومان این تابع تجمیع همیشه باید از نوع داده عددی باشد. تمام مقادیر NULL قبل از اعمال تابع AVG از آرگومان حذف می شوند.
استفاده از تابع AVG aggregate در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. - بازگشت 133833 SELECT AVG (Budget) "Average Budget for a project" FROM Project;
اینجاست که میانگین حسابی بودجه برای همه بودجه ها محاسبه می شود.
توابع COUNT و COUNT_BIG را جمع کنید
تجمیع COUNT تابعدارای دو شکل مختلف:
COUNT (col_name) COUNT (*)
شکل اول تابع تعداد مقادیر موجود در ستون col_name را می شمارد. اگر پرس و جو از کلمه کلیدی DISTINCT استفاده کند، تمام مقادیر ستون تکراری قبل از استفاده از تابع COUNT حذف می شوند. این فرم از تابع COUNT هنگام شمارش تعداد مقادیر ستون، مقادیر NULL را در نظر نمی گیرد.
استفاده از اولین شکل تابع COUNT در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. SELECTNumber Project, COUNT (DiSTINCT Job) "Jobs in Project" FROM Works_on GROUP BY ProjectNumber.
اینجاست که تعداد موقعیت های مختلف برای هر پروژه شمارش می شود. نتیجه این پرس و جو:
همانطور که از پرس و جو در مثال مشاهده می کنید، مقادیر NULL توسط تابع COUNT در نظر گرفته نشده است. (مجموع تمام مقادیر در ستون job 7 است نه 11 آنطور که باید باشد.)
شکل دوم تابع COUNT، یعنی. تابع COUNT (*) تعداد ردیف های جدول را می شمارد. و اگر دستور SELECT پرس و جو با تابع COUNT (*) حاوی یک عبارت WHERE با یک شرط باشد، تابع تعداد ردیف هایی را که شرایط مشخص شده را برآورده می کند، برمی گرداند. برخلاف نسخه اول تابع COUNT، فرم دوم مقادیر NULL را نادیده نمی گیرد زیرا این تابع بر روی رشته ها عمل می کند نه ستون ها. مثال زیر استفاده از تابع COUNT (*) را نشان می دهد:
از SampleDb استفاده کنید. شغل را به عنوان "نوع شغل" انتخاب کنید، COUNT (*) "کارگران مورد نیاز" FROM Works_on GROUP BY Job;
اینجاست که تعداد پست ها در همه پروژه ها محاسبه می شود. نتیجه اجرای پرس و جو:
COUNT_BIG تابععملکرد مشابه COUNT. تنها تفاوت بین این دو در نوع نتیجه ای است که برمی گردانند: COUNT_BIG همیشه مقادیر BIGINT را برمی گرداند، در حالی که COUNT مقادیر INTEGER داده را برمی گرداند.
توابع جمع آماری
توابع زیر گروه توابع جمع آماری را تشکیل می دهند:
VARواریانس آماری همه مقادیر در یک ستون یا عبارت را محاسبه می کند.
VARPواریانس آماری جمعیت همه مقادیر در یک ستون یا عبارت را محاسبه می کند.
STDEVانحراف استاندارد (که به عنوان جذر واریانس مربوطه محاسبه می شود) همه مقادیر در یک ستون یا عبارت را محاسبه می کند.
STDEVPانحراف استاندارد مجموعه تمام مقادیر در یک ستون یا عبارت را محاسبه می کند.
توابع جمعی تعریف شده توسط کاربر
موتور پایگاه داده همچنین از اجرای توابع تعریف شده توسط کاربر پشتیبانی می کند. این قابلیت به کاربران اجازه می دهد تا توابع جمع آوری سیستم را با توابعی تکمیل کنند که می توانند خودشان پیاده سازی و نصب کنند. این توابع نشان دهنده یک کلاس خاص از توابع تعریف شده توسط کاربر هستند و بعداً به تفصیل مورد بحث قرار خواهند گرفت.
بند داشتن
در یک جمله داشتنشرطی را تعریف می کند که برای گروهی از ردیف ها اعمال می شود. بنابراین، این بند برای گروه‌های ردیف همان معنای عبارت WHERE برای محتویات جدول مربوطه را دارد. نحو عبارت HAVING به شرح زیر است:
شرایط داشتن
در اینجا پارامتر شرط یک شرط را نشان می دهد و حاوی توابع یا ثابت های مجموع است.
استفاده از عبارت HAVING در رابطه با تابع جمع COUNT (*) در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. - "p3" را برمی گرداند SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT (*)
در این مثال، سیستم از عبارت GROUP BY برای گروه بندی تمام سطرها بر اساس مقادیر در ستون ProjectNumber استفاده می کند. سپس تعداد ردیف‌های هر گروه شمارش می‌شود و گروه‌های حاوی کمتر از چهار ردیف (سه یا کمتر) انتخاب می‌شوند.
همانطور که در مثال زیر نشان داده شده است، عبارت HAVING را می توان بدون توابع انبوه نیز استفاده کرد:
از SampleDb استفاده کنید. - "مشاور" را برمی گرداند.
این مثال ردیف های جدول Works_on را بر اساس عنوان شغل گروه بندی می کند و کارهایی را که با حرف "K" شروع نمی شوند حذف می کند.
بند HAVING را می توان بدون بند GROUP BY نیز استفاده کرد، اگرچه این روش معمول نیست. در این حالت، تمام ردیف های جدول در یک گروه بازگردانده می شوند.
ترتیب بر اساس بند
پیشنهاد سفارش توسطترتیب مرتب سازی ردیف ها را در مجموعه نتایجی که توسط پرس و جو برگردانده شده است را تعیین می کند. این بند دارای نحو زیر است:
ترتیب مرتب سازی در پارامتر col_name مشخص شده است. Col_number یک نشانگر ترتیب مرتب سازی جایگزین است که ستون ها را به ترتیبی که در لیست انتخاب دستور SELECT ظاهر می شوند (1 ستون اول، 2 ستون دوم و غیره) شناسایی می کند. پارامتر ASCیک ترتیب صعودی را تعریف می کند و پارامتر DESC- در پایین دست پیش فرض ASC است.
نام ستون ها در بند ORDER BY لازم نیست در لیست ستون های انتخاب شده باشد. اما این در مورد جستارهایی مانند SELECT DISTINCT صدق نمی کند، زیرا در چنین پرس و جوهایی، نام ستون های مشخص شده در عبارت ORDER BY نیز باید در لیست ستون های انتخابی ظاهر شود. علاوه بر این، این بند نمی تواند شامل نام ستون هایی از جداولی باشد که در عبارت FROM مشخص نشده اند.
همانطور که از نحو عبارت ORDER BY می بینید، مرتب سازی مجموعه نتایج را می توان در چندین ستون انجام داد. این مرتب سازی در مثال زیر نشان داده شده است:
این مثال شماره بخش و نام خانوادگی و نام کارمندان را برای کارمندانی که تعداد پرسنل آنها کمتر از 20000 نفر است انتخاب می کند و همچنین بر اساس نام خانوادگی و نام مرتب شده است. نتیجه این پرس و جو:
ستون ها در بند ORDER BY را می توان نه با نام آنها، بلکه به ترتیب در لیست انتخاب مشخص کرد. بر این اساس، جمله در مثال بالا را می توان به صورت زیر بازنویسی کرد:
این روش جایگزین برای تعیین ستون ها بر اساس موقعیت آنها به جای نام، در صورتی استفاده می شود که معیار ترتیب شامل یک تابع جمع باشد. (روش دیگر استفاده از نام ستون‌ها است که سپس در عبارت ORDER BY ظاهر می‌شوند.) با این حال، در عبارت ORDER BY، توصیه می‌شود ستون‌ها را به جای اعداد، با نام آنها مشخص کنید تا به‌روزرسانی درخواست آسان‌تر شود. اگر نیاز به اضافه کردن یا حذف ستون ها در لیست انتخاب دارید. تعیین ستون ها در عبارت ORDER BY با شماره آنها در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. SELECT ProjectNumber، COUNT (*) "Number of Employees" FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
در اینجا، برای هر پروژه، تعداد پروژه و تعداد کارکنان شرکت کننده در آن انتخاب می شود و نتیجه را به ترتیب نزولی بر تعداد کارمندان مرتب می کند.
Transact-SQL مقادیر NULL را هنگام مرتب شدن به ترتیب صعودی در بالای لیست قرار می دهد و هنگامی که به ترتیب نزولی مرتب می شوند مقادیر NULL را در انتهای لیست قرار می دهد.
استفاده از ORDER BY Clause برای صفحه بندی نتایج
نمایش نتایج پرس و جو در صفحه فعلی می تواند در یک برنامه سفارشی پیاده سازی شود یا می توانید به سرور پایگاه داده دستور انجام این کار را بدهید. در حالت اول تمامی ردیف های پایگاه داده به اپلیکیشن ارسال می شوند که وظیفه آن انتخاب سطرهای مورد نیاز و نمایش آن ها می باشد. در حالت دوم، تنها ردیف های مورد نیاز برای صفحه فعلی از سمت سرور واکشی و نمایش داده می شوند. همانطور که ممکن است انتظار داشته باشید، ایجاد صفحات در سمت سرور معمولا بهترین عملکرد را ارائه می دهد زیرا فقط خطوط مورد نیاز برای نمایش برای مشتری ارسال می شود.
برای پشتیبانی از ایجاد صفحه سمت سرور، SQL Server 2012 دو عبارت SELECT جدید را معرفی می کند: OFFSET و FETCH. کاربرد این دو جمله در مثال زیر نشان داده شده است. در اینجا، از پایگاه داده AdventureWorks2012 (که می توانید در منبع پیدا کنید)، شناسه کسب و کار، عنوان شغلی و تاریخ تولد همه کارمندان زن را استخراج می کند که بر اساس عنوان شغلی به ترتیب صعودی مرتب شده اند. مجموعه ردیف های حاصل به صفحات 10 خطی تقسیم می شود و صفحه سوم نمایش داده می شود:
در یک جمله انحرافتعداد خطوط نتیجه را برای پرش در نتیجه نمایش داده شده مشخص می کند. این مقدار پس از مرتب‌سازی ردیف‌ها با عبارت ORDER BY محاسبه می‌شود. در یک جمله واکشی بعدیتعداد ردیف های WHERE و مرتب شده برای بازگشت را مشخص می کند. پارامتر این بند می تواند یک ثابت، یک عبارت یا نتیجه یک پرس و جو دیگر باشد. عبارت FETCH NEXT مشابه بند است اول واکشی.
هدف اصلی هنگام ایجاد صفحات در سمت سرور این است که بتوان فرم های صفحه رایج را با استفاده از متغیرها پیاده سازی کرد. این کار را می توان از طریق بسته SQL Server انجام داد.
SELECT Statement and IDENTITY Property
ویژگی IDENTITYبه شما امکان می دهد مقادیر یک ستون خاص از جدول را در قالب یک شمارنده افزایشی خودکار تعیین کنید. ستون های یک نوع داده عددی مانند TINYINT، SMALLINT، INT و BIGINT می توانند این ویژگی را داشته باشند. برای چنین ستون جدولی، موتور پایگاه داده به طور خودکار مقادیر متوالی را تولید می کند که با مقدار شروع مشخص شده شروع می شود. بنابراین، از ویژگی IDENTITY می توان برای تولید مقادیر عددی تک رقمی برای ستون انتخاب شده استفاده کرد.
یک جدول فقط می تواند شامل یک ستون با ویژگی IDENTITY باشد. همانطور که در مثال زیر نشان داده شده است، مالک جدول توانایی تعیین مقدار اولیه و افزایش را دارد:
از SampleDb استفاده کنید. محصول جدول ایجاد کنید (شناسه INT IDENTITY (10000، 1) NOT NULL، نام NVARCHAR (30) NOT NULL، قیمت پول) درج در محصول (نام، قیمت) VALUES ("محصول1"، 10)، ("محصول2"، 15) , ("محصول3"، 8)، ("محصول4"، 15)، ("محصول5"، 40); - 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Product5"; - مشابه عبارت قبلی SELECT $ هویت از محصول WHERE Name = "Product5";
این مثال ابتدا یک جدول Product ایجاد می کند که حاوی یک ستون ID با ویژگی IDENTITY است. مقادیر در ستون Id به طور خودکار توسط سیستم ایجاد می شود، از 10000 شروع می شود و برای هر مقدار بعدی در یک مرحله افزایش می یابد: 10000، 10،001، 10،002 و غیره.
چندین تابع و متغیر سیستم با ویژگی IDENTITY مرتبط هستند. به عنوان مثال، کد مثال استفاده می کند متغیر سیستم $ident... همانطور که از نتایج اجرای این کد می بینید، این متغیر به طور خودکار به ویژگی IDENTITY ارجاع داده می شود. همچنین می توانید به جای آن از عملکرد سیستم استفاده کنید هویت.
مقدار اولیه و افزایش ستون با ویژگی IDENTITY را می توان با استفاده از توابع پیدا کرد IDENT_SEEDو IDENT_INCRبه ترتیب. این توابع به صورت زیر اعمال می شوند:
از SampleDb استفاده کنید. SELECT IDENT_SEED ("محصول")، IDENT_INCR ("محصول")
همانطور که گفته شد، مقادیر IDENTITY به طور خودکار توسط سیستم تنظیم می شود. اما کاربر می تواند به صراحت مقادیر خود را برای رشته های خاص با اختصاص دادن به پارامتر مشخص کند. IDENTITY_INSERTمقدار ON قبل از درج مقدار صریح:
SET IDENTITY درج نام جدول ON
از آنجایی که پارامتر IDENTITY_INSERT می تواند برای تنظیم هر مقدار، از جمله مقادیر تکراری، در ستونی با ویژگی IDENTITY استفاده شود، ویژگی IDENTITY معمولاً منحصر به فرد بودن مقادیر ستون را اعمال نمی کند. بنابراین، محدودیت های UNIQUE یا PRIMARY KEY باید برای اعمال منحصر به فرد بودن مقادیر ستون اعمال شود.
هنگامی که پس از تنظیم IDENTITY_INSERT روی on، مقادیر را در جدول وارد می کنید، سیستم مقدار بعدی را در ستون IDENTITY ایجاد می کند و بالاترین مقدار فعلی را در آن ستون افزایش می دهد.
عبارت CREATE SEQUENCE
استفاده از ویژگی IDENTITY چندین اشکال مهم دارد که مهمترین آنها عبارتند از:
دارایی به جدول مشخص شده محدود می شود.
مقدار جدید ستون را به هیچ وجه نمی توان به دست آورد، مگر با اعمال آن.
ویژگی IDENTITY فقط هنگام ایجاد یک ستون قابل تعیین است.
به این دلایل، SQL Server 2012 دنباله هایی را معرفی می کند که دارای معنایی مشابه با ویژگی IDENTITY هستند، اما بدون معایب ذکر شده قبلی. در این زمینه، یک دنباله به عملکرد یک پایگاه داده اشاره دارد که به شما امکان می دهد مقادیر شمارنده را برای اشیاء مختلف پایگاه داده، مانند ستون ها و متغیرها، مشخص کنید.
توالی ها با استفاده از دستورالعمل ایجاد می شوند ایجاد دنباله... دستور CREATE SEQUENCE در استاندارد SQL تعریف شده است و توسط سایر سیستم های پایگاه داده رابطه ای مانند IBM DB2 و Oracle پشتیبانی می شود.
مثال زیر نحوه ایجاد یک دنباله در SQL Server را نشان می دهد:
از SampleDb استفاده کنید. ایجاد توالی dbo.Sequence1 به عنوان INT START با 1 افزایش در 5 MINVALUE 1 MAXVALUE 256 CYCLE.
در مثال بالا، مقادیر Sequence1 به‌طور خودکار توسط سیستم تولید می‌شوند و از مقدار 1 شروع می‌شوند و برای هر مقدار متوالی با افزایش 5. بنابراین، در بند شروعمقدار اولیه نشان داده شده است، و در پیشنهاد افزایشی- گام. (مرحله می تواند مثبت یا منفی باشد.)
در دو جمله اختیاری بعدی MINVALUEو MAXVALUEحداقل و حداکثر مقدار شی sequence را مشخص می کند. (توجه داشته باشید که MINVALUE باید کمتر یا مساوی با مقدار اولیه باشد و MAXVALUE نمی تواند بزرگتر از حد بالای نوع داده مشخص شده برای دنباله باشد.) در یک جمله چرخهنشان می دهد که وقتی از حداکثر (یا حداقل برای دنباله ای با گام منفی) مقدار فراتر رفت، دنباله از ابتدا تکرار می شود. به‌طور پیش‌فرض، این بند روی NO CYCLE تنظیم می‌شود، به این معنی که تجاوز از حداکثر یا حداقل مقدار توالی یک استثنا را ایجاد می‌کند.
ویژگی اصلی توالی ها مستقل بودن آنها از جداول است. آنها را می توان با هر شی پایگاه داده مانند ستون های جدول یا متغیرها استفاده کرد. (این ویژگی تأثیر مثبتی بر ذخیره سازی و در نتیجه بر عملکرد دارد. یک دنباله خاص نیازی به ذخیره سازی ندارد، فقط آخرین مقدار آن ذخیره می شود.)
مقادیر جدید توالی با ایجاد می شود مقدار بعدی برای عباراتکه کاربرد آن در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. - 1 را برمی گرداند SELECT NEXT VALUE FOR dbo.sequence1. - 6 را برمی گرداند (مرحله بعدی) SELECT NEXT VALUE FOR dbo.sequence1.
می‌توانید از عبارت NEXT VALUE FOR برای اختصاص دادن نتیجه یک دنباله به سلول یا سلول ستونی استفاده کنید. مثال زیر استفاده از این عبارت برای تخصیص نتایج به یک ستون را نشان می دهد:
از SampleDb استفاده کنید. ایجاد جدول محصول (ID INT NOT NULL، نام NVARCHAR (30) NOT NULL، قیمت پول) درج در مقادیر محصول (ارزش بعدی برای dbo.sequence1، "Product1"، 10); درج در مقادیر محصول (مقدار بعدی برای dbo.sequence1، "Product2"، 15)؛ -...
مثال بالا ابتدا جدولی به نام Product با چهار ستون ایجاد می کند. در مرحله بعد، دو عبارت INSERT دو ردیف را در این جدول قرار می دهند. دو خانه اول ستون اول 11 و 16 خواهد بود.
مثال زیر استفاده از نمای کاتالوگ را نشان می دهد sys.sequencesبرای مشاهده مقدار فعلی یک دنباله بدون استفاده از آن:
به طور معمول، عبارت NEXT VALUE FOR در یک عبارت INSERT برای دستور دادن به سیستم برای درج مقادیر تولید شده استفاده می شود. این عبارت همچنین می تواند به عنوان بخشی از یک پرس و جو چند خطی با استفاده از عبارت OVER استفاده شود.
برای تغییر ویژگی یک دنباله موجود، اعمال کنید عبارت ALTER SEQUENCE... یکی از مهمترین کاربردهای این عبارت، گزینه RESTART WITH است که توالی مشخص شده را ریست می کند. مثال زیر استفاده از دستور ALTER SEQUENCE را برای بازنشانی تقریباً تمام خصوصیات Sequence1 نشان می دهد:
از SampleDb استفاده کنید. ALTER SEQUENCE dbo.sequence1 شروع مجدد با 100 افزایش در 50 MINVALUE 50 MAXVALUE 200 NO CYCLE.
دنباله با استفاده از دستورالعمل حذف می شود دنباله رها کردن.
عملگرها را تنظیم کنید
علاوه بر عملگرهایی که قبلاً مورد بحث قرار گرفت، Transact-SQL از سه عملگر مجموعه دیگر پشتیبانی می کند: UNION، INTERSECT و EXCEPT.
اپراتور اتحادیه
اپراتور اتحادیهنتایج دو یا چند پرس و جو را در یک مجموعه نتیجه واحد ترکیب می کند که شامل تمام ردیف های متعلق به همه پرس و جوها در اتحادیه است. در نتیجه، نتیجه پیوستن دو جدول، یک جدول جدید است که شامل تمام ردیف‌های یک یا هر دو جدول اصلی است.
شکل کلی عملگر UNION به شکل زیر است:
select_1 UNION select_2 (select_3]) ...
گزینه های select_1، select_2، ... عبارت های SELECT هستند که join را ایجاد می کنند. اگر از پارامتر ALL استفاده شود، تمام ردیف ها از جمله موارد تکراری نمایش داده می شوند. در عبارت UNION، پارامتر ALL همان معنی را دارد که در SELECT، با یک تفاوت: برای SELECT، این پارامتر پیش فرض است و برای UNION، باید به طور صریح مشخص شود.
در شکل اصلی خود، SampleDb برای نشان دادن استفاده از عملگر UNION مناسب نیست. بنابراین، این بخش یک جدول جدید EmployeeEnh ایجاد می کند که مشابه جدول Employee موجود است اما دارای یک ستون City اضافی است. این ستون محل سکونت کارکنان را نشان می دهد.
ایجاد جدول EmployeeEnh این فرصت را به ما می دهد تا استفاده از بند را نشان دهیم. بهدر عبارت SELECT دستور SELECT INTO دو عملیات را انجام می دهد. ابتدا یک جدول جدید با ستون های لیست شده در لیست SELECT ایجاد می شود. سپس سطرهای جدول اصلی به جدول جدید وارد می شوند. نام جدول جدید در عبارت INTO و نام جدول منبع در عبارت FROM مشخص شده است.
مثال زیر ایجاد جدول EmployeeEnh را از جدول Employee نشان می دهد:
از SampleDb استفاده کنید. SELECT * INTO EmployeeEnh FROM Employee. ALTER TABLE EmployeeEnh ADD City NCHAR (40) NULL;
در این مثال، دستور SELECT INTO جدول EmployeeEnh را ایجاد می کند، تمام ردیف های جدول منبع Employee را در آن قرار می دهد و سپس دستور ALTER TABLE ستون City را به جدول جدید اضافه می کند. اما ستون شهر اضافه شده حاوی هیچ مقداری نیست. مقادیر را می توان از طریق Management Studio یا با استفاده از کد زیر در این ستون درج کرد:
از SampleDb استفاده کنید. به روز رسانی EmployeeEnh SET City = "Kazan" WHERE ID = 2581; به روز رسانی EmployeeEnh SET City = "Moscow" WHERE ID = 9031; به روز رسانی EmployeeEnh SET City = "Yekaterinburg" WHERE ID = 10102; به روز رسانی EmployeeEnh SET City = "St. Petersburg" WHERE Id = 18316; به روز رسانی EmployeeEnh SET City = "Krasnodar" WHERE ID = 25348; به روز رسانی EmployeeEnh SET City = "Kazan" WHERE ID = 28559; به روز رسانی EmployeeEnh SET City = "Perm" WHERE ID = 29346;
ما اکنون آماده نشان دادن استفاده از بیانیه UNION هستیم. مثال زیر یک پرس و جو برای ایجاد پیوند بین جداول EmployeeEnh و Department با استفاده از این عبارت را نشان می دهد:
از SampleDb استفاده کنید. SELECT City AS "City" FROM EmployeeEnh UNION SELECT Location FROM Department;
نتیجه این پرس و جو:
فقط جداول سازگار را می توان با استفاده از عبارت UNION ملحق کرد. منظور ما از جداول سازگار این است که هر دو لیست ستون های انتخابی باید دارای تعداد یکسانی از ستون ها باشند و ستون های مربوطه باید دارای انواع داده های سازگار باشند. (از نظر سازگاری، انواع داده های INT و SMALLINT سازگار نیستند.)
نتیجه الحاق را فقط می توان با استفاده از عبارت ORDER BY در آخرین دستور SELECT، همانطور که در مثال زیر نشان داده شده است، مرتب کرد. بند های GROUP BY و HAVING را می توان با دستورات SELECT جداگانه استفاده کرد، اما نه در خود join.
پرس و جو در این مثال کارمندانی را انتخاب می کند که یا در بخش d1 کار می کنند یا قبل از 1 ژانویه 2008 روی پروژه ای کار می کنند.
اپراتور UNION از گزینه ALL پشتیبانی می کند. این گزینه موارد تکراری را از مجموعه نتایج حذف نمی کند. اگر تمام عبارات SELECT که توسط یک یا چند عملگر UNION به یک جدول ملحق شده اند، می تواند به جای عملگر UNION استفاده شود. در این حالت، مجموعه دستورات SELECT با یک دستور SELECT با مجموعه ای از دستورات OR جایگزین می شود.
اپراتورهای INTERSECT و EXCEPT
دو عملگر دیگر برای کار با مجموعه ها، تقاطعو جز، به ترتیب تقاطع و تفاوت را تعریف کنید. در زیر تقاطع، در این زمینه، مجموعه ای از ردیف ها وجود دارد که متعلق به هر دو جدول است. و تفاوت بین دو جدول به صورت تمام مقادیری است که به جدول اول تعلق دارند و در جدول دوم وجود ندارند. مثال زیر استفاده از عملگر INTERSECT را نشان می دهد:
Transact-SQL از استفاده از پارامتر ALL با عملگرهای INTERSECT یا EXCEPT پشتیبانی نمی کند. استفاده از عملگر EXCEPT در مثال زیر نشان داده شده است:
به یاد داشته باشید که این سه عملگر مجموعه دارای اولویت اجرای متفاوتی هستند: INTERSECT بالاترین اولویت را دارد و پس از آن EXCEPT و UNION کمترین اولویت را دارد. عدم توجه به اولویت اجرا هنگام استفاده از چندین عملگر مجموعه مختلف می تواند منجر به نتایج غیرمنتظره شود.
عبارات موردی
در زمینه برنامه نویسی کاربردی پایگاه داده، گاهی اوقات لازم است که ارائه داده ها را اصلاح کنید. به عنوان مثال، افراد را می توان بر اساس طبقه اجتماعی خود با استفاده از مقادیر 1، 2، و 3 که به ترتیب نشان دهنده مردان، زنان و کودکان هستند، تقسیم بندی کرد. این تکنیک برنامه نویسی می تواند زمان لازم برای اجرای برنامه را کاهش دهد. عبارت CASE Transact-SQL به شما اجازه می دهد تا به راحتی این نوع رمزگذاری را پیاده سازی کنید.
برخلاف اکثر زبان های برنامه نویسی، CASE یک عبارت نیست، بلکه یک عبارت است. بنابراین، یک عبارت CASE را می توان تقریباً در هر جایی که Transact-SQL اجازه استفاده از عبارات را می دهد استفاده می شود. عبارت CASE دو شکل دارد:
عبارت CASE ساده؛
عبارت جستجو CASE.
سینتکس یک عبارت CASE ساده به صورت زیر است:
یک عبارت با یک عبارت CASE ساده ابتدا لیست تمام عبارات موجود را جستجو می کند بند WHENاولین عبارتی که با express_1 مطابقت دارد و سپس عبارت مربوطه را اجرا می کند سپس بند... اگر هیچ عبارت منطبقی در لیست WHEN وجود ندارد، پس بند ELSE.
نحو برای عبارت جستجوی CASE به شرح زیر است:
در این حالت اولین شرط واجد شرایط را جستجو می کند و سپس عبارت THEN مربوطه را اجرا می کند. اگر هیچ یک از شرایط شرایط را برآورده نکرد، بند ELSE اجرا می شود. استفاده از عبارت جستجوی CASE در مثال زیر نشان داده شده است:
از SampleDb استفاده کنید. SELECT ProjectName, CASE WHEN Budget> 0 AND Budget 100000 AND Budget 150000 AND Budget
نتیجه این پرس و جو:
این مثال بودجه همه پروژه ها را وزن می کند و وزن های محاسبه شده را همراه با نام پروژه های مربوطه نمایش می دهد.
مثال زیر روش دیگری را برای استفاده از عبارت CASE نشان می دهد که در آن عبارت WHEN شامل سوالات فرعی است که بخشی از عبارت هستند:
از SampleDb استفاده کنید. SELECT ProjectName, CASE WHEN p1.Budget (انتخاب AVG (p2.Budget) FROM Project p2) سپس "بالاتر از میانگین" END "رده بودجه" FROM Project p1;
نتیجه این پرس و جو به شرح زیر است:
برای خلاصه کردن اطلاعات موجود در پایگاه داده، SQL توابع جمعی را ارائه می دهد. یک تابع تجمیع یک ستون کامل از داده ها را به عنوان آرگومان می گیرد و یک مقدار واحد را برمی گرداند که آن ستون را به روشی خاص خلاصه می کند.
برای مثال، تابع مجموع AVG () ستونی از اعداد را به عنوان آرگومان می گیرد و میانگین آنها را محاسبه می کند.
برای محاسبه متوسط درآمد سرانه یک ساکن Zelenograd، به پرس و جو زیر نیاز دارید:
«درآمد متوسط =»، AVG (SUMD) را انتخاب کنید
SQL دارای شش تابع مجموع است که انواع مختلفی از خلاصه ها را ارائه می دهد (شکل 1):
- SUM () مجموع تمام مقادیر موجود در ستون را محاسبه می کند.
- AVG () میانگین را در بین مقادیر موجود در ستون محاسبه می کند.
- MIN () کوچکترین را در بین تمام مقادیر موجود در ستون پیدا می کند.
- MAX () بزرگترین را در بین تمام مقادیر موجود در ستون پیدا می کند.
- COUNT () تعداد مقادیر موجود در ستون را می شمارد.
- COUNT (*) تعداد ردیف های جدول نتیجه پرس و جو را می شمارد.
آرگومان یک تابع مجموع می تواند یک نام ستون ساده، مانند مثال قبلی، یا یک عبارت باشد، مانند عبارت زیر که مالیات سرانه را محاسبه می کند:
SELECT AVG (SUMD * 0.13)
این کوئری یک ستون موقت حاوی مقادیر (SUMD * 0.13) برای هر ردیف در جدول PERSON ایجاد می کند و سپس میانگین ستون موقت را محاسبه می کند.
مجموع درآمد تمام ساکنان Zelenograd را می توان با استفاده از تابع جمع SUM محاسبه کرد:
جمع (SUMD) را از شخص انتخاب کنید
تابع تجمیع همچنین می تواند برای محاسبه مجموع یک جدول از نتایج به دست آمده از پیوستن چندین جدول منبع استفاده شود. به عنوان مثال، می توانید کل درآمدی را که ساکنان از منبعی به نام بورسیه دریافت می کنند محاسبه کنید:
انتخاب جمع (پول)
از سود، HAVE_D
WHERE PROFIT.ID = HAVE_D.ID
AND PROFIT.SOURCE = 'بورسیه'
توابع مجموع MIN () و MAX () به شما امکان می دهند به ترتیب کوچکترین و بزرگترین مقادیر را در جدول پیدا کنید. با این حال، ستون می تواند حاوی مقادیر عددی یا رشته ای، یا مقادیر تاریخ یا زمان باشد.
به عنوان مثال، می توانید تعریف کنید:
الف) کمترین درآمد کل ساکنین و بالاترین مالیات قابل پرداخت:
SELECT MIN (SUMD)، MAX (SUMD * 0.13)
ب) تاریخ تولد مسن ترین و جوان ترین ساکن:
SELECT MIN (RDATE)، MAX (RDATE)
ج) نام‌های خانوادگی، نام‌ها و نام‌های پدر اولین و آخرین ساکنان فهرست، به ترتیب حروف الفبا:
SELECT MIN (FIO)، MAX (FIO)
هنگام اعمال این توابع انبوه، باید به یاد داشته باشید که داده های عددی بر اساس قوانین حسابی مقایسه می شوند، تاریخ ها به ترتیب مقایسه می شوند (مقادیر تاریخ اولیه کمتر از موارد بعدی در نظر گرفته می شوند)، فواصل زمانی بر اساس مدت زمان آنها مقایسه می شوند.
هنگام استفاده از MIN () و MAX () با داده های رشته، نتیجه مقایسه دو رشته به جدول رمزگذاری کاراکتر مورد استفاده بستگی دارد.
تابع مجموع COUNT () تعداد مقادیر را در یک ستون از هر نوع می شمارد:
(الف) چند آپارتمان در منطقه کوچک 1 وجود دارد؟
SELECT COUNT (ADR) FROM FLAT WHERE ADR LIKE "%, 1_ _-%"
ب) چند نفر از ساکنان منابع درآمد دارند؟
تعداد (نام متمایز) از HAVE_D را انتخاب کنید
ج) چه تعداد منبع درآمد توسط ساکنان استفاده می شود؟
SELECT COUNT (DISTINCT ID) FROM HAVE_D (کلید کلیدی DISTINCT مشخص می کند که مقادیر غیر تکراری در ستون شمارش می شوند).
تابع مجموع ویژه COUNT (*) ردیف های جدول نتیجه را می شمارد نه مقادیر داده ها:
(الف) چند آپارتمان در منطقه کوچک 2 وجود دارد؟
انتخاب تعداد (*) FROM FLAT WHERE ADR LIKE "%, 2 __-%"
ب) ایوان ایوانوویچ چند منبع درآمد دارد؟
SELECT COUNT (*) FROM PERSON, HAVE_D WHERE FIO = "Ivanov Ivan Ivanovich" و PERSON.NOM = HAVE_D.NOM
(ج) چند نفر ساکن در یک آپارتمان در یک آدرس معین زندگی می کنند؟
SELECT COUNT (*) FROM PERSON WHERE ADR = "Zelenograd, 1001-45"
یکی از راه‌های درک چگونگی اجرای پرس‌و‌جوهای انبوه، این است که اجرای کوئری را در دو بخش در نظر بگیرید. ابتدا مشخص می‌شود که پرس‌وجو چگونه بدون توابع انبوه کار می‌کند و چندین ردیف از نتایج را برمی‌گرداند. سپس توابع انبوه به نتایج پرس و جو اعمال می شوند و یک ردیف خلاصه منفرد را برمی گرداند.
به عنوان مثال، پرس و جو پیچیده زیر را در نظر بگیرید: میانگین درآمد سرانه کل، مجموع درآمد کل ساکنان، و میانگین درآمد منبع را به عنوان درصدی از کل درآمد ساکنان بیابید. پاسخ توسط اپراتور داده می شود
SELECT AVG (SUMD)، SUM (SUMD)، (100 * AVG (money / SUMD)) FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM و HAVE_D.ID = PROFIT.ID
بدون توابع انبوه، پرس و جو به صورت زیر خواهد بود:
SUMD, SUMD, MONEY / SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM و HAVE_D.ID = PROFIT.ID را انتخاب کنید
و یک ردیف از نتایج را برای هر مقیم و منبع درآمد خاص برمی گرداند. توابع انبوه از ستون های جدول نتیجه این پرس و جو برای تولید یک جدول تک ردیفی با نتایج خلاصه استفاده می کنند.
در ردیف ستون های برگشتی، می توانید به جای نام هر ستون، یک تابع تجمیع را مشخص کنید. به عنوان مثال، می توان آن را در عبارتی گنجاند که مقادیر دو تابع مجموع را اضافه یا کم می کند:
حداکثر (SUMD) -MIN (SUMD) را از شخص انتخاب کنید
با این حال، یک تابع تجمعی نمی تواند آرگومان دیگری برای تابع تجمیعی باشد، یعنی. توابع جمع تو در تو ممنوع هستند.
علاوه بر این، توابع انبوه و نام ستون های معمولی را نمی توان همزمان در لیست ستون های برگشتی استفاده کرد، زیرا هیچ نکته ای در این مورد وجود ندارد، به عنوان مثال:
FIO، SUM (SUMD) را از شخص انتخاب کنید
در اینجا، اولین عنصر لیست به DBMS دستور می دهد تا جدولی ایجاد کند که از چندین ردیف تشکیل شده و برای هر ساکن یک ردیف داشته باشد. مورد دوم در لیست از DBMS می خواهد که یک مقدار نتیجه را دریافت کند که مجموع مقادیر در ستون SUMD است. این دو دستورالعمل با یکدیگر در تضاد هستند که منجر به خطا می شود.
موارد فوق در مورد پردازش سوالات فرعی و پرس و جو با گروه بندی اعمال نمی شود.
