افزایش کارایی TSQL با عبارت CASE در SQL Server راهنمای کامل منطق شرطی

افزایش کارایی T-SQL با عبارت CASE: راهنمای کامل منطق If/Else در SQL Server

در SQL Server، عبارت CASE راهی قدرتمند برای پیاده‌سازی منطق If/Else (اگر/در غیر این صورت) در کوئری‌ها، به روزرسانی‌ها و حتی تعریف ستون‌های جدید است. این عبارت امکان می‌دهد تا بر اساس شرایط مختلف، نتایج متفاوتی را تولید کنید و انعطاف‌پذیری زیادی در مدیریت و نمایش داده‌ها فراهم می‌آورد. در ادامه به بررسی انواع و کاربردهای عبارت CASE در T-SQL می‌پردازیم.

دو نوع اصلی از عبارت CASE وجود دارد:

1. **عبارت CASE ساده (Simple CASE Statement)**: این نوع عبارت یک مقدار ورودی را با لیستی از مقادیر مقایسه می‌کند.

ساختار عبارت CASE ساده به شکل زیر است:

CASE input_expression
     WHEN when_expression THEN result_expression 
     [ ...n ] 
     [ ELSE else_result_expression ] 
END

در این ساختار، `input_expression` با هر `when_expression` مقایسه می‌شود. اگر تطابقی یافت شد، `result_expression` مربوطه برگردانده می‌شود. بخش `ELSE` اختیاری است و اگر هیچ یک از شرایط `WHEN` درست نباشد، مقدار آن بازگردانده می‌شود. در صورت عدم وجود `ELSE` و عدم تطابق، NULL برگردانده می‌شود.

**مثال اول: استفاده از CASE ساده**
فرض کنید می‌خواهید بر اساس یک کد عددی، وضعیت متنی مربوط به آن را نمایش دهید.

SELECT  BusinessEntityID ,
        NationalIDNumber ,
        CASE MaritalStatus
          WHEN 'M' THEN 'Married'
          WHEN 'S' THEN 'Single'
          ELSE 'Unknown'
        END AS MaritalStatusDescription ,
        Gender ,
        HireDate
FROM    HumanResources.Employee
WHERE   BusinessEntityID BETWEEN 10 AND 20;

در این مثال، بر اساس مقدار ستون `MaritalStatus` (که ‘M’ یا ‘S’ است)، وضعیت تأهل به “Married”، “Single” یا “Unknown” تغییر می‌کند.

2. **عبارت CASE جستجو شده (Searched CASE Statement)**: این نوع عبارت به شما امکان می‌دهد تا چندین شرط منطقی را ارزیابی کنید.

ساختار عبارت CASE جستجو شده به شکل زیر است:

CASE
     WHEN Boolean_expression THEN result_expression 
     [ ...n ] 
     [ ELSE else_result_expression ] 
END

این ساختار شباهت زیادی به دستور `IF…ELSE IF…ELSE` دارد. هر `Boolean_expression` به طور مستقل ارزیابی می‌شود. اولین شرطی که TRUE باشد، `result_expression` مربوط به خود را بازمی‌گرداند. بخش `ELSE` نیز در این نوع اختیاری است و در صورت عدم برآورده شدن هیچ شرطی و عدم وجود `ELSE`، NULL برگردانده می‌شود.

**مثال دوم: استفاده از CASE جستجو شده**
برای دسته‌بندی کارمندان بر اساس تاریخ استخدام و تعیین وضعیت آن‌ها:

SELECT  BusinessEntityID ,
        NationalIDNumber ,
        HireDate ,
        CASE
          WHEN HireDate BETWEEN '2005-01-01' AND '2005-12-31'
          THEN 'Hired in 2005'
          WHEN HireDate BETWEEN '2006-01-01' AND '2006-12-31'
          THEN 'Hired in 2006'
          ELSE 'Hired in other years'
        END AS HireYearCategory
FROM    HumanResources.Employee
WHERE   BusinessEntityID BETWEEN 10 AND 20;

در این کوئری، ستون `HireYearCategory` بر اساس سال استخدام کارمند، مقادیر متفاوتی را نمایش می‌دهد.

**کاربردهای پیشرفته عبارت CASE در SQL Server:**

**استفاده از عبارت CASE با توابع تجمیعی (Aggregate Functions)**
عبارت CASE می‌تواند در کنار توابع تجمیعی مانند COUNT، SUM، AVG و MAX برای انجام محاسبات شرطی به کار رود. این کار برای ساخت گزارش‌های خلاصه‌سازی بسیار مفید است.

**مثال سوم: شمارش مشروط با CASE و COUNT**
می‌توانید تعداد کارمندان زن و مرد را در یک کوئری به تفکیک شمارش کنید:

SELECT  COUNT(CASE WHEN Gender = 'M' THEN 1
                    ELSE NULL
               END) AS MaleCount ,
        COUNT(CASE WHEN Gender = 'F' THEN 1
                    ELSE NULL
               END) AS FemaleCount
FROM    HumanResources.Employee;

در این مثال، `COUNT` فقط مقادیر غیر NULL را می‌شمارد. بنابراین، با استفاده از `CASE`، فقط زمانی که شرط `Gender` مطابقت دارد، 1 را برمی‌گردانیم و در غیر این صورت NULL، که منجر به شمارش دقیق می‌شود.

**استفاده از عبارت CASE در بند ORDER BY**
عبارت CASE امکان مرتب‌سازی پویا را فراهم می‌کند. می‌توانید بر اساس شرایط خاص، ترتیب مرتب‌سازی نتایج را تغییر دهید.

**مثال چهارم: مرتب‌سازی پویا با CASE در ORDER BY**
برای مرتب‌سازی بر اساس نام خانوادگی، اما با اولویت دادن به کارمندانی که وضعیت تأهلشان ‘M’ (Married) است:

SELECT  BusinessEntityID ,
        NationalIDNumber ,
        Gender ,
        MaritalStatus ,
        HireDate
FROM    HumanResources.Employee
WHERE   BusinessEntityID BETWEEN 10 AND 20
ORDER BY CASE
           WHEN MaritalStatus = 'M' THEN 1
           ELSE 2
         END ,
         Gender DESC;

در این کوئری، کارمندان متأهل (MaritalStatus = ‘M’) ابتدا قرار می‌گیرند و سپس سایر کارمندان، و سپس همه بر اساس جنسیت به صورت نزولی مرتب می‌شوند.

**استفاده از عبارت CASE برای مدیریت مقادیر NULL**
CASE می‌تواند جایگزین مناسبی برای توابعی مانند ISNULL یا COALESCE باشد، به خصوص زمانی که نیاز به منطق پیچیده‌تر برای جایگزینی NULL دارید.

**مثال پنجم: جایگزینی NULL با CASE**
فرض کنید می‌خواهید مقادیر NULL در یک ستون را با یک متن خاص جایگزین کنید:

SELECT  BusinessEntityID ,
        NationalIDNumber ,
        Gender ,
        MaritalStatus ,
        CASE
          WHEN MaritalStatus IS NULL THEN 'Not Disclosed'
          ELSE MaritalStatus
        END AS MaritalStatus_Fixed ,
        HireDate
FROM    HumanResources.Employee
WHERE   BusinessEntityID BETWEEN 10 AND 20;

در این مثال، اگر `MaritalStatus` برابر با NULL باشد، “Not Disclosed” نمایش داده می‌شود، در غیر این صورت مقدار واقعی ستون.

**استفاده از عبارت CASE برای به‌روزرسانی‌های شرطی (Conditional Updates)**
می‌توانید از عبارت CASE در دستور UPDATE برای تغییر مقادیر ستون‌ها بر اساس شرایط مختلف استفاده کنید.

**مثال ششم: به‌روزرسانی مشروط با CASE**
برای به‌روزرسانی ستون `MaritalStatus` به ‘U’ (Unknown) برای کارمندانی که در سال 2005 استخدام شده‌اند و در حال حاضر وضعیت ‘S’ (Single) دارند:

-- ابتدا یک جدول موقت برای آزمایش ایجاد کنید
CREATE TABLE #EmployeeStatus (
    BusinessEntityID INT,
    HireDate DATE,
    MaritalStatus CHAR(1)
);

INSERT INTO #EmployeeStatus (BusinessEntityID, HireDate, MaritalStatus)
VALUES
(1, '2005-03-15', 'S'),
(2, '2006-07-20', 'M'),
(3, '2005-11-01', 'S'),
(4, '2004-01-10', 'M');

-- مشاهده داده های قبل از به روزرسانی
SELECT * FROM #EmployeeStatus;

-- دستور UPDATE با CASE
UPDATE #EmployeeStatus
SET MaritalStatus = CASE
                      WHEN HireDate BETWEEN '2005-01-01' AND '2005-12-31'
                           AND MaritalStatus = 'S' THEN 'U'
                      ELSE MaritalStatus -- اگر شرط بالا برقرار نبود، مقدار فعلی حفظ شود
                    END
WHERE HireDate BETWEEN '2005-01-01' AND '2005-12-31'
      AND MaritalStatus = 'S';

-- مشاهده داده های بعد از به روزرسانی
SELECT * FROM #EmployeeStatus;

-- حذف جدول موقت
DROP TABLE #EmployeeStatus;

این دستور UPDATE تنها رکوردهایی را که هر دو شرط (سال استخدام 2005 و وضعیت تأهل ‘S’) را برآورده می‌کنند، به‌روزرسانی می‌کند.

**استفاده از عبارت CASE برای چندین شرط (معادل IF تو در تو)**
CASE جستجو شده می‌تواند به طور موثر جایگزین ساختارهای `IF…ELSE IF` تو در تو شود و خوانایی کد را افزایش دهد.

**مثال هفتم: مدیریت چندین شرط با CASE**
برای تعیین رتبه کارمندان بر اساس ترکیبی از جنسیت و وضعیت تأهل:

SELECT  BusinessEntityID ,
        NationalIDNumber ,
        Gender ,
        MaritalStatus ,
        CASE
          WHEN Gender = 'M' AND MaritalStatus = 'M' THEN 'Married Male'
          WHEN Gender = 'M' AND MaritalStatus = 'S' THEN 'Single Male'
          WHEN Gender = 'F' AND MaritalStatus = 'M' THEN 'Married Female'
          WHEN Gender = 'F' AND MaritalStatus = 'S' THEN 'Single Female'
          ELSE 'Unknown Status'
        END AS EmployeeCategory
FROM    HumanResources.Employee
WHERE   BusinessEntityID BETWEEN 10 AND 20;

در این مثال، یک ستون `EmployeeCategory` جدید بر اساس ترکیب جنسیت و وضعیت تأهل کارمندان ایجاد می‌شود.

عبارت CASE ابزاری بسیار قدرتمند و انعطاف‌پذیر در SQL Server است که برای پیاده‌سازی منطق شرطی در سناریوهای مختلف مدیریت و تحلیل داده استفاده می‌شود. تسلط بر آن به شما کمک می‌کند تا کوئری‌های کارآمدتر و گزارش‌های دقیق‌تری بنویسید.