استفاده از عبارت Case در SQL Server

مقدمه

یک عبارت case نتایح کوئری در SQL را براساس مقادیر و شرایط خاص مطابق با منطق تعریف شده، ارزیابی و برمی‌گرداند. یک جدول از رأی دهنده‌گان با فیلدهایی به نام شناسه رأی دهنده‌گان ، نام و تاریخ تولید date of birth day در نظر بگیرید.

اگر به دنبال منطق در مورد واجد شرایط بودن رأی دادن هستید، این به مقادیر موجود در ستون DOB یا همان date of birth day بستگی دارد.

اگر سن رأی دهنده بزرگتر از ۱۸ باشد، این افراد واجد شرایط برای رأی دادن هستند.

بیایید به مثال دیگری نگاه کنیم. بسیاری از اوقات، ما مقادیر ستون را در بیت‌های ۱ یا ۰ ذخیره می‌کنیم. فرض کنید مقادیر موجود بودن یک محصول را به صورت ۱ یا ۰ ذخیره می‌کنید. به عنوان مثال:

۱ = به معنی دسترس بودن محصول است.

۰ = به این معنی هست که موجودی محصول تمام شده است.

اگر به چشم‌انداز پایگاه داده نگاه کنیم، استفاده از اختصارات یا بیت‌ها تا جایی که ممکن است، تمرین خوبی است. برای بهینه‌ساز کوئری در تهیه plan  در کوئری مفید است. اما از منظر برنامه، کاربر نهایی به این مقادیر نیاز ندارد. مشتریان فقط باید ببینند که آیا محصول موجود است یا خیر؟

در تصویر زیر هم پایگاه داده و هم view نرم افزار را می‌بینیم.

در شکل زیر جدول SQL  را مشاهده می‌کنیم.

همچنین در شکل فوق view از نرم‌افزار را مشاهده می‌کنیم.

سؤال این هست که عبارت case  در کوئری نویسی در SQL Server چطور عمل می‌کند؟

یک دستور CASE در SQL Server یک عبارت را ارزیابی می‌کند و یک مقدار را براساس شرایط تعریف شده برمی‌گرداند. بنابراین، در مثال قبلی، عبارات CASE مطابق شکل زیر عمل می‌کنند.

در سطح بالا، syntax  دستور case در شکل زیر نشان داده شده است. در اینجا، شرایط متعددی را مشخص کردیم. SQL Server شرایط را به صورت متوالی ارزیابی می‌کند. هنگامی که یک شرط با موفقیت ارزیابی شد، ارزیابی شرایط باقی مانده را متوقف می‌کند. اگر هیچ یک از شرایط برآورده نشد، می‌توانیم از عبارت اختیاری ELSE برای برگرداندن مقدار پیش‌فرض استفاده کنیم. برای مثال، اگر مقداری متفاوت از ۰ و ۱ در ستون در دسترس بودن یا availability داشته باشیم، خروجی را از بلوک کد ELSE دریافت می‌کنید. حداقل به یک مجموعه از بلوک‌های WHEN و THEN نیاز دارد. دستور CASE باید با بلوک END خاتمه یابد.

حال در ادامه دستور case  با استفاده از مثال‌های مختلف بررسی می‌کنیم.

توجه: در این مقاله از پایگاه داده نمونه مایکروسافت Adventure Works استفاده می‌کنیم. می‌توانید نسخه backup آن را از Microsoft Docs دانلود کنید.

مثال از دستور Select به همراه عبارت Case

در این نوع دستور CASE از عبارات بررسی برابری استفاده می‌کنیم. کوئری زیر یک عبارت CASE ساده را پیاده‌سازی می‌کند.

در ادامه با دستور use ابتدا دیتابیس adventure works 2019 را فراخوانی می‌کنیم. ابتدا با استفاده از دستور select از دیتابیس adventure works 2019 از اسکیمای human resources جدول employ فیلد national id number یا همان کد ملی را استخراج کرده برای این منظور کوئری زیر را می‌نویسیم:

select 
NationalIDNumber
from HumanResources.Employee

خروجی کوئری فوق مطابق تصویر زیر است:

حال در ادامه با استفاده از دستور top 5 رکورد ابتدایی جدول employee از اسکیمای human resources استخراج کرده. برای این منظور کوئری زیر را می‌نویسیم:

select
 top 5 NationalIDNumber
from HumanResources.Employee

خروجی کوئری فوق مطابق تصویر زیر است:

حال در ادامه در جدول employee فیلدی یا ستونی تحت عنوان saler id falg وجود دارد که دارای مقادیر صفر و یک است. اگر فیلد saler id flag برای کد ملی برابر یک باشد، یعنی آن کارمند فعال شده است و اگر مقدار فیلد saler id flag برای کد ملی برابر با صفر باشد، یعنی آن کارمند غیرفعال است. برای این منظور از دستور case و کلمات کلیدی when, then, else استفاده می‌کنیم. برای این منظور کوئری زیر را می‌نویسیم:

• اگر مقدار فیلد [SalariedFlag] برابر با یک باشد، کارمند فعال را نشان می‌دهد.
• برای همه مقادیر دیگر، خروجی را به عنوان کارمند غیرفعال نمایش می‌دهد.
• فراخوانی ۵ سطر ابتدایی جدول کارکنان و تشخیص کارمندان فعال و غیرفعال

select top 5 NationalIDNumber,
case  SalariedFlag
when 1 then 'active employee'
else 'inactive employee'
end as [SalariedFlag]
from HumanResources.Employee

خروجی کوئری فوق مطابق تصویر زیر است:

همچنین ما می‌توانیم چندین شرط در عبارات case استفاده کنیم:

یعنی در ستون saler if flag  مقادیر یک به عنوان کارمند فعال و مقادیر صفر به عنوان کارمند غیرفعال و در غیراینصورت، مقادیر نامعتبر نشان داده شود.

برای این منظور کوئری زیر را می‌نویسیم:

select top 5 NationalIDNumber,
case  SalariedFlag
when 1 then 'active employee'
when 0 then 'inactive employee'
else 'invalid value'
end as [SalariedFlag]
from HumanResources.Employee

استانداردسازی داده‌ها با استفاده از عبارات Case

معمولاً برای ذخیره مقادیر در جداول SQL از اختصارات استفاده می‌کنیم. اختصارات استاندارد عبارتند از جنسیت، کد کشور، وضعیت ازدواج، نام محصولات و…

فرض کنید اختصارات را برای ذخیره جنسیت کارمندان مشخص می‌کنیم. اکنون برنامه ما باید نتایج را بدون هیچگونه اختصاری نمایش دهد.

عبارات SQL CASE به استاندارد کردن خروجی برای معیارهای تعریف شده کمک می‌کند. در کوئری زیر از شرایط زیر استفاده می‌کنیم:

• اگر مقدار جنسیت M است، آن را به صورت مذکر نمایش دهید.
• اگر مقدار جنسیت F است، آن را به صورت  مؤنث نمایش دهید.
• برای هر مقدار دیگر، مقدار نامعتبر را نمایش دهید.
• برای این منظور بااستفاده از دستور use  ابتدا دیتابیس ۲۰۱۹ adventure works را فراخوانی می‌کنیم.

فراخوانی دیتابیس:

use AdventureWorks2019
go

ابتدا با استفاده از دستور Select کلیه رکوردهای جدول Employee از اسکیمای human resources را فراخوانی می‌کنیم. برای این منظور کوئری زیر را می‌نویسیم:

select * from HumanResources.Employee

خروجی کوئری فوق مطابق تصویر زیر است:

حال در ادامه می‌خواهیم کوئری بنویسیم که مقادیر فیلد Gender از جدول employee از اسکیمای Human Resources استخراج کرده؛ به شرطی که رکوردهایی که برابر m هستند، برابر male نمایش دهد و رکوردهایی که برابر F  هستند را برابر با female نمایش دهد. برای این منظور کوئری زیر را می‌نویسیم:

SELECT DISTINCT CASE gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'Invalid Value'
END AS Gender
FROM HumanResources.Employee

خروجی کوئری فوق مطابق تصویر زیر است:

 مثال از کاربرد Case و دستورات شرطی And

در ادامه ابتدا با دستور select کلیه رکوردهای جدول Product از اسکیمای Production استخراج کرده. برای این منظور کوئری زیر را می‌نویسیم:

select * 
from Production.Product

خروجی کوئری فوق مطابق تصویر زیر است:

در عبارت CASE جستجو شده، به جای مقادیر مستقیم، عبارت CASE را مشخص می‌کنیم. هنگامی که مقدار عبارت شرطی را در عبارت WHEN ارزیابی و برآورده کرد، مقدار متناظر آن برگردانده می‌شود.

به SQL زیر نگاه کنید. در اینجا، عباراتی را در عبارت WHEN برای [ListPrice] تعریف کردیم. مشخص می‌کند که هزینه محصول ۲۵۰ دلار است و به عنوان یک کالای الکترونیکی مشخص شده است.

با استفاده از دستور case فیلدی به نام گروه محصول یا product category  تعریف می‌کنیم.

حال در ادامه می‌خواهیم کوئری بنویسیم که فیلدهای کد محصول و نام محصول و گروه محصول از اسکیمای Production جدول Product را استخراج کرده؛ به این صورت که اگر قیمت محصول برابر با صفر باشد، در این صورت این کالا خارج از موجودی هست. حال اگر قیمت محصول بین ۰ تا ۱۰۰ دلار باشد، در اینصورت محصول جزو کالای مصرفی خواهد بود. حال اگر قیمت محصول بین ۱۰۰ تا ۵۰۰ دلار باشد، در این صورت کالا جزو کالاهای الکترونیک است. اگر قیمت محصول بین ۵۰۰ تا ۱۵۰۰ دلار باشد، در اینصورت محصول جزو کالاهای لوکس بوده و در غیراینصورت، محصول جزو کالاهای اضافی خواهد بود. برای این منظور کوئری زیر را با دستور case می‌نویسیم و در ادامه کلیه رکوردهای جدول براساس لیست قیمت یا همان list price را به صورت نزولی مرتب می‌کنیم. برای این منظور کوئری زیر را می‌نویسیم:

SELECT ProductNumber,
Name, 
[Product category] = 
CASE 
WHEN ListPrice = 0 THEN 'Out of Stock items' 
WHEN ListPrice > 0 and ListPrice<=100 THEN 'Consumer goods' 
WHEN ListPrice >100 and ListPrice <= 500 THEN 'Electronics items' 
WHEN ListPrice >500 and ListPrice < 1500 THEN 'Luxury items' 
ELSE 'Extra items' 
END 
FROM Production.Product 
order by ListPrice desc

خروجی کوئری فوق مطابق تصویر زیر است:

 استفاده از دستورات Case,Order By در SQL Server

در کوئری‌های SQL  از عبارت ORDER BY برای مرتب سازی داده‌ها به ترتیب صعودی یا نزولی استفاده می‌کنند. می‌توانید از عبارات CASE همراه با عبارت ORDER BY استفاده کنید. فرض کنید از جدول محصولات، فیلدهای نام محصول [ProductName] و  لیست قیمت [ListPrice] را بازیابی می‌کنیم. ما می‌خواهیم نتایج را به روش‌های زیر مرتب کنیم:

حال در ادامه ابتدا با دستور Select فیلدهای name و قیمت محصول یا List price از اسکیمای Production جدول Product  استخراج می‌کنیم. برای این منظور کوئری زیر را می‌نویسیم:

select Name,
ListPrice
from Production.Product

خروجی کوئری مطابق تصویر زیر است:

اگر قیمت لیست محصول کمتر از ۲۰۰۰ باشد، نتیجه را به ترتیب مرتب سازی را به صورت صعودی می‌خواهیم نمایش داده شود.

اگر قیمت لیست محصول بیشتر از ۲۰۰۰ باشد، نتیجه مرتب سازی می‌خواهیم نزولی باشد.

در این کوئری از دو عبارت SQL CASE برای پیاده سازی منطق استفاده می‌کنیم.

در اینجا می‌خواهیم برای رکوردهایی که فیلد لیست قیمت یا list price  کمتر از ۲۰۰۰ دلار باشد، به ترتیب صعودی مرتب گردد و برای رکوردهایی که فیلد لیست قیمت یا list price آن‌ها بیشتر از ۲۰۰۰ دلار باشد، به ترتیب نزولی مرتب گردد. برای این منظور کوئری زیر را می‌نویسیم:

SELECT Name,
ListPrice
FROM Production.Product
ORDER BY CASE
WHEN ListPrice<=2000 THEN ListPrice END 
,CASE WHEN ListPrice >2000 THEN ListPrice END DESC

خروجی کوئری فوق مطابق تصویر زیر است:

مطابق تصویر ذیل ردیف‌های ۱ تا ۱۷ رکوردهای محصول به صورت نزولی مرتب شده و از ردیف ۱۷ به بعد رکوردهای اطلاعات محصول به ترتیب صعودی مرتب‌سازی انجام شده است.

در خروجی کوئری زیر، می‌توانید مرتب‌سازی داده‌ها را که به ترتیب نزولی و صعودی ظاهر می‌شوند، تأیید کنید.

در مثالی دیگر، فرض کنید می‌خواهیم داده‌ها را در جدول کارکنان براساس شرط زیر مرتب کنیم:

برای کارکنان فعال یعنی کارکنانی که current flag آن‌ها برابر با یک است، داده‌ها باید براساس ستون تاریخ استخدام مرتب‌سازی گردند.

برای کارمندان غیرفعال، باید داده‌ها را براساس مقادیر موجود در ستون تاریخ تولد مرتب کرد.

حال در ادامه در ابتدا فیلدهای national id number,job title,hire date,birthdate,currentflag, از اسکیمای human resources جدول Employee استخراج کرده. برای این منظور کوئری زیر را می‌نویسیم:

select
NationalIDNumber,
JobTitle,
HireDate,
BirthDate,
CurrentFlag
from HumanResources.Employee

خروجی کوئری فوق مطابق تصویر زیر است:

حال در ادامه می‌خواهیم کوئری بنویسیم که فیلدهای کد ملی و عنوان شغلی و تاریخ استخدام و تاریخ تولد و Current Flag از اسکیمای human resource جدول employee استخراج کرده. برای کارمندان فعال، یعنی کارمندانی که Current Falg آن‌ها برابر با یک است، تاریخ استخدام یا همان hire date را نشان دهیم و برای کارمندان غیرفعال، یعنی کارمندانی که current falg آن‌ها برابر با صفر است، تاریخ تولد یا همان birth date را نمایش دهیم. برای این منظور کوئری زیر را می‌‌نویسیم:

SELECT NationalIDNumber,
JobTitle,
Hiredate,
BirthDate, 
currentflag
FROM HumanResources.Employee 
ORDER BY 
CASE CURRENTFLAG WHEN 1 THEN HireDate 
else Birthdate 
end

خروجی کوئری فوق مطابق تصویر زیر است:

دستورات Aggregation Function و Case Statement

Aggregation function در SQL Server محاسبات را انجام می‌دهند و یک مقدار واحد را برمی‌گردانند. نمونه‌هایی از توابع Aggregation Function عبارتند از MIN، MAX، COUNT، sum.

فرض کنید ما می‌خواهیم تعداد استخدام کارمندان را برای هرسال از سال ۲۰۰۷ تا ۲۰۱۰ بازیابی کنیم. باید نتایج را در قالب زیر نمایش دهد:

برای این منظور ازaggregation function از نوع COUNT در SQL Server استفاده می‌کنیم.

ابتدا تابع SQL DATEPART داده‌ها را براساس سال فیلتر می‌کند. به عنوان مثال، تابع date part داده‌های سال ۲۰۰۷ را فیلتر می‌کند.

اگر سال ۲۰۰۷ باشد، از دستور CASE برای برگرداندن ۱ استفاده می‌کنیم.

تابع Count Aggregation Function رکوردها را می‌شمارد و نتایج را نمایش می‌دهد.

به طور مشابه، کوئری برای سال‌های باقی مانده کار می‌کند.

SELECT Count(CASE
WHEN Datepart(yy, hiredate) = 2007 THEN 1
ELSE NULL
END) AS [2007Hires],
Count(CASE
WHEN Datepart(yy, hiredate) = 2008 THEN 1
ELSE NULL
END) AS [2008Hires],
Count(CASE
WHEN Datepart(yy, hiredate) = 2009 THEN 1
ELSE NULL
END) AS [2009Hires],
Count(CASE
WHEN Datepart(yy, hiredate) = 2009 THEN 1
ELSE NULL
END) AS [2010Hires]
FROM HumanResources.Employee

خروجی کوئری فوق مطابق تصویر زیر است:

به طور مشابه، فرض کنید می‌خواهیم از تابع انبوه GROUP BY برای گروه‌بندی ردیف‌هایی با دسته‌بندی محصول یکسان استفاده کنیم. ما می‌توانیم عبارت CASE را در SQL برای مرتب سازی داده‌ها از مجموعه نتایج گروه بندی شده مشخص کنیم.

برای این منظور کوئری زیر را می‌نویسیم:

SELECT [Product category] = CASE
WHEN listprice = 0 THEN 'Out of Stock items'
WHEN listprice > 0
AND listprice <= 100 THEN 'Consumer goods'
WHEN listprice > 100
AND listprice <= 500 THEN 'Electronics items'
WHEN listprice > 500
AND listprice < 1500 THEN 'Luxury items'
ELSE 'Extra items'
END,
Min(listprice) AS MinPrice,
Max(listprice) AS MaxPrice,
Count(listprice) AS Numberofproducts
FROM production.product
GROUP BY CASE
WHEN listprice = 0 THEN 'Out of Stock items'
WHEN listprice > 0
AND listprice <= 100 THEN 'Consumer goods'
WHEN listprice > 100
AND listprice <= 500 THEN 'Electronics items'
WHEN listprice > 500
AND listprice < 1500 THEN 'Luxury items'
ELSE 'Extra items'
END
ORDER BY numberofproducts DESC

خروجی کوئری فوق مطابق تصویر زیر است:

در کوئری فوق از دو عبارت SQL CASE استفاده می‌کنیم.

اولین عبارت CASE داده‌ها را براساس عبارت تعریف شده در لیست قیمت دسته‌بندی می‌کند. با استفاده از این عبارت CASE، محصولات را به دسته‌های زیر تقسیم می‌کنیم:

• اقلام خارج از انبار
• کالاهای مصرفی
• اقلام الکترونیکی
• اقلام لوکس

در دستور مورد دوم، از تابع GROUP BY برای گروه‌بندی نتیجه براساس دسته استفاده می‌کنیم.

علاوه بر این، نتایج را براساس NumberOfProducts به ترتیب نزولی مرتب‌سازی می‌کنیم.

تقسیم مقادیر غیرصفر بر صفر با استفاده از دستور Case

اگر مقدار مخرج صفر باشد، خطای تقسیم بر صفر رخ می‌دهد. اگر این کسرها را در SQL Server انجام دهید، مانند شکل زیر به شما خطای تقسیم بر صفر را می‌دهد.

این یک تمرین عالی است که سؤالات خود را به گونه‌ای بنویسید که از این اشتباهات رایج جلوگیری کنید. برای جلوگیری از این امر، از منطق کسری در یک دستور CASE استفاده می‌کنیم.

ابتدا با دستور use دیتابیس master را فراخوانی می‌کنیم:

use master 
go

با استفاده از دستور Declare دو متغیر به نام student1, student2 را تعریف می‌کنیم.

با استفاده دستور Set به متغیرهای studdent1,student2 مقادیر ۱۰۰ و ۰ را تخصیص می‌دهیم.

حال کوئری می‌نویسیم که در صورتی که مقدار متغیر student2 برابر با صفر باشد، مقدار null را نمایش دهد؛ در غیر اینصورت، نسبت متغیر student1 به student2 را محاسبه کنیم.

DECLARE @Student1 INT
DECLARE @Student2 INT

SET @Student1=100
SET @Student2=0

select
CASE WHEN @Student2=0
THEN NULL
ELSE @Student1/@Student2 end as StudentMarksRatio

خروجی کوئری فوق مطابق تصویر زیر است:

نکات کاربردی در مورد استفاده از عبارات Case 

دستورات SQL CASE از ۱۰ سطح تودرتو پشتیبانی می‌کنند.

شما نمی‌توانید جریان اجرای دستورات، توابع یا procedure را بااستفاده از عبارات CASE کنترل کنید.

همیشه باید از یک بلوک ELSE استفاده کنید تا اگر شرطی برآورده نشد، یک مقدار پیش فرض دریافت کنید.

شما باید از استفاده از شرایط متناقض در دستور SQL CASE اجتناب کنید. دستور CASE به صورت متوالی کار می‌کند و با اولین شرط موفقیت‌آمیز، ارزیابی را متوقف می‌کند.