در این نوشته می خواهیم درباره به کارگیری تابع های تجمیعی در زیر کوئری ها (یا کوئری های تو در تو Nested Query) گفتگو کنیم. تابع های تجمیعی تنها یک سطر را برگشت می دهند، پس از این رو می توانیم آنها را برای نوشتن پرس و جو های تو در تو تک سطری (Single Row Subquery) به کار ببریم.

زیر کوئری ها در – بخش یکم

توابع تجمیعی در SQL

جمع بندی مطلب توابع تجمیعی در SQL

به کار بردن تابع های تجمیعی درون کوئری درونی، کاربردهای زیادی دارد و می توانیم با آنها داده های متفاوتی را بدست آوریم. می توانیم هر کدام از تابع های تجمیعی COUNT و MIN و MAX و AVG و SUM را به کار ببریم. در ادامه چندین نمونه کاربرد تنها نوشته ایم و در دنباله اسکریپت SQL هر کدام را آموزش داده ایم

  • کدام کارمندان دستمزدش برابر با بیشترین دستمزدها است.
  • چه تعداد از کارمندان بیشترین دستمزدها را می گیرند.
  • کدام مشتری ها بانک بیشترین بدهی بانکی را دارد.
  • کدام مشتری ها بیشترین خریدها را کرده اند.

مثال – فهرست تمامی کارمندهایی را پیدا کنید که بیشترین (یا کمترین) دستمزد را دریافت می کنند.

مثال – مقدار کمترین دستمزد را نشان دهید.

مثال – تعداد کارمندانی که بیشترین دستمزد دریافت می کنند را نشان دهید. درباره این پرس و جو و پرس جوی نخست در این نوشته، عملگر برابری (=) را به کار بردیم تا بتوانیم بفهمیم کدام کارمندان کمترین یا بیشترین دستمزد را دریافت می کنند. همچنین در این پرس و جو، درون پرس و جوی اصلی، تابع ()COUNT را به کار بردیم، تا تعداد سطرهای برگشتی را بدست آوریم، که برابر است با تعداد کارمندانی که بیشترین دستمزد را دریافت می کنند. همچنین در کوئری های درونی به ترتیب از تابع های ()MIN و ()MAX کمک گرفته شده تا کمترین و بیشترین دستمزد پیدا شود و به عنون مقدار شرط پرس و جوی اصلی برگشت داده شود.

مثال – فرض کنید جدولی دارید به نام room که دارای یک ستون به نام capacity است. فهرست نام و بیشترین ظرفیت اتاق هایی را بدست آورید که نام ساختمان اتاق ها با CAS یا Cas آغاز شود. پرس و جوی زیر یک ایراد در نوشتن اسکریپت های SQL را نشان می دهد و آنکه نمی توانیم در کنار تابع های تجمیعی، نام ستون دیگری را بیاوریم.

برای نوشتن پرس و جوی درست برای این مثال، باید یک زیر پرس و جو را برای بدست آوردن بیشترین ظرفیت به کار بیریم که مقدار برگشتی آن در شرط عبارت WHERE به کار گرفته می شود. در کوئری زیر می بینید که در شرط پیش روی عبارت AND بشترین ظرفیت بدست می آید.

مثال – فرض کنید جدولی دارید به نام room که دارای یک ستون به نام capacity است. فهرست نام و بیشترین ظرفیت اتاق هایی را بدست آورید که نام ساختمان اتاق ها با CAS آغاز شود. پرس و جوی زیر نشان می دهد که زیر کوئری (پرس وجوی درونی) می توانند شامل عبارت های شرطی و عبارت باقاعده LIKE باشد.

بنابراین در کد بالا زیر کوئری با توجه به شرط پیش روی WHERE یک شماره برگشت می دهد که این شماره برابر با بیشترین مقدار ظرفیت است. سپس این شماره به شرط = AND capacity انستاب داده می شود و سپس با این عدد و با شرط عبارت با قاعده، خروجی مناسب همراه با نام نیز برگشت داده می شود. 

زیر پرس و جو های چند سطری

گفتیم که گونه دیگر زیر پرس و جوهایی هستند که چندین سطر را برگشت می دهند. در این گونه، باید یکی از عبارت های IN یا ANY یا ALL را به کار ببریم. اسکریپت زیر دستمزد کارمندانی را برگشت می دهد که دستمزد آنها کمترین دستمزد در دپارتمان ها است. عملگر IN برابر بودن یک مقدار ستون در یک لیست را بررسی می کند.

در کوئری بالا زیر پرس و جو چندین سطر را برگشت می دخد، پس باید یکی از عملگرهای IN یا ANY یا ALL را به کار ببریم. کد زیر از عملگر ANY کمک می گیرد، زیرا می خواهیم هر مقدار ستون با فهرست برگشت داده شده مقایسه شود. پرس و جوی زیر فهرست همه کارمندانی را برگشت می دهد که کار آنها برنامه نویس IT نباشد و یا اینکه دستمزد آنها از هر برنامه نویسی کمتر باشد.

در پرس و جوی بالا، اینکه کارمندا برگشتی IT_PROG نباشد با شرط پیش روی AND پایان اسکریپت انجام می شود. برای آنکه کارمندانی که حقوق آن کمتر از هر برنامه نویس باشد، زیر کوئری را نوشته ایم. زیر پرس و جو× فهرست دستمزد همه برنامه نویس ها را برگشت می دهد و شرط salary < ANY بررسی می کند که آیا ستونِ سطر برگشتی از پرس و جوی اصلی، کمتر از هر یک از دستمزدهای برگشتی زیر کوئری است. اگر بود و (AND) اگر کارمند IT_PROG نبود، پس شرط درست است، و سطر جز خروجی خواهد بود.

مثال – فهرست دانش آموزهایی را پیدا کنید که در درس CS105 ثبت نام نکرده اند.

مثال – فرض کنید دو جدول کالاها و سفارش ها را داریم. می خواهیم فهرست کالاهایی را بدست آوریم که تعداد سفارش آنها بیش از ۱۰۰ تا باشد. این پرس و جو نیز به گونه چند سطری است، پس برای کاربرد این مثال از عملگر IN کمک گرفته ایم. در جدول های فرضی ما، ستون product_id ستون کلید اصلی جدول products و ستون product_id در جدول orders، کلید بیرونی (خارجی) به جدول products است، پس رابطه یک به چند میان آنها است، به گونه ای که برای هر کالا جندین سفارش و هر شفارش متعلق به یک کالا است. در زیر پرس و جو درونی، فهرست تمامی شناسه کالا ها را از جدول orders بر می گرداند که تعداد سفارش ها بیش از ۱۰۰ باشد و سپس در شرط کوئری اصلی آنها محصول هایی نمایش داده می شوند که شناسه آنها درون فهرست برگشتی زیر کوئری باشد.

مثال – فرض کنید دو جدول مشتری ها و سفارش ها را داریم. می خواهیم فهرست از مشتری ها به همراه مجموع سفارش آنها را داشته باشیم. 

مثال – فرض کنید دو جدول مشتری ها و سفارش ها را داریم. می خواهیم فهرست از شفارش ها را بدست آوریم که مشتری آنها از شهر تهران باشند. در کد زیر، درون پرس و جوی درونی، فهرست شناسه مشتری هایی را پیدا می کند که شهر آنها تهران است و سپس باز هم با عملگر IN بررسی می کنیم که شناسه های مشتری (customer_id) برگشتی از کوئری اصلی درون فهرست برگشتی زیر کوئری باشد. سپس سطرهای برگشتی به گونه نزولی مرتب می شوند.