عبارتهای مشترک میان جداول

عبارتهای مشترک میان جداول(CTE) یکی از خصوصیات جدید SQL Sevrer 2005 می­باشد. یک CTE در واقع نام یک مجموعه موقت از نتایجی است که می­توان از آن در عبارت FROM دستور Select استفاده کرد. بعد از ایجاد یک CTE می­توان از مجموعه نتایج آن در کلیه دستورات Insert، Update، Select و Delete دیگری که در محدوده مشابه با این CTE ایجاد شده­اند استفاده کرد.

مهمترین مزیتی که CTEها برای برنامه نویس فراهم می­کنند در واقع آسانتر کردن پرس و جو از جداول مشتق شده می­باشد. با استفاده از ساختارهای T_SQL قدیمی برای کار با جدوال مشتق شده میبایست برای اطلاعات بازگشتی از آنها تعریف مجزایی (مانند جداول موقت) صورت گیرد. استفاده از CTE برای تعریف یک جدول موقت، مشاهده ساختار جداول مشتق شده را با استفاده از کدهایی که نوشته می­شود آسانتر می­کند.

یک CTE از سه قسمت اصلی تشکیل شده است:

ü       نام CTE که بعد از کلمه کلیدی WITH قرار میگیرد.

ü       لیست ستونها.

ü       عبارت پرس و جو که در میان پرانتزهای موجود بعد از کلمه کلیدی AS قرار می­گیرد.

 البته در میان این سه بخش استفاده از لیست ستونها اختیاری می­باشد.

ایجاد یک CTE

برای ایجاد یک CTE باید از گرامری شبیه به گرامر زیر استفاده کنید. بعد از وارد کردن کدهای زیر در SQL Server و اجرای آن باید خروجی مشابه با تصویر زير مشاهده کنید.

 

WITH TopSales (SalesPersonID, TerritoryID, NumberOfSales)

AS

(

    SELECT SalesPersonID,TerritoryID, Count(*)

       FROM Sales.SalesOrderHeader

       GROUP BY SalesPersonID, TerritoryID

)

SELECT * FROM TopSales

    WHERE SalesPersonID IS NOT NULL

       ORDER BY NumberOfSales DESC

چگونه کار می­کند

با توجه به اطلاعاتی که قرار است توسط دستور Select بازیابی گردند، باید ستونهای تعریف شده در CTE با ستونهای تعیین شده در این دستور مطابقت داشته باشد. همانطور که مشاهده می­کنید این نکته نیز رعایت شده است:

WITH TopSales (SalesPersonID, TerritoryID, NumberOfSales)

 همان­طور که مشاهده می­نمایید ستونهای تعیین شده در دستور Select با ستونهای CTE مرتبط می­باشند.

SELECT SalesPersonID,TerritoryID, Count(*)

عبارت GROUP BY (برای تشریح عملگر PIVOT حتماً مثال این قسمت را مطالعه کنید)

از عبارت GROUP BY برای دسته بندی اطلاعات بازیابی شده در قالب گروه­هایی استفاده می­شود. با استفاده از GROUP BY، میتوانید در لیست ستون­های دستور Select به ازاء هر گروه از توابع تراکمی برای ایجاد آمارهای مخصوص هر گروه استفاده کنید. استفاده از این عبارت هنگامی مفید خواهد بود که قصد داشته باشید گزارش­هایی از جدول دادهها بر اساس اطلاعات ستون(هایی) ایجاد کنید.

نکته: استفاده از توابع تراکمی مانند count، sum و ... بدون استفاده از عبارت Group BY خطا در بر دارد

استفاده از عبارت GROUP BY

جدول Sales.CreditCard حاوی اطلاعات مربوط به کارتهای اعتباری می­باشد. فرض کنید می­خواهیم که کارتهایی که از یک نوع می­باشند و در یک سال مشخص باطل می­شوند را مشخص کنید.

برای این کار ابتدا یک پنجره جدید برای ایجاد پرس و جو ها مانند قبل باز کنید و سپس دستورات زیر را در آن وارد کنید. با اجرای این دستورات خروجی مشابه با تصویر زير مشاهده خواهید کرد.

Use AdventureWorks

Go

Select CardType, ExpYear,count(CardType) AS 'Total Cards'

from Sales.CreditCard

Where ExpYear in (2006,2007)

group by ExpYear,CardType

order by CardType,ExpYear

 

چگونه کار می­کند

در پرس و جوی بالا سه ستون مشخص شده است. دو ستون اول مستقیماً از جدول Sales.CreditCard بازیابی شده اما ستون سوم تعداد کل کارتهای در ستون CardType از جدول CreditCard می­باشد.

Select CardType, ExpYear,count(CardType) AS 'Total Cards'

from Sales.CreditCard

پس از انتخاب ستون­ها، در قسمت Where شرط مورد نظر آورده شده است و سپس برای دسته بندی و مرتب کردن نمایش اطلاعات از عبارت­های Group By و Oreder By استفاده شده است. شرط استفاده شده در قسمت Where برای بازیابی اطلاعات کارتهایی که در یکی از سالهای 2006 یا 2007 باطل می­شوند می­باشد.

Where ExpYear in (2006,2007)

سپس با استفاده از عبارت Group By اعلام می­کنیم که اطلاعات بازیابی شده باید بر اساس نوع کارتها و سال انقضای آنها باید گروه بندی شوند.

group by ExpYear,CardType

در انتها با استفاده از عبارت Order By نحوه نمایش اطلاعات بر اساس ستونهای ExpYear و CardType به صورت صعودی تنظیم می­شود.

order by CardType,ExpYear

عملگر PIVOT

استفاده از عملگر Pivot زمانی مفید است که قصد داشته باشید از یک جدول اطلاعات را به صورت ضربدری بازیابی کنید. با استفاده از این عملگر میتوان اطلاعات درون ردیف ها را به صورت ستون در نظر گرفت. برای درک بهتر عملکرد این عملگر، مثال قبل را در نظر بگیرید.

در مثال قبل، با توجه به اینکه شرط داده شده برای کارتهای منقضی در سالهای 2006 و 2007 بود اما اطلاعات کارتهای مشابه برای این دو سال به صورت مجزا بازیابی شده و در نتیجه تعداد سطور بازیابی شده زیاد خواهد شد. Pivot با روشی آسانتر خروجی مشابه با خروجی قبل اما در قالبی خلاصه تر و قابل فهم تر ایجاد میکند.

استفاده از عملگر Pivot

همانطور که قبلاً نیز ذکر شد، جدول Sales.CreditCards حاوی اطلاعات مربوط به کارتهای اعتباری مشتریان میباشد. برای درک بهتر عملکرد و بررسی خروجی­ای که این عملگر ایجاد میکند، عملیاتی که در قسمت قبل و با استفاده از عبارت Group By انجام گرفت را دوباره انجام میدهیم.

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

Use AdventureWorks

Go

select CardType ,[2006] as Year2006,[2007] as Year2007

from

(

select CardType,ExpYear

from Sales.CreditCard

)piv Pivot

(

count(ExpYear) for ExpYear in ([2006],[2007])

)as carddetail

order by CardType

چگونه کار می­کند

ابتدا در اولین دستور Select ستونهایی که قرار است در خروجی نشان داده شوند به همراه نام مستعار آنها تعیین شده است.

select CardType ,[2006] as Year2006,[2007] as Year2007 from

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

پس از تعیین ستونهای خروجی، نوبت به بازیابی اطلاعات کلی از جدول Sales.CreditCards میرسد. البته باید یک عملگر Pivot نیز به خروجی انتساب داده شود.

(

select CardType,ExpYear

from Sales.CreditCard

)piv Pivot

حال باید تعداد کارتهایی که در سال مورد نظر قرار دارند را محاسبه کرد. برای این منظور نیز از کدهای زیر استفاده شده است.

(

count(ExpYear) for ExpYear in ([2006],[2007])

)as carddetail

در انتها اطلاعات بدست آمده براساس نامِ نوع کارتها به صورت صعودی مرتب شده است.

order by CardType

 

تابع ()ROW_NUMBER

SQL Server 2005 برای درجه بندی اطلاعات، تابعی به نام ()Row_Number ارائه کرده است. این تابع یک عدد یکتا و ترتیبی برای هر رکورد در مجموعه برگشتی ایجاد کرده و برمیگرداند.

 استفاده از تابع Row_Number()

در دستورات زیر برای انتساب یک عدد یکتا به رکوردهای جدول Sales.SalesPerson از این تابع استفاده شده است

select SalesPersonID, Bonus,

ROW_NUMBER() over (order by SalesPersonID) as RowsCount

from Sales.SalesPerson

چگونه کار می­کند

در دستور Selectی که در بالا از آن استفاده شده است، سه ستون برای نشان دادن در خروجی تعیین شده است. ستون سوم قدری با بقیه تفاوت دارد.

ROW_NUMBER() over (order by SalesPersonID ) as RowsCount

در اینجا با  اعمال تابع ()Row_Number بر روی ستون SalesPersonID و تعیین ترتیب صعودی برای این ستون، در هر رکورد یک ستون جدید با نام RowsCount اضافه میشود که به ترتیب صعودی مقداری دهی شده اند.

 

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

منابع:  

1-  کتاب الکترونیکی Apress.Beginning.C.Sharp.2008.Databases.From.Novice.to.Professional.Jan.2008

2-  استفاده از کتاب برنامه نویسی بانک اطلاعاتی در C# و VB.NET از گروه واژه