روشهای یافتن حداکثر مقدار از چندین ستون در SQL Server

چگونه حداکثر مقدار از چندین ستون را در SQL Server بیابیم؟ روش‌های کاربردی

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

**آماده‌سازی داده‌های نمونه در SQL Server**

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

برای ایجاد جدول `dbo.TestTable` و پر کردن آن با داده‌های نمونه از دستورات زیر استفاده کنید:

CREATE TABLE dbo.TestTable
(
    Col1 INT,
    Col2 INT,
    Col3 INT,
    Col4 INT,
    Col5 INT
);

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

INSERT INTO dbo.TestTable VALUES (1,2,3,4,5);
INSERT INTO dbo.TestTable VALUES (10,20,NULL,40,30);
INSERT INTO dbo.TestTable VALUES (100,50,75,125,110);
INSERT INTO dbo.TestTable VALUES (NULL,NULL,NULL,NULL,NULL);
INSERT INTO dbo.TestTable VALUES (2,4,10,1,1);
INSERT INTO dbo.TestTable VALUES (5,1,8,2,9);

برای مشاهده داده‌های نمونه‌ای که اضافه کرده‌اید، می‌توانید این کوئری را اجرا کنید:

SELECT * FROM dbo.TestTable;

**یافتن حداکثر مقدار با استفاده از عبارت CASE**

یکی از روش‌های رایج برای یافتن حداکثر مقدار از چندین ستون در SQL Server، استفاده از عبارت `CASE` است. این روش به شما امکان می‌دهد تا مقادیر هر ستون را مقایسه کرده و بزرگترین آن‌ها را انتخاب کنید. این روش زمانی مفید است که تعداد ستون‌ها محدود باشد. در این مثال، ما از عبارت `CASE` تو در تو برای مقایسه `Col1` با `Col2`، سپس نتیجه با `Col3` و به همین ترتیب تا `Col5` استفاده می‌کنیم.

SELECT Col1, Col2, Col3, Col4, Col5,
       CASE WHEN Col1 > Col2 AND Col1 > Col3 AND Col1 > Col4 AND Col1 > Col5 THEN Col1
            WHEN Col2 > Col3 AND Col2 > Col4 AND Col2 > Col5 THEN Col2
            WHEN Col3 > Col4 AND Col3 > Col5 THEN Col3
            WHEN Col4 > Col5 THEN Col4
            ELSE Col5
       END AS MaxValue
FROM dbo.TestTable;

توجه داشته باشید که عبارت `CASE` می‌تواند کمی طولانی شود، به خصوص اگر تعداد ستون‌ها زیاد باشد. همچنین، اگر هر ستون `NULL` باشد، نتیجه `CASE` نیز `NULL` خواهد بود. برای مثال، در ردیف چهارم که همه ستون‌ها `NULL` هستند، `MaxValue` نیز `NULL` خواهد شد.

**یافتن حداکثر مقدار با استفاده از CROSS APPLY**

روش دیگر برای پیدا کردن حداکثر مقدار، استفاده از `CROSS APPLY` در ترکیب با `VALUES` است. این روش انعطاف‌پذیری بیشتری را ارائه می‌دهد و کد را برای تعداد زیادی ستون خواناتر می‌کند. در اینجا، ما از `VALUES` برای تبدیل ستون‌ها به ردیف‌ها و سپس از `MAX` برای یافتن بزرگترین مقدار در آن ردیف‌ها استفاده می‌کنیم.

SELECT Col1, Col2, Col3, Col4, Col5,
       ca.MaxValue
FROM dbo.TestTable
CROSS APPLY (
    SELECT MAX(v) AS MaxValue
    FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) AS T(v)
) ca;

در این روش، `VALUES (Col1), (Col2), (Col3), (Col4), (Col5)` هر مقدار ستون را به عنوان یک ردیف جداگانه در یک جدول موقت به نام `T` با ستون `v` تبدیل می‌کند. سپس `MAX(v)` بر روی این مقادیر اجرا شده و حداکثر را برای هر ردیف اصلی `dbo.TestTable` برمی‌گرداند. این روش به طور خودکار مقادیر `NULL` را نادیده می‌گیرد و تنها از مقادیر غیر `NULL` برای محاسبه حداکثر استفاده می‌کند.

**یافتن حداکثر مقدار با استفاده از UNPIVOT**

`UNPIVOT` یک ابزار قدرتمند در SQL Server برای تبدیل ستون‌ها به ردیف‌ها است. می‌توانیم از آن برای “باز کردن” ستون‌ها و سپس یافتن حداکثر مقدار از میان ردیف‌های حاصل استفاده کنیم.

SELECT Col1, Col2, Col3, Col4, Col5,
       pvt.MaxValue
FROM dbo.TestTable
UNPIVOT (
    Value FOR Col IN (Col1, Col2, Col3, Col4, Col5)
) AS unpvt
CROSS APPLY (
    SELECT MAX(Value) AS MaxValue
    FROM unpvt
) AS pvt;

**نکته مهم:** `UNPIVOT` به طور پیش‌فرض، ردیف‌هایی که شامل مقادیر `NULL` هستند را نادیده می‌گیرد. این بدان معناست که اگر همه ستون‌های مورد نظر برای یک ردیف `NULL` باشند، آن ردیف در خروجی `UNPIVOT` ظاهر نمی‌شود و در نتیجه `MaxValue` نیز `NULL` خواهد بود.

**استفاده از CTE همراه با UNPIVOT**

برای خوانایی و سازماندهی بهتر کوئری‌ها، به ویژه زمانی که منطق پیچیده‌تر می‌شود، می‌توانیم `UNPIVOT` را در یک عبارت جدول مشترک (CTE) استفاده کنیم. این به ما اجازه می‌دهد تا مراحل را به صورت منطقی تقسیم کنیم.

WITH unpivoted AS (
    SELECT Col1, Col2, Col3, Col4, Col5, Value
    FROM dbo.TestTable
    UNPIVOT (
        Value FOR Col IN (Col1, Col2, Col3, Col4, Col5)
    ) AS unpvt
)
SELECT Col1, Col2, Col3, Col4, Col5,
       MAX(Value) AS MaxValue
FROM unpivoted
GROUP BY Col1, Col2, Col3, Col4, Col5;

این کوئری ابتدا ستون‌ها را با `UNPIVOT` به ردیف‌ها تبدیل می‌کند، سپس در CTE `unpivoted` ذخیره می‌کند. در نهایت، `MAX(Value)` را بر روی مقادیر `unpivoted` گروه‌بندی شده بر اساس ستون‌های اصلی جدول اعمال می‌کند تا حداکثر مقدار را برای هر ردیف اصلی بدست آورد. این روش نیز مانند `CROSS APPLY` مقادیر `NULL` را به درستی مدیریت می‌کند.

**استفاده از تابع GREATEST (در SQL Server موجود نیست)**

در برخی از سیستم‌های مدیریت پایگاه داده مانند PostgreSQL یا MySQL، تابعی به نام `GREATEST` وجود دارد که به سادگی حداکثر مقدار را از لیستی از آرگومان‌ها برمی‌گرداند. استفاده از آن می‌تواند به شکل زیر باشد:

GREATEST(Col1, Col2, Col3, Col4, Col5)

متأسفانه، این تابع به طور مستقیم در SQL Server وجود ندارد. با این حال، همانطور که در بخش‌های قبلی توضیح داده شد، می‌توانید از روش‌های `CASE`، `CROSS APPLY` با `VALUES`، یا `UNPIVOT` برای دستیابی به عملکردی مشابه در SQL Server استفاده کنید. این روش‌ها جایگزین‌های کارآمدی برای `GREATEST` در محیط SQL Server هستند.