Immerse yourself in the world of .NET and join our live stream for 3 days of free online content June 7 - 9 featuring speakers from the .NET Community and Microsoft product teams.
Learn to develop for web, mobile, desktop, games, services, libraries and more for a variety of platforms and devices all with .NET! We'll have presentations on .NET Core and ASP.NET Core, C#, F#, Roslyn, Visual Studio, Universal Windows Platform (UWP), Xamarin, and much more.
- Lead Function:
LEAD ( scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] order_by_clause )
Create Table TestLead_LAG
(SalesOrderID int not null,
SalesOrderDetailID int not null ,
OrderQty smallint not null);
GO
Insert Into TestLead_LAG
Values (43662,49,1),(43662,50,3),(43662,51,1),
(43663,52,1),(43664,53,1),(43664,54,1),
(43667,77,3),(43667,78,1),(43667,79,1),
(43667,80,1),(43668,81,3),(43669,110,1),
(43670,111,1),(43670,112,2),(43670,113,2),
(43670,114,1),(43671,115,1),(43671,116,2)
مثال:قصد داریم در هر سطر مقدار بعدی فیلد SalesOrderDetailID در فیلد دیگری به نام LeadValue نمایش دهیم، بنابراین Script زیر را ایجاد می‌کنیم:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LEAD(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) LeadValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQtyخروجی بصورت زیر خواهد بود:
مطابق شکل، براحتی واضح است، که در هر سطر مقدار بعدی فیلد SalesOrderDetailID در فیلد LeadValue درج و نمایش داده می‌شود. فقط در سطر 10، چون مقدار بعدی برای فیلد SalesOrderDetailID وجود ندارد، SQL Server مقدار فیلد LeadValue را، Null در نظر می‌گیرد.در این مثال فقط از آرگومان Scalar_expression، استفاده کردیم، و Offset و Default را مقدار دهی ننمودیم، بنابراین SQL Server بطور پیش فرض هیچ سطری را حذف نمی‌کند و مقدار Default را Null در نظر می‌گیرد.مثال دوم: قصد داریم در هر سطر مقدار دو سطر بعدی فیلد SalesOrderDetailID را در فیلد LeadValue نمایش دهیم، و در صورت وجود نداشتن مقدار فیلد SalesOrderDetailID، مقدار پیش فرض صفر ،در فیلد LeadValue قرار دهیم،بنابراین Script آن بصورت زیر خواهد شد:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LEAD(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID) LeadValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQtyخروجی:
در صورت مسئله بیان کرده بودیم، در هر سطر،مقدار فیلد SalesOrderDetailID دو سطر بعدی، را نمایش دهیم، بنابراین مقداری که برای Offset در نظر میگیریم، برابر دو خواهد بود، سپس گفته بودیم، چنانچه در هر سطر مقدار فیلد SalesOrderDetailID وجود نداشت،بجای مقدار پیش فرض Null،از مقدار صفر استفاده شود، بنابراین به Default مقدار صفر را نسبت دادیم.LEAD(SalesOrderDetailID,2,0)در شکل، مطابق صورت مسئله، مقدار فیلد LeadValue سطر اول برابر است با 78،به بیان ساده‌تر برای بدست آوردن مقدار فیلد LaedValue هر سطر، می‌بایست هر سطر را به علاوه 2 (Offset) نماییم، تا سطر بعدی بدست آید، سپس مقدار SalesOrderDetailID را در فیلد LeadValue قرار می‌دهیم.به سطر 9 و 10 توجه نمایید، که مقدار فیلد LeadValue آنها برابر با صفر است، واضح است، سطر 10 + 2 برابر است با 12( 10+2=12 )، چنین سطری در خروجی نداریم، بنابراین بطور پیش فرض مقدار LeadVaule توسط Sql Server برابر Null در نظر گرفته می‌شود، اما نمی‌خواستیم، که این مقدار Null باشد، بنابراین به آرگومان Default مقدار صفر را نسبت دادیم، تا SQL Server ، به جای استفاده از Null، مقدار در نظر گرفته شده صفر را استفاده نماید.اگر چنین فانکشنی وجود نداشت، برای شبیه سازی آن می‌بایست از Join روی خود جدول استفاده می‌نمودیم، و یکسری محاسابت دیگر، که کار را سخت می‌نمود، مثال دوم را با Script زیر میتوان شبیه سازی نمود:WITH cteLead AS ( SELECT SalesOrderID,SalesOrderDetailID,OrderQty, ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS sn FROM TestLead_LAG WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ) SELECT m.SalesOrderID, m.SalesOrderDetailID, m.OrderQty, case when sLead.SalesOrderDetailID is null Then 0 Else sLead.SalesOrderDetailID END as leadvalue FROM cteLead AS m LEFT OUTER JOIN cteLead AS sLead ON sLead.sn = m.sn+2 ORDER BY m.SalesOrderID, m.SalesOrderDetailID, m.OrderQtyجدول موقتی ایجاد نمودیم، که ROW_Number را در آن اضافه کردیم، سپس جدول ایجاد شده را با خود Join کردیم، و گفتیم، که مقدار فیلدLeadValue هر سطر برابر است با مقدار فیلد SalesOrderDetailID دو سطر بعد از آن. و با Case نیز مقدار پیش فرض را صفر در نظر گرفتیم.
- LAG Function:
این فانکشن نیز در SQL Server 2012 ارائه شده است، و امکان دسترسی، به Dataهای سطر قبلی نسبت به سطر جاری را در نتیجه یک پرس و جو (Query)، ارائه می‌دهد. بدون آنکه از Self-join استفاده نمایید،Syntax آن شبیه به فانکشن Lead میباشد و بصورت زیر است:LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )Syntax مربوط به فانکشن LAG را شرح نمی‌دهم، بدلیل آنکه شبیه به فانکشن Lead میباشد، فقط تفاوت آن در Offset است، Offset در فانکشن LAG روی سطرهای ماقبل سطر جاری اعمال می‌گردد.مثال دوم را برای حالت LAG Function شبیه سازی می‌نماییم:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LAG(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID) LAGValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty goخروجی :
همانطور که گفتیم، LAG Function عکس LEAD Function میباشد. یعنی مقدار فیلد LAGValue سطر جاری برابر است با مقدار SalesOrderDetailID دو سطر ما قبل خود.مقدار فیلد LAGValue دو سطر اول و دوم نیز برابر صفر است، چون دو سطر ماقبل آنها وجود ندارد، و مقدار صفر نیز بدلیل این است که Default را برابر صفر در نظر گرفته بودیم.مثال: در این مثال از Laed Function و LAG Function بطور همزمان استفاده می‌کنیم، با این تفاوت، که از گروه بندی نیز استفاده شده است:Script زیر را اجرا نمایید:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, Lead(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) LeadValue, LAG(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) LAGValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty goخروجی:
با بررسی هایی که در مثالهای قبل نمودیم،خروجی زیر را میتوان براحتی تشخیص داد، و توضیح بیشتری نمی‌دهم.موفق باشید.
CREATE TABLE Data
(
id INTEGER NOT NULL PRIMARY KEY IDENTITY,
data VARCHAR(50) NOT NULL
);
INSERT INTO Data
VALUES ('~!hasan @#$%^&*(reza)[ali]^^^^^^^^'),
('(Ja[][][]va~!@#$*d-mohammad)'),
('Mohammad'), ('Maryam');
CREATE TABLE Unwanted
(
id INT NOT NULL PRIMARY KEY IDENTITY,
chars CHAR(1) NOT NULL UNIQUE
);
INSERT Unwanted
VALUES ('~'),('!'),('@'),('#'),('$'),('%'),
('['),(']'),('^'),('&'),('*');
WITH pre AS
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS id, chars
FROM Unwanted
),
cte AS
(
SELECT data.id,
nbr,
CAST(Data AS VARCHAR(50)) AS Data
FROM Data
CROSS JOIN (SELECT COUNT(*) AS nbr FROM Unwanted)t
WHERE EXISTS
(SELECT *
FROM Unwanted
WHERE Data LIKE '%#' + chars + '%' ESCAPE '#')
UNION ALL
SELECT C.id,
nbr - 1,
CAST(REPLACE(C.data, U.chars, '') AS VARCHAR(50))
FROM cte AS C
INNER JOIN pre U
ON C.nbr = U.id
WHERE nbr > 0
)
SELECT data
FROM cte
WHERE nbr = 0
UNION ALL
SELECT data
FROM Data
WHERE NOT EXISTS
(SELECT id
FROM cte
WHERE cte.id = data.id);
Goodbye, Microsoft Lumia. Hello Surface mobile?
SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank] FROM Book b INNER JOIN Author a ON b.AuthorID = a.AuthorID INNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key] LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key] ORDER BY [Rank] DESC
Join us November 8 for the Launch of Visual Studio 2022
