آموزش جامعSQL Server(جلسه۱۷:عبارات(Expression)و نکات کلیدی)
1398/03/21 16:30 , میلاد صاحب نظر

آموزش جامعSQL Server(جلسه۱۷:عبارات(Expression)و نکات کلیدی)

بالاخره به جلسه پایانی مبحث اصول اولیه SQL Server رسیدیم. در این جلسه به آخرین موضوع مهم برای یادگیری اصول SQL Server یعنی عبارت‌ها و در آخر هم به بیان دو نکته مهم و کلیدی خواهیم پرداخت.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

مباحث این جلسه عبارت‌اند از:

  • CASE: اضافه کردن منطق if-else به queryهای SQL با استفاده از عبارات ساده و قابل جستجوی CASE.

  • COALESCE: کنترل مقادیر NULL به طور مؤثر با استفاده از عبارت COALESCE.

  • NULLIF: اگر دو آرگومان با هم برابر باشند مقدار NULL بازگردانده می‌شود، در غیر این صورت، اولین آرگومان بازگردانده می‌شود.

  • یافتن مقادیر تکراری: چگونگی یافتن مقادیر تکراری در یک یا چند ستون از یک جدول را نشان می‌دهد.

  • حذف مقادیر تکراری: چگونگی حذف ردیف‌های تکراری از یک جدول را توضیح می‌دهد.

با ما همراه باشید!

CASE در SQL Server

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

چون CASE یک عبارت است، می‌توانید از آن در هر دستوری که عبارت می‌پذیرد (مانند GROUP BY،، WHERE، SELECT و HAVING) استفاده کنید.

عبارت CASE ساده در SQL Server

کد زیر syntax عبارت CASE ساده را نشان می‌دهد:

CASE input   
    WHEN e1 THEN r1
    WHEN e2 THEN r2
    ...
    WHEN en THEN rn
    [ ELSE re ]   
END  

عبارت CASE ساده، عبارت ورودی (input) را با یک عبارت (ei) که در آن دستور WHEN قرار دارد از لحاظ برابری مقایسه می‌کند. اگر عبارت ورودی با عبارت دارای WHEN (یعنی ei) برابر باشد، نتیجه (ri) در دستور THEN متناظر بازگردانده می‌شود.

اگر عبارت ورودی با هیچ عبارتی برابر نباشد و دستور ELSE موجود باشد، عبارت CASE نتیجه موجود در دستور ELSE (یعنی re) را بازمی‌گرداند.

در صورتی که دستور ELSE حذف شده باشد و عبارت ورودی با هیچ عبارتی در دستور WHEN برابر نباشد، عبارت CASE مقدار NULL را بازمی‌گرداند.

الف) مثال استفاده از عبارت CASE در دستور SELECT

جدول sales.orders از پایگاه داده نمونه Bikestores را در نظر بگیرید:

جدول sales.orders

این مثال از تابع ()COUNT همراه با دستور GROUP BY برای بازگرداندن تعداد سفارش‌ها برای هر وضعیت سفارش استفاده می‌کند:

SELECT    
    order_status, 
    COUNT(order_id) order_count
FROM    
    sales.orders
WHERE 
    YEAR(order_date) = 2018
GROUP BY 
    order_status;

نتیجه به این شکل است:

نتایج

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

SELECT    
    CASE order_status
        WHEN 1 THEN 'Pending'
        WHEN 2 THEN 'Processing'
        WHEN 3 THEN 'Rejected'
        WHEN 4 THEN 'Completed'
    END AS order_status, 
    COUNT(order_id) order_count
FROM    
    sales.orders
WHERE 
    YEAR(order_date) = 2018
GROUP BY 
    order_status;

تصویر زیر خروجی را نشان می‌دهد:

نتایج

ب) مثال استفاده از عبارت CASE ساده در تابع جمع

Query زیر را مشاهده کنید:

SELECT    
    SUM(CASE
            WHEN order_status = 1
            THEN 1
            ELSE 0
        END) AS 'Pending', 
    SUM(CASE
            WHEN order_status = 2
            THEN 1
            ELSE 0
        END) AS 'Processing', 
    SUM(CASE
            WHEN order_status = 3
            THEN 1
            ELSE 0
        END) AS 'Rejected', 
    SUM(CASE
            WHEN order_status = 4
            THEN 1
            ELSE 0
        END) AS 'Completed', 
    COUNT(*) AS Total
FROM    
    sales.orders
WHERE 
    YEAR(order_date) = 2018;

خروجی به این شکل است:

نتایج

در این مثال:

  • اول، شرط موجود در دستور WHERE شامل سفارش فروش در سال ۲۰۱۸ است.

  • دوم، عبارت CASE بر اساس وضعیت سفارش مقدار 0 یا 1 را بازمی‌گرداند.

  • سوم، تابع ()SUM تعداد سفارش برای هر وضعیت سفارش را جمع می‌زند.

  • چهارم، تابع ()COUNT کل سفارشات را بازمی‌گرداند.

عبارت CASE قابل جستجو در SQL Server

کد زیر syntax عبارت CASE قابل جستجو را نشان می‌دهد:

CASE  
    WHEN e1 THEN r1
    WHEN e2 THEN r2
    ...
    WHEN en THEN rn
    [ ELSE re ]   
END 

در این syntax:

  • e1, e2, …ei, …en عبارت‌های boolean هستند.
  • r1, r2, …ri, …rn یکی از نتایج احتمالی است.

عبارت CASE قابل جستجو به ارزیابی عبارت boolean در هر دستور WHEN در سفارش مشخص شده می‌پردازد و اگر نتیجه ارزیابی عبارت boolean (یعنی ei) برابر با TRUE باشد، نتیجه (ri) بازگردانده می‌شود.

اگر نتیجه هیچ عبارت boolean برابر با TRUE نباشد، آنگاه عبارت CASE قابل جستجو نتیجه (Re) موجود در در دستور ELSE را بازمی‌گرداند یا اگر دستور ELSE مشخص نشده باشد، مقدار NULL را بازمی‌گرداند.

الف) استفاده از عبارت CASE قابل جستجو در دستور SELECT

جداول sales.orders و sales.order_items از پایگاه داده نمونه را مشاهده کنید:

جداول sales.orders و sales.order_items

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

SELECT    
    o.order_id, 
    SUM(quantity * list_price) order_value,
    CASE
        WHEN SUM(quantity * list_price) <= 500 
            THEN 'Very Low'
        WHEN SUM(quantity * list_price) > 500 AND 
            SUM(quantity * list_price) <= 1000 
            THEN 'Low'
        WHEN SUM(quantity * list_price) > 1000 AND 
            SUM(quantity * list_price) <= 5000 
            THEN 'Medium'
        WHEN SUM(quantity * list_price) > 5000 AND 
            SUM(quantity * list_price) <= 10000 
            THEN 'High'
        WHEN SUM(quantity * list_price) > 10000 
            THEN 'Very High'
    END order_priority
FROM    
    sales.orders o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
WHERE 
    YEAR(order_date) = 2018
GROUP BY 
    o.order_id;

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

نتایج

به این شکل، چگونگی استفاده از عبارت CASE برای افزودن منطق if-else به queryهای SQL را آموختید.

COALESCE در SQL Server

عبارت COALESCE تعداد آرگومان می‌پذیرد، آن‌ها را به ترتیب ارزیابی می‌کند و اولین آرگومان غیر null را بازمی‌گرداند.

کد زیر syntax عبارت COALESCE را نشان می‌دهد:

COALESCE(e1,[e2,...,en])

در این syntax، آرگومان‌های e1, e2, …en عبارات اسکالر هستند که به عنوان مقادیر اسکالر ارزیابی می‌شوند. عبارت COALESCE اولین عبارت غیر null را بازمی‌گرداند. اگر نتیجه ارزیابی همه عبارات NULL شود، آنگاه عبارت COALESCE مقدار NULL را بازمی‌گرداند.

چون COALESCE یک عبارت است، می‌توانید از آن در هر دستوری که عبارت می‌پذیرد (مانند GROUP BY، WHERE، SELECT و HAVING) استفاده کنید.

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

اجازه دهید چند مثال عملی استفاده از عبارت COALESCE را بررسی کنیم:

الف) مثال استفاده از عبارت COALESCE همراه با داده‌هایی از نوع رشته حروف

مثال زیر از عبارت COALESCE برای بازگرداندن رشته ‘Hi’ استفاده می‌کند، چون اولین آرگومان غیر null است:

SELECT 
    COALESCE(NULL, 'Hi', 'Hello', NULL) result;

نتیجه به این شکل است:

result
------
Hi
 
(1 row affected)
ب) مثال استفاده از عبارت COALESCE همراه با داده‌های عددی

این مثال از عبارت COALESCE برای ارزیابی لیستی از آرگومان‌ها استفاده می‌کند و اولین عدد را بازمی‌گرداند:

SELECT 
    COALESCE(NULL, NULL, 100, 200) result;

خروجی به این شکل است:

result
-----------
100
 
(1 row affected)
پ)مثال استفاده از عبارت COALESCE باری جایگزینی مقدار NULL با مقادیر جدید

جدول sales.customers از پایگاه داده نمونه Bikestores را مشاهده کنید:

جدول sales.customers

Query زیر نام، نام خانوادگی، شماره تلفن و ایمیل همه مشتریان را بازمی‌گرداند:

SELECT 
    first_name, 
    last_name, 
    phone, 
    email
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;

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

نتایج

ت) استفاده از عبارت COALESCE برای استفاده از داده‌های موجود

ابتدا، یک جدول جدید به نام Salaries ایجاد کنید که حقوق کارمندان را ذخیره می‌کند:

CREATE TABLE salaries (
    staff_id INT PRIMARY KEY,
    hourly_rate decimal,
    weekly_rate decimal,
    monthly_rate decimal,
    CHECK(
        hourly_rate IS NOT NULL OR 
        weekly_rate IS NOT NULL OR 
        monthly_rate IS NOT NULL)
);

هر کارمند فقط یک نرخ دارد یا ساعتی، یا هفتگی و یا ماهانه.

سپس، چند ردیف به جدول salaries اضافه کنید:

INSERT INTO 
    salaries(
        staff_id, 
        hourly_rate, 
        weekly_rate, 
        monthly_rate
    )
VALUES
    (1,20, NULL,NULL),
    (2,30, NULL,NULL),
    (3,NULL, 1000,NULL),
    (4,NULL, NULL,6000);
    (5,NULL, NULL,6500);

در آخر، داده‌ها را از جدول salaries‌ به دست آورید:

SELECT
    staff_id, 
    hourly_rate, 
    weekly_rate, 
    monthly_rate
FROM
    salaries
ORDER BY
    staff_id;

خروجی به این شکل است:

نتایج

بعد، حقوق هر کارمند را با استفاده از بارت COALESCE به صورت ماهانه محاسبه کنید:

SELECT
    staff_id,
    COALESCE(
        hourly_rate*22*8, 
        weekly_rate*4, 
        monthly_rate
    ) monthly_salary
FROM
    salaries;

تصویر زیر خروجی را نشان می‌دهد:

نتایج

در این مثال، ما از عبارت COALESCE برای فقط استفاده از مقدار غیر NULL یافته شده در ستون‌های weekly_rate، hourly_rate و monthly_rate استفاده کردیم.

عبارت COALESCE در برابر CASE

عبارت COALESCE یک ترکیب بهتر از عبارت CASE است.

عبارات زیر یک نتیجه را بازمی‌گردانند:

COALESCE(e1,e2,e3)
 
CASE
    WHEN e1 IS NOT NULL THEN e1
    WHEN e2 IS NOT NULL THEN e2
    ELSE e3
END

توجه کنید که سیستم بهینه‌ساز query ممکن است از عبارت CASE برای بازنویسی عبارت COALESCE استفاده کند.

در نتیجه، چگونگی استفاده از عبارت COALESCE برای کنترل مقادیر NULL در queryها را آموختید.

NULLIF در SQL Server

عبارت NULLIF دو آرگومان می‌پذیرد و اگر دو آرگومان با هم برابر باشند مقدار NULL را بازمی‌گرداند. در غیر این صورت، اولین عبارت بازگردانده می‌شود.

کد زیر syntax عبارت NULLIF را نشان می‌دهد:

NULLIF(expression1, expression2)

در این syntax، کدهای expression1 و expression2 عبارات اسکالر هستند. یعنی اینکه هر کدام از آن‌ها به عنوان مقدار اسکالر ارزیابی می‌شوند.

توصیه می‌شود که از توابع وابسته به زمان (مانند ()RAND) در تابع NULLIF استفاده نکنید. چون این کار ممکن است باعث شود تابع دو بار ارزیابی شده و نتایج متفاوتی نسبت به فراخوانی دو تابع بازگرداند.

مثال‌های NULLIF در SQL Server

اجازه دهید با استفاده از عبارت NULLIF چند مثال حل کنیم.

استفاده از عبارت NULLIF همراه با داده‌های عددی

این مثال مقدار NULL را بازمی‌گرداند چون اولین آرگومان با دومین آرگومان برابر است:

SELECT 
    NULLIF(10, 10) result;

خروجی به این شکل است:

result
------
NULL
 
(1 row affected)

به هر حال، مثال زیر اولین آرگومان را بازمی‌گرداند، چون دو آرگومان با هم برابر نیستند:

SELECT 
    NULLIF(20, 10) result;

 خروجی به این صورت است:

result
------
20
 
(1 row affected)
استفاده از عبارت NULLIF با داده‌های رشته حروف

مثال زیر از عبارت NULLIF استفاده می‌کند. مقدار NULL بازگردانده می‌شود چون اولین رشته حروف با دومین رشته حروف برابر است:

SELECT 
    NULLIF('Hello', 'Hello') result;

خروجی به این شکل است:

result
------
NULL
 
(1 row affected)

این مثال اولین آرگومان را بازمی‌گرداند چون دو آرگومان مشابه هم نیستند:

SELECT 
    NULLIF('Hello', 'Hi') result;

کد زیر خروجی را نشان می‌دهد:

result
------
Hello
 
(1 row affected)
استفاده از عبارت NULLIF برای ترجمه یک رشته خالی به NULL

زمانی که با داده‌های legacy که شامل ترکیبی از nullها و رشته‌های خالی در یک ستون می‌باشند سر و کار دارید، عبارت NULLIF بسیار مفید واقع خواهد شد. مثال زیر را در نظر بگیرید.

ابتدا، یک جدول جدید به نام Sales.leads برای ذخیره سرنخ یا leadهای فروش ایجاد کنید:

CREATE TABLE sales.leads
(
    lead_id    INT PRIMARY KEY IDENTITY, 
    first_name VARCHAR(100) NOT NULL, 
    last_name  VARCHAR(100) NOT NULL, 
    phone      VARCHAR(20), 
    email      VARCHAR(255) NOT NULL
);

سپس، سه ردیف به جدول sales.leads اضافه کنید:

INSERT INTO sales.leads
(
    first_name, 
    last_name, 
    phone, 
    email
)
VALUES
(
    'John', 
    'Doe', 
    '(408)-987-2345', 
    'john.doe@example.com'
),
(
    'Jane', 
    'Doe', 
    '', 
    'jane.doe@example.com'
),
(
    'David', 
    'Doe', 
    NULL, 
    'david.doe@example.com'
);

در انتها، داده‌ها را از جدول sales.leads به دست آورید:

SELECT 
    lead_id, 
    first_name, 
    last_name, 
    phone, 
    email
FROM 
    sales.leads
ORDER BY
    lead_id;

خروجی به این شکل است:

نتایج

ستون phone یک ستون قابل null شدن است. اگر شماره تلفن یک سرنخ در زمان ثبت داده‌ها مشخص نباشد، ستون phone دارای NULL خواهد بود.

به هر حال، از خروجی، دومین ردیف دارای یک رشته خالی در ستون phone می‌باشد چون در ورود داده اشتباه رخ داده است. توجه کنید که اگر با پایگاه داده‌های legacy کار می‌کنید، ممکن است زیاد با چنین وضعیتی مواجه شوید.

برای یافتن سرنخ‌هایی که شماره تلفن ندارند، از query زیر استفاده کنید:

SELECT    
    lead_id, 
    first_name, 
    last_name, 
    phone, 
    email
FROM    
    sales.leads
WHERE 
    phone IS NULL;

خروجی به این شکل است:

نتایج

خروجی یک ردیف کم دارد که همان ردیفی است که دارای رشته خالی در ستون phone است. برای برطرف کردن این مشکل می‌توانید از عبارت NULLIF استفاده کنید:

SELECT    
    lead_id, 
    first_name, 
    last_name, 
    phone, 
    email
FROM    
    sales.leads
WHERE 
    NULLIF(phone,'') IS NULL;

تصویر زیر خروجی را نشان می‌دهد:

نتایج

عبارت NULLIF و CASE

این عبارت که از NULLIF استفاده می‌کند:

SELECT 
    NULLIF(a,b)

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

CASE 
    WHEN a=b THEN NULL 
    ELSE a 
END

مثال زیر را در نظر بگیرید:

DECLARE @a int = 10, @b int = 20;
SELECT
    NULLIF(@a,@b) AS result;

خروجی به این شکل است:

result
-----------
10
 
(1 row affected)

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

DECLARE @a int = 10, @b int = 20;
SELECT
    CASE
        WHEN @a = @b THEN null
        ELSE 
            @a
    END AS result;

عبارت CASE خیلی طولانی و بلند است، در حالی که عبارت NULLIF خیلی کوتاه‌تر و خواناتر است.

به این شکل، چگونگی استفاده از عبارت NULLIF برای بازگرداندن NUU در صورتی که اولین آرگومان با دومین آرگومان برابر نبود را آموختید.

به این ترتیب با انواع دستورها و روش‌های اصول اولیه SQL Server به صورت مثال محور آشنا شدید. اکنون به سراغ دو نکته مهم و کلیدی خواهیم رفت:

یافتن مقادیر تکراری از یک جدول در SQL Server

از لحاظ فنی، برای اطمینان از منحصر به فرد بودن ردیف‌ها در یک یا چند ستون از جدول، از ویژگی UNIQUE استفاده می‌کنیم. به هر حال، گاهی ممکن است به خاطر طراحی ضعیف پایگاه داده، باگ‌های برنامه یا داده‌های تمیز و مرتب نشده از منابع خارجی، مقادیر تکراری در یک جدول بیابید.

کار شما شناسایی این مقادیر تکراری به روش‌های مؤثر است.

برای یافتن مقادیر تکراری در یک جدول، مراحل زیر را انجام دهید:

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

  • سپس، یک QUERY برای جستجوی مقادیر تکراری بنویسید.

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

اجازه دهید یک جدول نمونه برای حل مثال ایجاد کنیم.

ایجاد یک جدول نمونه

ابتدا، یک جدول جدید به نام t1 که شامل سه ستون a، id و b باشد ایجاد کنید.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    id INT IDENTITY(1, 1), 
    a  INT, 
    b  INT, 
    PRIMARY KEY(id)
);

سپس، چند ردیف به جدول t1 اضافه کنید:

INSERT INTO
    t1(a,b)
VALUES
    (1,1),
    (1,2),
    (1,3),
    (2,1),
    (1,2),
    (1,3),
    (2,1),
    (2,2);

جدول t1 شامل ردیف‌های تکراری زیر است:

(1,2)
(2,1)
(1,3)

هدف شما نوشتن یک query برای یافتن ردیف‌های تکراری بالا است.

استفاده از دستور GROUP BY برای یافتن مقادیر تکراری در یک جدول

این کد از دستور GROUP BY برای یافتن ردیف‌های تکراری در ستون‌های a و b از جدول t1 استفاده می‌کند:

SELECT 
    a, 
    b, 
    COUNT(*) occurrences
FROM t1
GROUP BY
    a, 
    b
HAVING 
    COUNT(*) > 1;

نتایج به این شکل است:

نتایج

نحوه عملکرد کد:

  • اول، دستور GROUP BY ردیف‌ها را بر اساس مقادیر موجود در ستون‌های a و b در چند گروه قرار می‌دهد.

  • دوم، تابع ()COUNT تعداد پیش آمدن هر گروه (a,b) را بازمی‌گرداند.

  • سوم، دستور HAVING فقط گروه‌های تکراری را نگه می‌دارد که گروه‌هایی هستند که بیش از یک بار پیش آمده‌اند.

برای بازگرداندن کل ردیف برای هر ردیف تکراری، باید نتیجه query بالا را با استفاده از CTE به جدول t1 متصل کنید:

WITH cte AS (
    SELECT
        a, 
        b, 
        COUNT(*) occurrences
    FROM t1
    GROUP BY 
        a, 
        b
    HAVING 
        COUNT(*) > 1
)
SELECT 
    t1.id, 
    t1.a, 
    t1.b
FROM t1
    INNER JOIN cte ON 
        cte.a = t1.a AND 
        cte.b = t1.b
ORDER BY 
    t1.a, 
    t1.b;

نتیجه به این شکل است:

نتایج

در کل، query که برای یافتن مقادیر تکراری در یک ستون نوشتیم، از دستور GROUP BY به این شکل استفاده می‌کند:

SELECT 
    col, 
    COUNT(col)
FROM 
    table_name
GROUP BY 
    col
HAVING 
    COUNT(col) > 1;

Query یافتن مقادیر تکراری در چندین ستون از دستور GROUP BY به این شکل استفاده می‌کند:

SELECT 
    col1,col2,...
    COUNT(*)
FROM 
    table_name
GROUP BY 
    col1,col2,...
HAVING 
    COUNT(*) > 1;

استفاده از تابع ()ROW_NUMBER برای یافتن مقادیر تکراری در جدول

کد زیر از تابع ()ROW_NUMBER برای یافتن ردیف‌های تکراری بر اساس ستون‌های a و b استفاده می‌کند:

WITH cte AS (
    SELECT 
        a, 
        b, 
        ROW_NUMBER() OVER (
            PARTITION BY a,b
            ORDER BY a,b) rownum
    FROM 
        t1
) 
SELECT 
  * 
FROM 
    cte 
WHERE 
    rownum > 1;

نتیجه به این صورت است:

نتایج

نحوه عملکرد:

ابتدا، ()ROW_NUMBER ردیف‌های جدول t1 را بر اساس نتایج ستون‌های a و b به چندین بخش تقسیم می‌کند. ردیف‌های تکراری دارای مقادیر تکراری در ستون‌های a و b هستند، اما همان‌طور که در تصویر زیر می‌بینید دارای تعداد ردیف‌های متفاوت می‌باشند:

نتایج

سپس، query بیرونی اولین ردیف در هر گروه را حذف می‌کند.

در کل، این کد از تابع ()ROW_NUMBER برای یافتن مقادیر تکراری در یک ستون از یک جدول استفاده می‌کند:

WITH cte AS (
    SELECT 
        col,
        ROW_NUMBER() OVER (
            PARTITION BY col
            ORDER BY col) row_num
    FROM 
        t1
) 
SELECT * FROM cte 
WHERE row_num > 1;

در نتیجه، چگونگی استفاده از دستور GROUP BY یا تابع ()ROW_NUMBER را برای یافتن مقادیر تکراری در SQL Server آموختید.

حذف مقادیر تکراری از یک جدول در SQL Server

برای حذف ردیف‌های تکراری از جدول در SQL Server، این مراحل را طی کنید:

  • ردیف‌های تکراری را با استفاده از دستور GROUP BY یا تابع ()ROW_NUMBER بیابید.

  • از دستور DELETE برای حذف ردیف‌های تکراری استفاده کنید.

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

ایجاد یک جدول نمونه

ابتدا، یک جدول جدید به نام Sales.contacts ایجاد کنید:

DROP TABLE IF EXISTS sales.contacts;
 
CREATE TABLE sales.contacts(
    contact_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name NVARCHAR(100) NOT NULL,
    last_name NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) NOT NULL,
);

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

INSERT INTO sales.contacts
    (first_name,last_name,email) 
VALUES
    ('Syed','Abbas','syed.abbas@example.com'),
    ('Catherine','Abel','catherine.abel@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Hazem','Abolrous','hazem.abolrous@example.com'),
    ('Hazem','Abolrous','hazem.abolrous@example.com'),
    ('Humberto','Acevedo','humberto.acevedo@example.com'),
    ('Humberto','Acevedo','humberto.acevedo@example.com'),
    ('Pilar','Ackerman','pilar.ackerman@example.com');

در آخر، داده‌ها را از جدول sales.contact به دست آورید:

SELECT 
   contact_id, 
   first_name, 
   last_name, 
   email
FROM 
   sales.contacts;

تصویر زیر خروجی query را نشان می‌دهد:

نتایج

ردیف‌های تکراری زیادی مانند (3,4,5)، (6,7) و (8,9) برای مخاطبانی که دارای نام، نام خانوادگی و ایمیل یکسان هستند وجود دارند.

مثال حذف ردیف‌های تکراری از یک جدول

کد زیر از یک CTE برای حذف ردیف‌های تکراری استفاده می‌کند:

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;

در این کد:

  • ابتدا، CTE از تاب ()ROW_NUMBER برای یافتن ردیف‌های تکراری مشخص شده توسط مقادیر موجود در ستون‌های last_name، firstname و email استفاده می‌کند.

  • سپس، دستور DELETE، تمام ردیف‌های تکراری را حذف می‌کند اما فقط یک عبارت از هر گروه تکراری را نگه می‌دارد.

SQL Server پیام زیر را نشان می‌دهد که بیان می‌کند ردیف‌های تکراری حذف شده‌اند.

(4 rows affected)

اگر داده‌ها را دوباره از جدول Sales.contacts به دست آورید، متوجه خواهید شد که ردیف‌های تکراری حذف شده‌اند.

SELECT contact_id, 
       first_name, 
       last_name, 
       email
FROM sales.contacts
ORDER BY first_name, 
         last_name, 
         email;

نتایج

به این شکل، چگونگی حذف ردیف‌های تکراری از یک جدول در SQL Server را نیز آموختید.

خلاصه

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

در جلسات بعدی به قسمت‌های پیچیده‌تر و جالب‌تری از SQL Server خواهیم پرداخت.

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات