عبارت CASE در SQL Server راهنمای جامع و مثال‌های کاربردی

عبارت CASE در SQL Server: راهنمای جامع و مثال‌های کاربردی برای بهینه‌سازی کوئری‌ها

دستور CASE در SQL Server به شما امکان می‌دهد تا منطق IF-THEN-ELSE را مستقیماً در کوئری‌های SQL خود پیاده‌سازی کنید. این دستور مجموعه‌ای از شرایط را ارزیابی کرده و نتیجه را برای اولین شرطی که برآورده شود، بازمی‌گرداند. این ویژگی انعطاف‌پذیری زیادی در پردازش داده‌ها و ساخت کوئری‌های پیچیده‌تر فراهم می‌کند.

سینتکس عبارت CASE در SQL

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

CASE WHEN Expression THEN Result [WHEN Expression THEN Result...] [ELSE Result] END

در این ساختار:

• Expression: شرطی است که باید ارزیابی شود.
• Result: مقداری است که در صورت درست بودن Expression بازگردانده می‌شود.
• ELSE Result: (اختیاری) مقداری است که اگر هیچ یک از Expressionها درست نباشند، بازگردانده می‌شود. اگر ELSE مشخص نشود و هیچ یک از شرایط درست نباشد، CASE مقدار NULL را برمی‌گرداند.

مثال‌های کاربردی عبارت CASE در SQL Server

۱. استفاده از عبارت CASE ساده

برای نشان دادن عملکرد پایه عبارت CASE، یک جدول نمونه ایجاد کرده و داده‌ها را در آن وارد می‌کنیم. این مثال اساس کار با CASE را به وضوح نمایش می‌دهد.

ابتدا جدول مورد نیاز را ایجاد می‌کنیم:

CREATE TABLE TestScores (
    StudentID INT PRIMARY KEY,
    Score INT
);

سپس داده‌های نمونه را به جدول اضافه می‌کنیم:

INSERT INTO TestScores (StudentID, Score) VALUES
(1, 85),
(2, 92),
(3, 78),
(4, 65),
(5, 95);

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

SELECT
    StudentID,
    Score,
    CASE
        WHEN Score >= 70 THEN 'Pass'
        ELSE 'Fail'
    END AS Result
FROM TestScores;

۲. عبارت CASE جستجو شده (Searched CASE Statement)

عبارت CASE جستجو شده امکان بررسی چندین شرط پیچیده‌تر را فراهم می‌کند، بدون اینکه نیاز باشد Expression اولیه را با هر WHEN تکرار کنیم. این نوع CASE برای سناریوهایی که نیاز به ارزیابی منطقی بین ستون‌های مختلف یا شرایط متفاوت دارید، بسیار کارآمد است. این مثال درجه‌بندی دانش‌آموزان را بر اساس نمراتشان نشان می‌دهد:

SELECT
    StudentID,
    Score,
    CASE
        WHEN Score >= 90 THEN 'A'
        WHEN Score >= 80 THEN 'B'
        WHEN Score >= 70 THEN 'C'
        WHEN Score >= 60 THEN 'D'
        ELSE 'F'
    END AS Grade
FROM TestScores;

۳. عبارت CASE با چندین شرط

شما می‌توانید چندین شرط را با استفاده از عملگرهای منطقی (مانند AND و OR) در داخل هر WHEN ترکیب کنید. این قابلیت انعطاف‌پذیری عبارت CASE را برای رسیدگی به سناریوهای پیچیده‌تر که نیاز به ارزیابی چندگانه دارند، افزایش می‌دهد. در این مثال، وضعیت دانش‌آموزان بر اساس نمره و وضعیت تکمیل تکالیف فرضی (که در این مثال فقط بر اساس نمره است، اما می‌توانست شامل ستون‌های دیگر نیز باشد) مشخص می‌شود:

SELECT
    StudentID,
    Score,
    CASE
        WHEN Score >= 90 AND StudentID IN (1, 2, 5) THEN 'Excellent'
        WHEN Score >= 70 AND StudentID IN (3) THEN 'Good'
        ELSE 'Needs Improvement'
    END AS PerformanceStatus
FROM TestScores;

۴. مرتب‌سازی نتایج با عبارت CASE (ORDER BY)

عبارت CASE را می‌توان در بند ORDER BY نیز استفاده کرد تا ترتیب نمایش نتایج را به صورت پویا و بر اساس منطق سفارشی تعیین کنید. این یک تکنیک پیشرفته SQL برای مرتب‌سازی داده‌ها به روش‌های غیرمعمول است. در این مثال، دانش‌آموزان بر اساس نمره به صورت صعودی مرتب می‌شوند، اما دانش‌آموزان با نمره بالاتر از ۹۰ (نمرات ممتاز) در ابتدا قرار می‌گیرند:

SELECT
    StudentID,
    Score
FROM TestScores
ORDER BY
    CASE
        WHEN Score >= 90 THEN 1  -- High scores first
        ELSE 2                  -- Other scores second
    END,
    Score DESC;                 -- Then sort by score descending within each group

توضیح: این کوئری ابتدا دانش‌آموزانی با نمره ۹۰ یا بالاتر را (به دلیل مقدار ۱ در CASE) نمایش می‌دهد و سپس مابقی را (مقدار ۲ در CASE). در هر گروه، نمرات به صورت نزولی مرتب می‌شوند.

۵. جلوگیری از خطای تقسیم بر صفر با عبارت CASE

یکی از کاربردهای حیاتی عبارت CASE در SQL، جلوگیری از خطای تقسیم بر صفر (Divide by Zero) است. این خطا زمانی رخ می‌دهد که در عملیات تقسیم، مخرج صفر باشد. با استفاده از CASE، می‌توانیم این شرایط را پیش‌بینی کرده و مقدار جایگزین یا منطق مناسبی را اعمال کنیم تا از بروز خطا جلوگیری شود. این مثال نرخ موفقیت را محاسبه می‌کند و در صورت صفر بودن تعداد کل، از خطای تقسیم بر صفر جلوگیری می‌کند:

ابتدا یک جدول جدید برای مثال ایجاد می‌کنیم:

CREATE TABLE SalesData (
    Region NVARCHAR(50),
    TotalSales INT,
    SuccessfulSales INT
);

داده‌های نمونه را اضافه می‌کنیم:

INSERT INTO SalesData (Region, TotalSales, SuccessfulSales) VALUES
('North', 100, 80),
('South', 0, 0),       -- This will cause divide by zero if not handled
('East', 50, 45),
('West', 75, 60);

اکنون، محاسبه نرخ موفقیت با استفاده از CASE:

SELECT
    Region,
    TotalSales,
    SuccessfulSales,
    CASE
        WHEN TotalSales = 0 THEN 0.0  -- Avoid divide by zero
        ELSE CAST(SuccessfulSales AS DECIMAL(5,2)) / TotalSales
    END AS SuccessRate
FROM SalesData;

۶. استفاده از عبارت CASE در بند GROUP BY

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

SELECT
    CASE
        WHEN TotalSales >= 70 THEN 'High Sales'
        ELSE 'Low Sales'
    END AS SalesCategory,
    SUM(SuccessfulSales) AS TotalSuccessfulSales
FROM SalesData
GROUP BY
    CASE
        WHEN TotalSales >= 70 THEN 'High Sales'
        ELSE 'Low Sales'
    END;

۷. به‌روزرسانی شرطی داده‌ها با عبارت CASE

عبارت CASE در دستور UPDATE به شما اجازه می‌دهد تا مقادیر ستون‌ها را به صورت شرطی و بر اساس منطق خاصی به‌روزرسانی کنید. این قابلیت برای سناریوهایی که نیاز به تغییرات دسته جمعی دارید اما هر تغییر به شرط خاصی وابسته است، فوق‌العاده کاربردی است. در این مثال، وضعیت دانش‌آموزان را بر اساس نمره‌شان در جدول TestScores به‌روزرسانی می‌کنیم:

ابتدا یک ستون برای ذخیره وضعیت اضافه می‌کنیم:

ALTER TABLE TestScores
ADD Status NVARCHAR(50);

اکنون با استفاده از CASE در UPDATE ستون Status را به‌روزرسانی می‌کنیم:

UPDATE TestScores
SET Status = CASE
                WHEN Score >= 90 THEN 'Excellent'
                WHEN Score >= 70 THEN 'Good'
                ELSE 'Unsatisfactory'
             END;

برای بررسی نتیجه به‌روزرسانی:

SELECT * FROM TestScores;

۸. استفاده از عبارت CASE در بند WHERE

اگرچه استفاده از عبارت CASE در بند WHERE کمتر رایج است (زیرا اغلب می‌توان از عملگرهای منطقی مستقیم استفاده کرد)، اما در برخی سناریوهای پیچیده که نیاز به فیلتر کردن پویا بر اساس چندین شرط دارید، می‌تواند مفید باشد. این مثال دانش‌آموزانی را انتخاب می‌کند که نمره آن‌ها بر اساس یک معیار پویا بالاتر از یک حد آستانه باشد:

SELECT
    StudentID,
    Score
FROM TestScores
WHERE
    CASE
        WHEN StudentID = 1 THEN Score >= 85
        WHEN StudentID = 2 THEN Score >= 90
        ELSE Score >= 70
    END = 1; -- For boolean logic, 1 means true, 0 means false in some SQL contexts or can compare directly

نکته: در SQL Server، عبارت‌های بولی در WHERE مستقیماً ارزیابی می‌شوند. مقدار 1 (True) و 0 (False) برای مقایسه‌های بولی معمولاً در سیستم‌های دیگر بیشتر دیده می‌شود. در SQL Server، می‌توانید مستقیماً WHEN ... THEN True/False یا WHEN ... THEN 1/0 را استفاده کنید و نتیجه را مقایسه کنید.

۹. عبارت CASE در رویه‌های ذخیره‌شده (Stored Procedures)

ادغام عبارت CASE در رویه‌های ذخیره‌شده SQL Server، امکان ایجاد منطق تجاری پیچیده و پارامتری‌شده را فراهم می‌کند. این رویکرد به شما اجازه می‌دهد تا رفتار رویه را بر اساس ورودی‌های مختلف تغییر دهید. در این مثال، یک رویه ذخیره‌شده ایجاد می‌کنیم که لیست دانش‌آموزان را بر اساس یک پارامتر وضعیت (قبول یا رد) فیلتر می‌کند:

CREATE PROCEDURE GetStudentsByStatus
    @StatusFilter NVARCHAR(50)
AS
BEGIN
    SELECT
        StudentID,
        Score,
        CASE
            WHEN Score >= 70 THEN 'Pass'
            ELSE 'Fail'
        END AS ResultStatus
    FROM TestScores
    WHERE
        CASE
            WHEN Score >= 70 THEN 'Pass'
            ELSE 'Fail'
        END = @StatusFilter;
END;

برای اجرای رویه ذخیره‌شده و مشاهده نتایج:

EXEC GetStudentsByStatus 'Pass';

EXEC GetStudentsByStatus 'Fail';

۱۰. عبارت CASE با ساب‌کوئری‌ها (Subqueries)

ترکیب عبارت CASE با ساب‌کوئری‌ها امکان ایجاد منطق شرطی بسیار قدرتمند و پیچیده را فراهم می‌آورد. این ترکیب به شما اجازه می‌دهد تا نتایج ساب‌کوئری‌ها را به عنوان بخشی از ارزیابی شرایط CASE در نظر بگیرید. این مثال، وضعیت دانش‌آموزان را بر اساس اینکه آیا نمره آن‌ها بالاتر از میانگین کل است یا خیر، دسته‌بندی می‌کند:

SELECT
    StudentID,
    Score,
    CASE
        WHEN Score > (SELECT AVG(Score) FROM TestScores) THEN 'Above Average'
        ELSE 'Below Average'
    END AS PerformanceLevel
FROM TestScores;

“`