SELECT SUM(CASE WHEN DueDate BETWEEN @dAgingDate-
30 AND @dAgingDate
THEN BalanceOwed ELSE 0 END) AS Age30 ,
SUM(CASE WHEN DueDate BETWEEN @dAgingDate-
60 AND @dAgingDate-31
THEN BalanceOwed ELSE 0 END) AS Age60 ,
SUM(CASE WHEN DueDate BETWEEN @dAgingDate-
90 AND @dAgingDate-61
THEN BalanceOwed ELSE 0 END) AS Age90
FROM OpenBalances
با وجود آنکه هر يک از روش های فوق می توانند کار مورد نظر را انجام دهد ،
SQL Server 2005 دستوری به نام PIVOT برای ساده کردن کار ارائه می دهد. به
زبان ساده می توان گفت " PIVOT به برنامه نويسان اجازه می دهد که سطرهای
داده (رکوردها) را به ستون های داده (فيلدها) تبديل کنند.
برای درک بيشتر کارکرد اين دستور بهتر است به
دو نمونه کد که در آن از دستور جديد PIVOT استفاده شده است نگاهی
بياندازيم . در مثال اول می خواهيم Order ها را از پايگاه داده Northwind
Orders دريافت کنيم و گزارشی از سفارش های هر مشتری در سال 1997 بر اساس
فصل بگيريم.
sp_dbcmptlevel Northwind, 90 -- necessary on older databases
USE northwind
-- Create a table variable to hold the results
DECLARE @tQtrPivotedTable TABLE (CustomerID char(25), M_Q1 Money,
M_Q2 Money, M_Q3 Money, M_Q4 Money)
INSERT INTO @tQtrPivotedTable
SELECT * FROM (SELECT CustomerID, DATEPART (q,OrderDate) as
OrderQtr, -- grab the Quarter of the month, using DateParts
(UnitPrice * Quantity) as Amount
FROM orders OH
JOIN [dbo].[Order Details] OD on OH.Orderid = OD.orderid WHERE
Year(OrderDate)=1997) AS TempOrders
PIVOT ( SUM(Amount) FOR OrderQtr In ( [1],[2],[3],[4])) As X -- Pivot
the Sum of the amount, based on the Quarter being 1 of the 4 values
SELECT * FROM @tQtrPivotedTable -- Dump out the results
کد
بالا برای هر تاريخ سفارش ، فصل مربوط به آن را توسط دستور DatePart مشخص
می کند و داده ها را در سطر هايی از يک جدول موقت نگهداری می کند. سپس کد
فوق مجموع مبلغ سفارش ها را PIVOT می کند يعنی سطرهای آن به ستون های داده
آناليز شده تبديل می کند.
PIVOT ( SUM(Amount) FOR OrderQtr In ( [1],[2],[3],[4]))
دستور
فوق مقاديری که ستون OrderQtr آن ها يکی از مقادير فهرست IN باشد (1 ، 2
،3 يا 4) را باهم Aggregate می کند و جمع ستون Amount آن ها را در يکی از
چهار ستون مربوط به آن قرار می دهد.
در اين مثال اگر به فرض بخواهيم گزارش را به جای بر اساس فصل ، بر اساس ماه بگيريم بايد تغييرات زير را در کد اعمال کنيم :
تغيير ورودی DATEPART برای گرفتن ماه به جای فصل (DatePart(m به جای (DatePart(q ، تغيير ستون های PIVOT از 1 تا 4 به 1 تا 12 :
PIVOT (SUM(Amount) FOR OrderMonth In ([1],[2],[3],…,[12]) As X
بايد حتما توجه کنيم که ليست مقادير IN (پس از کلمه کليدی IN) بايد حتما
مشخص و استاتيک باشد. (Hard Code شده باشد) متغيری که روی آن PIVOT می
کنيم (در مثال فوق متغير OrderQtr که PIVOT را برای آن نوشته ايم) بايد
يکی از مقادير ليست IN را داشته باشد.
حال به مثال ديگری توجه کنيد. فرض کنيد جدولی از
صورت حساب مشتريان داريم که حاوی تاريخ صورتحساب ، مبلغ صورحساب و مبلغ
پرداخت شده تا به حال می باشد و می خواهيم گزارشی از طول مدت تاخير وصول
مبلغ صورتحساب بر اساس بازه های زمانی مشخص 1 تا 30 روز ، 31 تا 60 روز و
61 تا 90 روز ، 91 تا 120 روز و از 120 روز به بالا ، بدست آوريم.
DECLARE @tInvoices TABLE (CustomerID char(15), InvoiceNo Char(20), InvoiceDate
DateTime,InvoiceAmount decimal(14,2), ReceivedAmount decimal(14,2))
INSERT INTO @tInvoices VALUES ('Customer 1', 'ABC', '09-01-2005', 1000
0)
INSERT INTO @tInvoices VALUES ('Customer 1', 'DEF', '10-01-2005', 2000
100)
INSERT INTO @tInvoices VALUES ('Customer 1', 'GHI', '11-01-2005', 3000,
3000)
INSERT INTO @tInvoices VALUES ('Customer 1', 'JKL', '12-01-2005', 4000
175)
INSERT INTO @tInvoices VALUES ('Customer 1', 'MNO', '12-18-2005', 4000,
175)
INSERT INTO @tInvoices VALUES ('Customer 2', 'PQR', '05-01-2005', 500,
250)
INSERT INTO @tInvoices VALUES ('Customer 2', 'STU', '08-01-2005', 12000
0)
INSERT INTO @tInvoices VALUES ('Customer 2', 'WYX', '10-01-2005', 7000,
70)
INSERT INTO @tInvoices VALUES ('Customer 2', 'YYZ', '12-01-2005', 3200,
1750)
DECLARE @dAgingDate DATETIME
SET @dAgingDate = CAST('12-1-2005' AS DATETIME)
-- create brackets...could be configurable [1-45 days, etc.]
DECLARE @tAgingBrackets TABLE ( StartDay int, EndDay int, BracketNumber int,
BracketLabel char(20))
INSERT INTO @tAgingBrackets VALUES (0, 30, 1, '< 30 Days')
INSERT INTO @tAgingBrackets VALUES (31, 60, 2, '31-60 Days')
INSERT INTO @tAgingBrackets VALUES (61, 90, 3, '61-90 Days')
INSERT INTO @tAgingBrackets VALUES (91, 120, 4, '91-120 Days' )
INSERT INTO @tAgingBrackets VALUES (121, 999999, 5, '> 120 Days')
-- create our result set
DECLARE @tAgingDetails TABLE (CustomerID char(15), InvoiceNo char(20),
InvoiceDate DateTime,
Bracket1 decimal(14,2), Bracket2 decimal(14,2),
Bracket3 decimal(14,2), Bracket4 decimal(14,2), Bracket5 decimal(14,2))
INSERT INTO @tAgingDetails
SELECT * FROM (select CustomerID,InvoiceNo,invoicedate, InvoiceAmount-
ReceivedAmountAS AmountOwed, TBR.BracketNumber
FROM @tInvoices TI, @tAgingBrackets TBR
WHERE InvoiceAmount-ReceivedAmount <> 0 and
DATEDIFF(dd,invoicedate,@dagingdate) BETWEEN TBR.StartDay and
TBR.EndDay ) as Temp
PIVOT ( SUM(AmountOwed) FOR BracketNumber In ( [1], [2],[3],[4],[5])) As X
SELECT * FROM @tAgingBrackets
SELECT * FROM @tAgingDetails ORDER BY Custom rid, InvoiceNo
2- OUTPUT
آيا تا کنون در SQL Server 2000 خواسته ايد بلافاصله پس از اجرای دستوراتی
مانند INSERT و UPDATE مقدار فيلدی که تحت تاثير دستور قرار گرفته است را
بخوانيد ؟ اين مقدار می تواند مقدار يک فيلد محاسبه شده ، يک شناسه
(Identity) و يا يک Default Value باشد. همچنين آيا تا کنون در دستوراتی
خواسته ايد به مقدار پيش از اجرای دستور و بعد از اجرای دستور دسترسی
داشته باشيد ؟ . برای اين گونه کارها ممکن بود از يک Select و يا استفاده
از جدول های سيستمی INSERTED و DELETED که تنها در Trigger ها قابل دسترسی
است ، استفاده کنيد.
برای مثال
در SQL Server 2000 ، در صورتی که می خواستيم مقدار يک فيلد Identity را
پس از اجرای دستور INSERT داشته باشيم ، اغلب از تابع SCOPE_IDENTITY
استفاده می کرديم :
DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
INSERT INTO @tTestTable VALUES ('steve Goff')
SELECT SCOPE_IDENTITY()
SQL
Server 2005 دستوری را به نام OUTPUT ارائه می کند که به برنامه نويسان در
اين گونه موارد کمک می کند. استفاده از دستور OUTPUT همراه با دستورهای
INSERT و UPDATE می تواند به سادگی داده های Insert شده و Update شده را
برگرداند.
به جای استفاده از SCOPE_IDENTITY می توانيم از OUTPUT استفاده کنيم :
DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
INSERT @tTestTable OUTPUT Inserted.MainPK VALUES ('steve Goff')
و اگر با چند INSERT سروکار داريد و می خواهيد ليستی از مقادير INSERT
شده را داشته باشيد می توانيد خروجی را در يک متغير از نوع Table نگهداری
کنيد
DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
DECLARE @tTemp table (mainpk int)
INSERT @tTestTable OUTPUT Inserted.MainPK into @tTemp VALUES ('Kevin Goff')
INSERT @tTestTable OUTPUT Inserted.MainPK into @tTemp VALUES ('steve Goff'
SELECT * FROM @tTemp
در صورتی که در دستور UPDATE بخواهيم به داده های قبل و بعد از اجرای
دستور دسترسی داشته باشيم می توانيم از جدول های سيستمی INSERTED و
DELETED برای داده های بعد و قبل از اجرای UPDATE استفاده کنيم. در مثال
زير ، نحوه انجام اين کار را مشاهده می کنيم :
DECLARE @tTest TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL ,Amount decimal(10,2))
INSERT INTO @tTest VALUES (100)
INSERT INTO @tTest VALUES (200)
INSERT INTO @tTest VALUES (300)
UPDATE @tTest SET Amount = Amount * 10
OUTPUT DELETED.MainPK, DELETED.Amount AS OldValue, INSERTED.Amount
NewValue
همچنين می توانيم اين خروجی را در متغيری از نوع Table قرار دهيم :
DECLARE @tTemp TABLE (MainPK int, OldValue Decimal(10,2), NewValue Decimal(10,2))
UPDATE @tTest SET Amount = Amount * 10
OUTPUT DELETED.MainPK, DELETED.Amount AS OldValue, INSERTED.Amount AS
NewValue INTO @tTemp
3- TOP N
درN ، SQL Server 2000 در دستـور
SELECT TOP N تنـها می توانسـت يک عـدد ثابـت باشد (مـثلا SELECT TOP 5)
.برنـامه نويسی که می خواست N رکورد اول يک Query را با استفاده از SELECT
TOP N بگيرد به صورتـی که N يک مقدار متغير باشد و در زمـان اجرا
(Runtime) مقدار آن مشـخـص شود ، بايـد از Query های Dynamic (که در زمان
اجرای برنامه ساخـته و اجرا می شوند) و يا عبارت ROWCOUNT استفـاده می
کـرد .
SQL Server 2005 ، در دستور SELECT TOP N با N به صورت يک متغير عددی رفتار می کند. به مثال زير توجه کنيد :
use northwind
DECLARE @nTop int
SET @nTop = 5
Select TOP (@nTop) customerid, oh.orderid, (unitprice * quantity) as amount
from orders OH
join [dbo].[Order Details] OD on oh.orderid = od.orderid
order by Amount Desc
چنانکه در کد بالا می بينيد مقدار N از يک متغير عددی خوانده می شود.
اين امکان را برای TOP N Percent که در آن N بر اساس درصد مشخص می شود و N درصد اول نتيجه Query را بر می گرداند ، نيز خواهيم داشت.
همچنين N می تواند خروجی يک تابع ، يک Stored Procedure و يا يک Query باشد. به مثال زير توجه کنيد :
SELECT TOP( SELECT COUNT(*) FROM SHIPPERS) * FROM ORDERS ORDER BY Freight
DESC
و در آخر بهتر است بدانيد که از دستور TOP N می توانيد در دستورات INSERT و UPDATE استفاده کنيد.
DECLARE @nTop int
SET @nTop = 2
DECLARE @tTest1 TABLE ( Amount decimal(10,2))
INSERT INTO @ttest1 VALUES ( 100)
INSERT INTO @ttest1 VALUES ( 200)
INSERT INTO @ttest1 VALUES ( 300)
UPDATE TOP(@nTop) @tTest1 SET Amount = Amount * 10
DECLARE @tTest2 TABLE ( Amount decimal(10,2))
INSERT TOP(2) @tTest2 SELECT * FROM @tTest1 order by amount
SELECT * FROM @ttest2
در
کد بالا يک متغير از نوع TABLE تعريف شده و تعدادی سطر به آن اضافه شده ،
سپس 2 سطر اول آن UPDATE شده است. متغير ديگری از نوع TABLE تعريف شده است
و 2 سطر اول جدول قبلی در آن INSERT شده است.
در قسمت های بعدی به معرفی ساير امکانات جديد در T-SQL 2005 خواهيم پرداخت.
آموزش تخصصی کامپیوتر
