آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

آموزش مرتب سازی رکوردها-ORDER BY

دوره های مرتبط با این مقاله

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

فهرست رکوردهایی که با دستور SELECT واکشی می کنیم، به همان ترتیبی نمایش داده می شوند که در جدول ظاهر می شود. SQL این اجازه را به شما می دهد که رکوردها را به ترتیب حروف الفبا، به ترتیب زمانی و یا به ترتیب عددی افزایشی مرتب نمایید. پس از انتخاب یک سری ستون برای استخراج، می توان نحوه ی مرتب سازی (ترتیب نمایش) آن ها را از یک فیلد مشخص، تعیین کرد.
1. رایانه ی خود را راه اندازی کرده و وارد سیستم شوید.
2. Microsoft SQL Server را اجرا کرده و دکمه ی Connect را برای اتصال به سرور کلیک کنید.
3. در پنجره ی Object Explorer، بر روی اسم سرور راست کلیک کرده و گزینه ی New Query را انتخاب کنید.
4. در همین پنجره، گره Databases را باز کنید.
5. پایگاه داده ی Department Store1 را از درس پیش به خاطر دارید. داخل Object Explorer، گره DepartmentStore1 و پس از آن Tables را باز کنید.
6. بر روی Inventory.StoreItems راست کلیک کرده و درایه ی Edit Top 200 Rows را انتخاب کنید.
7. در نوار ابزار Query Designer، بر روی دکمه های

آموزش SQL، آموزش SQLو آموزش SQL

کلیک نمایید تا هر یک از پنجره های مربوطه نمایش داده شود.
8. در پنجره ی Diagram، تیک چک باکس تمامی فیلدها را بردارید.
9. در کادر SQL، دستور delete TOP (200) را تایپ کنید.
10. در کادر Diagram، چک باکس های ItemNumber، ItemName و UnitPrice را تیک دار نمایید.
11. در منوی اصلی: Query Designer -> Execute SQL.

مرتب سازی رکوردها به صورت ویژوال

برای فراهم آوردن امکان مرتب سازی رکوردها به ترتیب حروف الفبا یا عددی افزایشی، می بایست به موتور DB اعلان کنیم کدام فیلد به عنوان مرجع (reference) بایستی مورد استفاده قرار گیرد. برای تعریف ترتیب به صورت ویژوال، در Query Editor یا Query Designer:
1. در کادر Diagram، بر روی فیلد Sort Type راست کلیک کرده و یکی از دو گزینه ی Sort Ascending یا Sort Descending را انتخاب نمایید.
داخل کادر Criteria، در زیر سرستون Sort Type، بر روی چک باکس ستون مربوطه کلیک نمایید. با این کار مشخص می شود که کادر مورد نظر یک combo box است. بر روی آیکون هشتک لیست کشویی (combo box) کلیک کرده و گزینه ی مطلوب خود را انتخاب نمایید:


آموزش SQL

آموزش کاربردی: مرتب سازی رکوردها به روش ویژوال

1. داخل کادر Criteria، در زیر سرستون Column، بر روی ItemName کلیک کرده و کلید Tab را چهار بار فشار دهید.
2. حرف a را تایپ کرده و Tabرا بزنید. اطمینان حاصل نمایید در کادر Sort Type، گزینه ی Ascending نمایش داده می شود.


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

در SQL، برای مشخص کردن ترتیب نمایش (نحوه ی مرتب سازی)، عبارت ORDER BY را پس از اسم جدول مورد نظر اضافه نمایید. فرمولی که باید مورد استفاده قرار دهید، به صورت زیر می باشد:


SELECT WhatField(s) FROM WhatObject 
ORDER BY WhatField;

ستونی که به عنوان مبنا مورد استفاده قرار می گیرد، بایستی عضو یا بخشی از ستون های انتخابی باشد. SQL از دو نوع مرتب سازی پشتیبانی می کند که این دو عبارتند از: ascending و descending. اگر رکورد ها را در SQL مرتب سازی نمایید و عبارت کلیدی ORDER BY و به دنبال آن اسم ستون را بکار ببرید، رکوردها به ترتیب صعودی (ascending) نمایش داده می شوند که حالت پیش فرض می باشد. با این حال، برای مشخص کردن ترتیب نمایش (نحوه ی مرتب سازی) به صورت صعودی، واژه ی کلیدی ASC را پس از سام ستون درج کنید. نحوه ی نگارش آن بدین صورت می باشد:


SELECT WhatField(s) FROM WhatObject ORDER BY WhatField ASC;

در Query Designer، برای مرتبی سازی رکوردها به صورت صعودی:
1. داخل پنجره ی Diagram، بر روی فیلد دلخواه راست کلیک کرده و گزینه ی Sort Ascending را (در کادر Sort Type) انتخاب نمایید.
2. در کادر Criteria، پس از انتخاب ستون، بر روی فیلد Sort Type مربوط به آن کلیک کرده و گزینه ی Ascending را انتخاب نمایید.
روش مرتب سازی معکوس صعودی، ترتیب نمایش descending یا نزولی می باشد. در Query Designer، برای مرتب کردن رکوردها به ترتیب نزولی:
1. در کادر Diagram، بر روی فیلد دلخواه راست کلیک کرده و گزینه ی Descending را انتخاب نمایید.
2. در پنجره ی Criteria، پس از مشخص کردن ستون مورد نظر، بر روی کادر Sort Type کلیک نموده و گزینه ی Descending را انتخاب کنید.
اگر ترتیب نمایش رکوردها را می خواهید با کدنویسی تعریف نمایید، در آن صورت لازم است به صورت صریح مشخص کنید که رکورد ها باید به این ترتیب نمایش داده شود. برای این منظور کافی است کلدواژه ی DESC را پس از اسم ستون مورد نظر درج نمایید. نحوی کلی نگارش آن به شرح زیر می باشد:

SELECT WhatField(s) FROM WhatObject ORDER BY WhatField DESC;

همان طور که در بخش های آتی نیز خواهیم دید در زمان مرتب سازی رکوردها، نتیجه ای بازگشتی (نتیجه ای که به عنوان خروجی دریافت می کنید) به نوع داده ای ستون بستگی دارد.


مرتب سازی رکوردها در SQL

1. در کادر SQL، به دستوری که عبارت ORDER BY را دربردارد توجه کنید. در منوی اصلی: Query Designer -> Execute SQL.

آموزش SQL

2. در کادر Criteria، بر روی سرستون Column کلیک کرده تا تمامی فیلدها را انتخاب شوند، سپس گزینه ی Delete را انتخاب نمایید.


مرتب سازی رکوردها براساس نوع داده ای: مرتب سازی فیلدهای NULL

می دانیم که برخی فیلدها دارای مقدار هستند و برخی می توانند مقداری نداشته باشند (NULL باشند). همان طور که قبلا ذکر کردیم، برای مرتب سازی رکوردها، می بایست ستونی که بر اساس آن مرتب سازی را انجام می دهید، مشخص کنید. در صورتی که برخی از رکوردهای آن فیلد NULL باشد، خواهید دید که اول آن رکورد ها انتخاب می شوند و سپس رکوردهای دارای مقدار.


مثال:
SELECT * FROM Registration.Students
ORDER BY LastName;
GO

نتیجه ی زیر را بدست می دهد:
آموزش SQL

از سوی دیگر، اگر رکوردها را به ترتیب نزولی مرتب کنید، رکوردهای دارای مقدار (non-null) اول انتخاب شده و نمایش داده شود.


مرتب سازی فیلدهای حاوی مقدار NULL

1. در کادر Diagram، بر روی تمامی چک باکس های ItemNumber، Manufacturer و UnitPrice کلیک کرده و آن ها را تیک دار نمایید.
2. در پنجره ی Criteria، بر روی کادر Sort Type کلیک نموده و گزینه ی Ascending را انتخاب کنید.
3. در منوی اصلی: Query Designer -> Execute SQL.
در پنجره ی Results و در ستون Manufacturer، می بینید که رکوردهای با مقدار NULL اول از همه نمایش داده می شوند. نوار پیمایش را به سمت پایین کشیده تا فیلدهای non-NULL نمایش داده شوند.


آموزش SQL

مرتب سازی فیلدهای مبتنی بر رشته

اگر رکوردها را بر اساس ستونی که نوع داده آن رشته می باشد (char، varchar، varchar(max)) مرتب سازی نمایید، موتور DB به زبان مورد استفاده ی پایگاه داده مراجعه کرده و آن را بررسی می کند. چنانچه زبان انتخابی از گروه زبان های لاتین باشد، که به صورت پیش فرض زبان انگلیسی آمریکایی می باشد، سطرها به ترتیب حروف الفبا و بر اساس ستون مشخص شده در دستور (پس از عبارت کلیدی ORDER BY) مرتب می شود.
در زیر مثالی را مشاهده می کنید که خروجی آن لیستی از دانشجویان می باشد که بر اساس شماره ی دانشجویی (Student Number) مرتب سازی شده است.


SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName [Last Name], 
       Gender,
       ParentsNames [Parents Names],
       SingleParentHome "SPH?"
FROM   Registration.Students
ORDER BY StudentNumber;
GO

نتیجه ی زیر را برمی گرداند:
آموزش SQL

همان طور که پیش تر گفته شد، در صورتی که ستون مورد نظر دارای مقادیر null باشد، رکوردهای آن پیش از رکوردهای دارای مقدار نمایش داده می شوند. می توان با درج کلیدواژه ی ASC، به طور صریح مشخص کرد که می خواهیم رکوردها به ترتیب صعودی سازمان دهی و نمایش داده شوند.
برای معکوس کردن ترتیب نمایش (از صعودی به نزولی)، کافی است کلیدواژه ی DESC را بلافاصله اسم ستون تایپ کنید.

SELECT FirstName,
       LastName,
       Gender,
       ParentsNames,
       SingleParentHome
FROM   Registration.Students
ORDER BY LastName DESC;
GO

نتیجه ی زیر را برمی گرداند:
آموزش SQL

آموزش کاربردی: مرتب سازی ستون های مبتنی بر رشته

1. در کادر Criteria، بر روی Manufacturer کلیک نمایید، سپس آیکون هشتک لیست کشویی (combo box) را باز کرده و گزینه ی ItemName را انتخاب کنید.
2. داخل پنجره ی Diagram راست کلیک کرده و بعد گزینه ی Execute SQL را انتخاب کنید:


آموزش SQL

فیلدهایی که نوع داده ای آن بولی (Boolean) تعریف شده، مقدارشان از دو حالت 0 (false) و 1 (true) خارج نیست. اگر لیست را بر اساس فیلدی حاوی این نوع مقدار مرتب نمایید، باز هم رکوردهای دربردارنده ی مقدار null در جایگاه اول قرار می گیرند، پس از آن فیلدهای حاوی مقدار 0 و در نهایت فیلدهای با مقدار 1 به نمایش در می آیند.


مثال:
USE Exercise;
GO
CREATE TABLE Videos(Title nvarchar(50), [Length] int,
	Rating nchar(10), [Year] int, WideScreen bit);
GO
INSERT INTO Videos
VALUES(N'Last Castle (The)', 133, N'R', 2001, 1);
GO
INSERT INTO Videos(Title, [Length], [Year])
VALUES(N'Sex, Lies, and Videotape', 99, 1989);
GO
INSERT INTO Videos(Title, [Length], [Year], WideScreen)
VALUES(N'American President (The)', 115, 1995, 0);
GO
INSERT INTO Videos(Title, WideScreen, Rating)
VALUES(N'Day After Tomorrow (The)', 1, N'PG-13');
GO
INSERT INTO Videos(Title, [Length], Rating, WideScreen)
VALUES(N'Sneakers', 126, N'PG-13', 1);
GO
SELECT * FROM Videos
ORDER BY WideScreen;
GO

خروجی:
آموزش SQL

اگر رکوردها را به ترتیب نزولی مرتب نمایید، رکوردهای با مقدار 1 اول لیست شده و بعد سطرهای حاوی مقدار 0 و در پایان رکوردهای null به نمایش در می آیند.


آموزش کاربردی: مرتب سازی فیلدهای بولی

1. بر روی زبانه ی SQLQuery1.sql (تب SQLQuery1.sql را انتخاب کرده) کلیک کرده و دستور را مانند زیر ویرایش نمایید:


USE LambdaSquare1;
GO
SELECT "Unit #" = aparts.UnitNumber,
       Beds = aparts.Bedrooms, 
       Baths = aparts.Bathrooms,
       [Monthly Rent] = aparts.Price, 
       "Primary Deposit" = aparts.Deposit,
       (aparts.Price + aparts.Deposit) "Due Before Moving", 
       aparts.Available
FROM Presentation.Units aparts
ORDER BY aparts.Available;
GO

2. برای اجرای کد، کلید F5 را فشار دهید.


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

همان طور که می دانید، SQL از مقادیر عددی مختلف پشتیبانی می کند. ستون هایی که حاوی مقادیری از این دست هستند را می توان به ترتیب افزایشی سازمانی دهی (مرتب سازی) کرده و نمایش داد. مفسر SQL از قوانین تعریف و مشخص شده در Control Panel تبعیت می کند. به عنوان مثال، در انگلیسی آمریکایی، 0 عدد مبنا (مرجع) می باشد. با توجه به اینکه مقادیر مثبت و منفی داریم، مقادیر منفی قبل از 0 و مقادیر مثبت بعد از 0 قرار می گیرند.
همان طور که در کار با دیگر نوع های داده ای مشاهده شد، اگر رکوردها را بر اساس ستونی حاوی مقدار عددی مرتب نمایید و آن ستون رکوردهایی را شامل می شود که دارای مقدار null هستند، می بینید که آن رکوردها اول لیست می شوند. باقی رکوردها به ترتیب عددی و به صورت افزایشی مرتب سازی می شوند.


مثال:
SELECT Title, Director, Rating, YearReleased "(c) Year"
FROM Videos
ORDER BY YearReleased;
GO

خروجی:
آموزش SQL

البته، برای مرتب سازی رکوردها به ترتیب نزولی، واژه ی کلیدی DESC را بلافاصله پس از اسم ستون درج نمایید.


آموزش کاربردی: مرتب سازی فیلدهای مبتنی بر عدد

1. زبانه ی SQLQuery1.sql را انتخاب کرده و کد زیر را در آن تایپ کنید (پایگاه داده ی LambdaSquare1 را در درس 10 ایجاد کردیم):


 
 USE LambdaSquare1;
GO
SELECT "Unit #" = aparts.UnitNumber,
       Beds = aparts.Bedrooms, 
       Baths = aparts.Bathrooms,
       [Monthly Rent] = aparts.Price, 
       "Primary Deposit" = aparts.Deposit,
       (aparts.Price + aparts.Deposit) "Due Before Moving", 
       aparts.Available
FROM Presentation.Units aparts
ORDER BY aparts.Price;
GO

2. کلید F5 را زده تا دستورات اجرا شوند.
3. دستور را به صورت زیر ویرایش کنید:


USE LambdaSquare1;
GO
SELECT "Unit #" = aparts.UnitNumber,
       Beds = aparts.Bedrooms, 
       Baths = aparts.Bathrooms,
       [Monthly Rent] = aparts.Price, 
       "Primary Deposit" = aparts.Deposit,
       (aparts.Price + aparts.Deposit) "Due Before Moving", 
       aparts.Available
FROM Presentation.Units aparts
ORDER BY "Due Before Moving";
GO

4. کلید F5 را زده تا کد اجرا گردد.
5. حال تب به سراغ تب کناری بروید.
6. در کادر Criteria، بر روی ItemName کلیک کنید. اکنون بر روی کادر Sort Type مربوطه ی آن کلیک کرده و Ascending را با فشردن دکمه ی Delete حذف نمایید.
7. بر روی فیلد Sort Type ستون UnitPrice کلیک نموده و ترتیب نمایش رکوردها را بر روی Ascending تنظیم نمایید. 8. در منوی اصلی: Query Designer -> Execute SQL.


آموزش SQL

9. در پنجره ی Criteria، بر روی سرستون Column کلیک نموده و تمامی ستون ها را انتخاب کنید.
10. حال همه را با انتخاب گزینه ی Delete پاک کنید.
11. در کادر Diagram، بر روی چک باکس های مربوط به ستون های ItemNumber، Manufacturer، ItemName و UnitPrice کلیک نمایید و آن ها را فعال کنید.
12. در پنجره ی Criteria، فیلد ItemNumber را انتخاب کرده و با زدن Tab به کادر مجاور بروید. حال داخل کادر Alias مربوط به آن ستون عبارت Item # را تایپ کنید .
13. همین کار را برای فیلد ItemName تکرار نمایید، ولی این بار عبارت Name/Description را در کادر Alias وارد نمایید.
14. در پنجره ی Criteria، بر روی فیلد UnitPrice کلیک نموده و عبارت Unit Price را داخل کادر Alias آن تایپ کنید.


مرتب سازی بر اساس چندین ستون

جدول زیر را درنظر داشته باشید:


USE Exercise;
GO
CREATE TABLE Employees([Empl #] nchar(10), [First Name] nvarchar(20),
	[Last Name] nvarchar(20), Salary money, [Full Time?] bit);
GO
INSERT INTO Employees
VALUES(N'29730', N'Philippe', N'Addy', 20.05, 1);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'28084', N'Joan', N'Shepherd', 12.72);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'79272', N'Joshua', N'Anderson', 18.26);
GO
INSERT INTO Employees
VALUES(N'22803', N'Gregory', N'Swanson', 15.95, 0);
GO
INSERT INTO Employees([Empl #], [Last Name], Salary, [Full Time?])
VALUES(N'28084', N'Shepherd', 12.72, 1),
      (N'39742', N'Anders', 8.88, 0);
GO
INSERT INTO Employees
VALUES(N'83084', N'Josephine', N'Anderson', 20.02, 1);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'79272', N'James', N'Anders', 18.26),
      (N'27924', N'Gregory', N'Hope', 12.85),
      (N'39742', N'John', N'Anderson', 8.88);
GO
SELECT * FROM Employees;
GO

خروجی:
آموزش SQL

فرض کنید می خواهید لیست را بر اساس میزان حقوق (ستون Salary) مرتب نمایید، برای این منظور دستور را به صورت زیر اجرا کنید:


SELECT [Empl #], [First Name], [Last Name], [Salary],
CASE [Full Time?]
	WHEN 0 THEN N'Contractor'
	WHEN 1 THEN N'Full Time'
	ELSE N'Unspecified'
END AS [Employment Status]
FROM Employees
ORDER BY [Salary];
GO

نتیجه ی زیر را بدست می دهد:


آموزش SQL

همان طور که مشاهده می کنید، برخی رکوردها دارای مقادیر تکراری هستند. در شرایطی که تعداد زیادی از رکوردها دارای مقادیر یکسان هستند، می توان ستون دیگری افزود که بر اساس آن رکوردها مرتب سازی می شوند.
برای تنظیم مرتب سازی بر اساس چندین ستون به صورت ویژوال، در کادر Criteria، بر روی کادر Sort Type مربوطه کلیک نموده و گزینه ی دلخواه را انتخاب نمایید. موتور DB تعداد ستون هایی که فعل مرتب سازی بر اساس آن صورت می گیرد را در کادر Sort Order نمایش می دهد (اگر مرتب سازی بر اساس یک ستون صورت پذیرد، 1 و اگر بر اساس دو ستون صورت پذیرد، عدد 2 را نمایش می دهد).


آموزش SQL

اگر order پیشنهادی دلخواه و مطلوب شما نمی باشد، برای تغییر آن می بایست بر روی کادر Sort Order مربوطه کلیک نموده، سپس لیست کشویی آن را باز کنید و عدد مورد نظر را انتخاب نمایید:

آموزش SQL

پس از انتخاب گزینه ی دلخواه، محیط SQL خود ترتیب نمایش (نحوه ی مرتب سازی) ستون ها را بروز آوری می کند.
برای مرتب سازی لیست بر اساس چندین ستون از طریق کدنویسی، پس از کلیدواژه ی ORDER BY، ستون های مد نظر خود را تایپ کرده و آن ها را به وسیله ی ویرگول از هم جدا کنید.


آموزش کاربردی: مرتب سازی بر اساس چندین ستون

1. بر روی تب SQLQuery1.sql کلیک نمایید و سپس دستور آن را با مجموعه دستور زیر جایگزین کنید:


USE LambdaSquare1;
GO
SELECT "Unit #" = aparts.UnitNumber,
       Beds = aparts.Bedrooms, 
       Baths = aparts.Bathrooms,
       [Monthly Rent] = aparts.Price, 
       "Primary Deposit" = aparts.Deposit,
       (aparts.Price + aparts.Deposit) "Due Before Moving", 
       aparts.Available
FROM Presentation.Units aparts
ORDER BY [Monthly Rent], "Primary Deposit";
GO

2. کلید F5 را زده تا کد اجرا شود.


آموزش SQL

مرتب سازی بر اساس فیلدهای دارای مقدار و فیلدهای تهی (NULL)

اگر مرتب سازی را بر اساس چند رکورد انجام دهید، موتور DB ابتدا ستون primary را نمایش می دهد. سپس در فیلد دوم، زمانی که دو رکورد دارای مقداری یکسان هستند، آن فیلدهایی که هیچ مقداری ندارند و یا به اصطلاح NULL هستند، لیست می شوند


مثال:
SELECT [Empl #], [First Name], [Last Name], [Salary], [Full Time?]
FROM Employees
ORDER BY [Salary], [Full Time?];
GO

آموزش SQL

توجه داشته باشید چنانچه دو رکورد دارای مقدار یکسان باشند و یکی از رکوردها مقدار NULL داشته باشد، اول رکورد با مقدار NULL نمایش داده می شود.1

مرتب سازی بر اساس دو ستون رشته ای (string-based)

فرض کنید دو رکورد رشته ای داریم که مقدارشان یکسان است. به هنگام مرتب سازی به این مسئله برمی خوریم: کدام رکوردها اول نمایش داده می شوند. برای حل این مسئله می توان تعریف فیلدی مجزا را درنظر داشت. بدین معنی که برای مرتب سازی رکوردها فیلدهایی را باهم ترکیب می کنیم.


مثال:
SELECT [Empl #], [First Name], [Last Name], [Salary],
CASE [Full Time?]
	WHEN 0 THEN N'No'
	WHEN 1 THEN N'Yes'
	ELSE N'Don''t Know'
END AS [Employment Status]
FROM Employees
ORDER BY [Last Name], [First Name];
GO

اگر این کار را انجام دهید، مفسر SQL ابتدا رکوردها را بر اساس اولین فیلد مرتب سازی می کند (که طی آن رکوردها گروه بندی می شوند). پس از آن مسئولیت به فیلد دوم محول می شود. به عبارتی دیگر، مقادیر بر اساس حروف الفبا ترتیب داده می شوند. کد بالا نتیجه ی زیر بدست می دهد:


آموزش SQL

همان طور که مشاهده می کنید، زمانی که مرتب سازی را بر اساس بیش یک از فیلد رشته ای انجام می دهید، فیلدهای با مقدار NULL ابتدا می آیند. فیلدهایی که NULL نیستند، رکوردهای مورد نظر بر اساس رکوردهای دوم مرتب می شوند به عنوان نمونه می توان به رکوردهای دارای مقدار Anderson اشاره کرد.


آموزش کاربردی: مرتب سازی بر اساس دو رکورد

1. بر روی تب مجاور کلیک نمایید.
2. در کادر Criteria، بر روی کادر Sort Type کلیک نموده و گزینه ی Ascending را انتخاب نمایید.
3. حال ترتیب نمایش فیلد ItemName را نیز بر روی Ascending تنظیم نمایید.
4. دستور درج شده در کادر SQL را مشاهده نمایید:


SELECT ItemNumber AS [Item #], Manufacturer,
ItemName AS [Name/Description], UnitPrice AS [Unit Price]
FROM   Inventory.StoreItems
ORDER BY Manufacturer, [Name/Description]

5. داخل پنجره راست کلیک کرده و گزینه ی Execute SQL را کلیک نمایید.
همان طور که در تصویر مشاهده می کنید، در ستون Manufacturer، ابتدا رکوردهای با مقدار NULL لیست می شوند. داخل کادر Results با نوار پیمایش پایین رفته تا رکوردهای با مقدار Manufacturer یکسان (رکوردهای با مقدار یکسان) پدیدار شوند. توجه داشته باشید که ستون Name/Description نحوه ی مرتب سازی را مدیریت می کند:


آموزش SQL

مرتب سازی رکوردها بر اساس یک عبارت (expression)

در زمان مرتب سازی رکوردها، موتور DB باید یک مقداری به عنوان مبنا و مرجع داشته باشد که بر اساس آن مقادیر مورد نظر را مرتب سازی کند. بر این اساس، پس از ORDER BY عبارتی را بکار می بریم که ترکیبی از دو ستون باشد (یک عبارت محاسباتی) و مرتب سازی بر اساس آن ها انجام شود.


مثال:
SELECT si.ItemNumber "Item #",
       si.Manufacturer, 
       si.Category, 
       si.SubCategory "Sub-Category", 
       si.ItemName "Item Name", 
       si.UnitPrice "Unit Price",
       FORMAT(si.DiscountRate, N'P') "Discount Rate"
FROM Inventory.StoreItems si
ORDER BY (si.UnitPrice * si.DiscountRate);
GO

نتیجه ی زیر را برمی گرداند:
آموزش SQL

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

همان طور که از expression (عبارت محاسباتی) به عنوان یک مبنا برای مرتب سازی رکوردها استفاده کردیم، اکنون می توانیم از خروجی یک تابع برای مرتب سازی رکوردها استفاده کنیم.


مثال:
SELECT si.ItemNumber "Item #",
       si.Manufacturer, 
	   si.Category, 
	   si.SubCategory "Sub-Category", 
	   si.ItemName "Item Name", 
	   si.UnitPrice "Unit Price",
	   si.DiscountRate "DiscountRate"
FROM Inventory.StoreItems si
ORDER BY FORMAT(si.DiscountRate, N'P');
GO

نتیجه ی زیر را بدست می دهد:
آموزش SQL

می توانید یک تابع تعریف کرده و از (خروجی) آن برای مرتب سازی استفاده کنید. دو نمونه را در زیر مشاهده می کنید:


USE DepartmentStore1;
GO
CREATE FUNCTION Inventory.CalculateDiscountAmount(@UnitPrice money, @DiscountRate decimal(6, 2))
RETURNS money
AS
BEGIN
	RETURN @UnitPrice * @DiscountRate;
END
GO
CREATE FUNCTION Inventory.CalculatePriceAfterDiscount(@UnitPrice money, @DiscountRate decimal(6, 2))
RETURNS money
AS
BEGIN
	RETURN @UnitPrice - (@UnitPrice * @DiscountRate);
END
GO

پس از تعریف تابع، می توانید آن را بلافاصله پس از عبارت کلیدی ORDER BY برای مرتب سازی رکوردها مورد استفاده قرار دهید. مثال زیر یکی از توابع تعریف شده در بالا را بکار می برد:


USE DepartmentStore1;
GO
SELECT si.ItemNumber "Item #",
       si.Manufacturer, 
       si.Category, 
       si.SubCategory "Sub-Category", 
       si.ItemName "Item Name", 
       si.UnitPrice "Unit Price",
       FORMAT(si.DiscountRate, N'P') "Discount Rate",
       FORMAT(Inventory.CalculateDiscountAmount(si.UnitPrice, si.DiscountRate), N'C') "Discount Amount"
FROM Inventory.StoreItems si
ORDER BY Inventory.CalculateDiscountAmount(si.UnitPrice, si.DiscountRate);
GO

مثال زیر تابع دیگر را مورد استفاده قرار می دهد:
USE DepartmentStore1;
GO
SELECT si.ItemNumber "Item #",
       si.Manufacturer, 
	   si.Category, 
	   si.SubCategory "Sub-Category", 
	   si.ItemName "Item Name", 
	   si.UnitPrice "Unit Price",
	   FORMAT(si.DiscountRate, N'P') "Discount Rate",
	   FORMAT(Inventory.CalculateDiscountAmount(si.UnitPrice, si.DiscountRate), N'C') "Discount Amount",
	   FORMAT(Inventory.CalculatePriceAfterDiscount(si.UnitPrice, si.DiscountRate), N'C') "After Discount"
FROM Inventory.StoreItems si
ORDER BY Inventory.CalculatePriceAfterDiscount(si.UnitPrice, si.DiscountRate);
GO

عبارت شرطی WITH TIES

جدول زیر و رکوردهای آن را در نظر بگیرید:


USE VideoCollection1;
GO
DROP TABLE Videos;
GO
CREATE TABLE Videos
(
	[Shelf #] nchar(7) null,
	Title nvarchar(50) not null,
	Director nvarchar(50),
	[Length] int,
	Rating nchar(10),
	[Year] int
);
GO
INSERT INTO Videos
VALUES(N'DHE-927', N'Two for the Money', N'D.J. Caruso', 123, N'R', 2008),
      (N'CGM-683', N'Her Alibi', N'Bruce Beresford', 94, N'PG-13', 1998),
      (N'FQT-973', N'Memoirs of a Geisha', N'Rob Marshall', 145, N'PG-13', 2006),
      (N'DBT-395', N'Wall Street', N'Oliver Stone', 126, N'R', 2000);
      
GO
INSERT INTO Videos(Title, Director, [Length], Rating)
VALUES(N'Stealing Harvard', N'Bruce McCulloch', 85, N'PG-13');
GO
INSERT INTO Videos([Shelf #], Title, Director, [Length], [Year])
VALUES(N'TPH-973', N'A Few Good Men', N'Rob Reiner', 138, 1992);
GO
INSERT INTO Videos(Title, Director, [Year], [Length])
VALUES(N'The Silence of the Lambs', N'Jonathan Demme', 1991, 118);
GO
INSERT INTO Videos([Shelf #], Title, Director, Rating, [Length])
VALUES(N'DZV-737', N'The Lady Killers', N'Joel Coen & Ethan Coen', N'R', 104);
GO
INSERT INTO Videos(Title, Director, [Length],  Rating, [Year])
VALUES(N'Sneakers', N'Phil Alden Robinson', 126, N'PG-13', 1992),
      (N'Annie', N'John Huston', 126, N'G', 1982),
      (N'Dave', N'Ivan Reitman', 110, N'PG-13', 1993);
GO
INSERT INTO Videos
VALUES(N'ADR-737', N'Incredibles (The)', N'Brad Bird', 133, N'PG', 2004);
GO

همان طور که قبلا گفته شد، برای استخراج کلیه ی رکوردهای یک جدول، کافی است عملگر * را پیش از واژه ی FROM درج نمایید و سپس دستور زیر را اجرا کنید:


 SELECT * FROM Videos;

آموزش SQL

ملاحظه می کنید که دستور فوق 12 رکورد را برمی گرداند. برای بازیابی 40% از کل رکوردها، دستور زیر را اجرا نمایید:


SELECT TOP 40 PERCENT *
FROM Videos;
GO

آموزش SQL

همان طور که می بینید 5 رکورد در نتیجه ی اجرای کوئری بازیابی شده که 3 رکورد آن دارای مقدار تکراری PG-13 می باشد. اگر می خواهید لیست را بر اساس ستون Rating مرتب نمایید، در آن صورت می بایست عبارت کلیدی ORDER BY به دستور الحاق کنید:


SELECT TOP 40 PERCENT *
FROM Videos
ORDER BY Rating;
GO

آموزش SQL

همان طور که می بینید، به عنوان خروجی 5 رکورد بازیابی می شود اما این بار فیلد حاوی مقدار PG-13 تنها یکبار نمایش داده شده و همچنین در آخر قرار گرفته است. Transact-SQL عملیاتی را معرفی می کند که دستور ORDER BY و مقدار TOP PERCENT را شامل می شود. این عملیات به صورت زیر کار می کند:
1. ابتدا: top expression percent records.
2. در گام دوم، تمامی رکوردهایی که مقدار آن ستون را استفاده می کنند بر اساس ستونی که بعد از عبارت کلیدی ORDER BY ذکر می شود، مرتب کرده و نمایش دهید. حتی اگر نتیجه رکوردهای بیشتری را نسبت به مقدار تعریف شده در PERCENT برگرداند.
برای اجرای این عملیات به صورت ویژوال، در پنجره Object Explorer، بر روی جدول یا view مورد نظر راست کلیک نموده و گزینه ی Edit Top 200 Rows را انتخاب کنید. یکی از دو پنجره ی Criteria یا SQL را فعال نموده و نمایش دهید.
در پنجره ی Criteria یا SQL، ترتیب دلخواه خود را (نزولی یا صعودی) برای ستون مورد نظر مشخص نمایید.


مثال:
آموزش SQL

در پنجره ی Properties، فیلد Top Specification را باز کرده و اطمینان حاصل نمایید مقدار (Top) بر روی Yes تنظیم شده است. در فیلد Expression، مقدار مد نظرتان را وارد کنید. فیلد Percent را بر روی Yes تنظیم نمایید، سپس به فیلد With Ties مراجعه کرده و مقدارش را به Yes تغییر دهید.


مثال:
آموزش SQL

در گام بعدی، دستور را اجرا می کنیم:


آموزش SQL

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


SELECT TOP ( expression ) [ PERCENT ] [ WITH TIES ]
What Columns
FROM Object
ORDER BY Column

عبارت WITH TIES از دستور SELECT برای اجرای دو عملیاتی که در بالا ملاحظه کردیم، استفاده می کند. عبارت WITH TIES پس از کلیدواژه ی PERCENT درج می گردد.


مثال:
SELECT TOP 40 PERCENT WITH TIES *
FROM Videos
ORDER BY Rating;
GO

نتیجه ی زیر را ارائه می کند:
آموزش SQL

از شرط WITH TIES برای انتخاب تعداد رکوردهایی که در قسمت TOP PERCENT مشخص شده است استفاده می شود. علاوه بر رکوردهای بازیابی شده رکوردهایی که مقدار آن ها در ستون مشخص شده در قسمت OREDER BY برابر با رکورد آخر باشد نیز بازیابی می شوند.


آموزش کاربردی

1. در پنجره ی Criteria، واژه ی Ascending را حذف کنید.
2. در کادر Diagram، بر روی چک باکس های ستون Categories و SubCategories کلیک کرده و آن ها را تیک دار نمایید.
3. داخل کادر Criteria، ستون ها را طوری مرتب کنید که با ترتیب مشخص شده در کادر Diagram یکی باشد.
4. نام مستعار فیلد SubCategories را Sub-Categories انتخاب نمایید.
5. بر روی کادر Sort Type کلیک نموده و گزینه ی Ascending را انتخاب نمایید.
6. اگر پنجره ی Properties نمایان نمی باشد، برای نمایش دادن آن در منوی اصلی: View -> Properties window.
7. در پنجره ی Properties، گره فیلد Top Specification را باز کرده و بعد دوبار بر روی فیلد TOP کلیک نمایید تا مقدارش بر روی Yes تنظیم شود
8. بر روی فیلد Expression کلیک نموده و مقدار 20 را وارد نمایید.


آموزش SQL

9. در منوی اصلی: Query Designer -> Execute SQL.
به تعداد رکوردهایی که برگردانده می شود دقت کنید.


آموزش SQL

10. در پنجره ی Properties، بر روی گزینه ی With Ties دوبار کلیک کرده و مقدار آن را به Yesتغییر دهید.
11. در منوی اصلی: Query Designer -> Execute SQL. حال تعداد رکوردهای واکشی شده را مشاهده نمایید.


آموزش SQL

نادیده گرفتن تعدادی از رکوردها

زمانی که شما تصمیم به مرتب سازی رکوردهای جدول می گیرید، database engine تمامی رکوردها را از اول تا آخر مرتب می کند. می توان مرتب سازی را گونه ای تنظیم کرد که از یک رکورد مشخص به بعد سطرها سازمان دهی شوند. برای مثال 14 رکورد اول نادیده گرفته شده و از آن به بعد مرتب سازی آغاز شود. برای این منظور فرمول زیر باید مورد استفاده قرار گیرد:


SELECT All options and everything we have seen so far
What Columns
FROM Object
ORDER BY Column
OFFSET Number ROW | ROWS

آخرین خط فرمول، قابلیت جدیدی است که به آن می پردازیم: فرمول با کلیدواژه ی OFFSET شروع شده و در روبروی آن عدد صحیح ثابت بزرگتر 1 درج می کنیم. توجه داشته باشید که دستور بایستی با یکی از دو واژه ی کلیدی ROW یا ROWS را پایان یابد.


آموزش کاربردی: نادیده گرفتن تعدادی از رکوردها

1. بر روی تب SqLQuery1 کلیک کرده و کد زیر را وارد کنید:
USE LambdaSquare1;
GO
SELECT aparts.UnitNumber "Unit #",
       aparts.Bedrooms   Beds, 
       aparts.Bathrooms  Baths,
       aparts.Price      [Monthly Rent], 
       aparts.Deposit    "Primary Deposit",
       (aparts.Price + aparts.Deposit) "Due Before Moving", 
       aparts.Available
FROM Presentation.Units aparts
ORDER BY [Monthly Rent]
OFFSET 12 ROWS;
GO

2. برای اجرا، داخل Query Editorراست کلیک نموده و گزینه ی Execute را انتخاب کنید.
آموزش SQL

مرتب سازی اولین یا زیرمجموعه ای (sub-set) از رکوردها

پس از نادیده گرفتن و حذف تعدادی از رکوردها، می توان از موتور DB خواست که تنها تعداد مشخصی از رکوردهای اول را مرتب سازی کند یا sorting را از یک سطر مشخص به بعد (پس از آن تعداد مشخص حذف شده) انجام دهد. برای این منظور، کافی است فرمول زیر را بکار ببرید:


SELECT All options and everything we have seen so far
What Columns
FROM Object
ORDER BY Column
OFFSET Number ROW | ROWS
FETCH FIRST | NEXT Number ROW | ROWS

در این فرمول پس از خط کد حاوی کلیدواژه ی OFFSET، عبارت کلیدی FETCH FIRST یا FETCH NEXT را تایپ کنید. در پایان فرمول نیز یک عدد صحیح ثابت و سپس کلمه ی رزرو شده ی ROW یا ROWS را درج کنید.


آموزش کاربردی: مرتب سازی اولین مجموعه از رکوردها

1. در Query Editor، دستور مورد نظر را به صورت زیر ویرایش کنید:
USE LambdaSquare1;
GO
SELECT aparts.UnitNumber "Unit #",
       aparts.Bedrooms   Beds, 
       aparts.Bathrooms  Baths,
       aparts.Price      [Monthly Rent], 
       aparts.Deposit    "Primary Deposit",
       (aparts.Price + aparts.Deposit) "Due Before Moving", 
       aparts.Available
FROM Presentation.Units aparts
ORDER BY [Monthly Rent]
OFFSET 12 ROWS
FETCH FIRST 10 ROWS ONLY;
GO

2. داخل Query Editor راست کلیک کرده و گزینه ی Execute را کلیک کنید.
آموزش SQL
  • 5178
  •    1236
  • تاریخ ارسال :   1394/11/25

دانلود PDF دانشجویان گرامی اگر این مطلب برای شما مفید بود لطفا ما را در GooglePlus محبوب کنید
رمز عبور: tahlildadeh.com یا www.tahlildadeh.com
ارسال دیدگاه نظرات کاربران
شماره موبایل دیدگاه
عنوان پست الکترونیک

ارسال

آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

تمامی حقوق این سایت متعلق به آموزشگاه تحلیل داده می باشد .