域名預(yù)訂/競(jìng)價(jià)，好“米”不錯(cuò)過(guò)

這篇文章主要介紹了SQL Server 開(kāi)窗函數(shù) Over()代替游標(biāo)的使用,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

前言：

今天在優(yōu)化工作中遇到的sql慢的問(wèn)題，發(fā)現(xiàn)以前用了挺多游標(biāo)來(lái)處理數(shù)據(jù)，這樣就導(dǎo)致在數(shù)據(jù)量多的情況下，需要一行一行去遍歷從而計(jì)算需要的數(shù)據(jù)，這樣處理的結(jié)果就是數(shù)據(jù)慢，容易卡死。

語(yǔ)法介紹：

1、與Row_Number() 函數(shù)結(jié)合使用，對(duì)結(jié)果進(jìn)行排序，這個(gè)是我們使用的非常多的

2、與聚合函數(shù)結(jié)合使用，利用over子句的分組和排序，對(duì)需要的數(shù)據(jù)進(jìn)行操作

例如：SUM() Over() 累加值、AVG() Over() 平均數(shù)

MAX() Over() 最大值、MIN() Over() 最小值

具體介紹：

下面模擬工作中通過(guò)開(kāi)窗函數(shù)代替游標(biāo)的例子，通過(guò)期初余額與單據(jù)的預(yù)收金額、應(yīng)收金額、實(shí)收金額來(lái)計(jì)算截止本單的期末余額，在以往就是通過(guò)游標(biāo)一行一行去遍歷，計(jì)算需要的期末余額，現(xiàn)在使用SUM() Over()來(lái)代替，最終要實(shí)現(xiàn)的效果圖如下：

第一行表示標(biāo)題；第二行表示客戶，是一行空行；第三行是期初余額，只顯示期末余額的數(shù)據(jù)，第四至第六行表示的是每種單據(jù)的余額情況，并逐步匯總當(dāng)前行的期末余額數(shù)據(jù)；最后一行表示的是對(duì)客戶的合計(jì)。

1、構(gòu)建需要用到的表和數(shù)據(jù)（簡(jiǎn)略版）

--客戶表

CREATE TABLE Organization(

FItemID INT NOT NULL PRIMARY KEY IDENTITY(1,1),

FNumber NVARCHAR(255),

FName NVARCHAR(255)

)

--期初數(shù)據(jù)表

CREATE TABLE InitialData(

FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),

FCustId INT NOT NULL,

FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --預(yù)收金額

FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --應(yīng)收金額

FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0) --實(shí)收金額

)

--單據(jù)明細(xì)表

CREATE TABLE DetailData(

FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),

FCustId INT NOT NULL,

FDate DATETIME NOT NULL,

FBillType NVARCHAR(64) NOT NULL,

FBillNo NVARCHAR(64) NOT NULL,

FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --預(yù)收金額

FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --應(yīng)收金額

FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0) --實(shí)收金額

)

INSERT INTO Organization(FNumber,FName) VALUES('001','北京客戶')

INSERT INTO Organization(FNumber,FName) VALUES('002','上?？蛻?)

INSERT INTO Organization(FNumber,FName) VALUES('003','廣州客戶')

INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(1,0,0,0)

INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(2,8000,7245,0)

INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(3,0,1068.21,1068.00)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(1,'2020-06-30','委托結(jié)算','XSD20200700008',0,1221.56,0)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(1,'2020-06-30','委托結(jié)算','XSD20200700009',0,373.46,0)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(1,'2020-06-30','委托結(jié)算退貨','XSD20200700010',0,-427.05,0)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(1,'2020-07-30','銷(xiāo)售商品返利','XSFL20200700005',0,-17.9,0)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(2,'2020-06-25','預(yù)收退款','SKD20200700002',-755,0,0)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(2,'2020-06-20','銷(xiāo)售發(fā)貨','XSD20200700006',0,6169.50,6169.50)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(2,'2020-07-30','銷(xiāo)售總額返利','XSFL20200700002',0,-493.56,-421.85)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(2,'2020-07-31','其他應(yīng)收','QTYS20200900001',0,6000.00,0)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(2,'2020-06-20','預(yù)收沖應(yīng)收','HXD20200700006',-7245.00,0,7245.00)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(3,'2020-06-30','銷(xiāo)售收款','SKD20200700003',0,0,2386.96)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(3,'2020-06-30','應(yīng)收轉(zhuǎn)應(yīng)收','HXD20200700007',0,2386.75,0)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)

VALUES(3,'2020-07-08','銷(xiāo)售退貨','XSD20200700014',0,-46.80,0)

GO

2、以往的游標(biāo)寫(xiě)法

SET NOCOUNT ON

--建立臨時(shí)表處理獲取數(shù)據(jù)

CREATE TABLE #DATA(

FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),

FClassTypeId INT NOT NULL,

FCustId INT NOT NULL,

FNumber NVARCHAR(255),

FName NVARCHAR(255),

FDate DATETIME NULL,

FBillType NVARCHAR(64) NULL,

FBillNo NVARCHAR(64) NULL,

FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --預(yù)收金額

FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --應(yīng)收金額

FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --實(shí)收金額

FBalanceAmount DECIMAL(28,10) NOT NULL DEFAULT(0) --期末余額

)

Declare @Id INT

Declare @CustId INT

Declare @PreAmount decimal(28,10)

Declare @ReceivableAmount decimal(28,10)

Declare @ReceiveAmount decimal(28,10)

Declare @OldCustId int

Declare @Count int

Declare @LastAmount decimal(28,10)

Declare @SumPreAmount decimal(28,10)

Declare @SumReceivableAmount decimal(28,10)

Declare @SumReceiveAmount decimal(28,10)

Declare @SumBalanceAmount decimal(28,10)

--使用游標(biāo)

Declare Data_cursor Cursor

For Select FID,FCustId,FPreAmount,FReceivableAmount,FReceiveAmount

From DetailData

Order By FCustId,FDate,FID

OPEN Data_cursor

FETCH NEXT FROM Data_Cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount

SET @OldCustId = @CustId

SET @Count = 0

SET @LastAmount = 0

SET @SumPreAmount = 0

SET @SumReceivableAmount = 0

SET @SumReceiveAmount = 0

SET @SumBalanceAmount = 0

WHILE @@FETCH_STATUS = 0

BEGIN

IF @Count > 0

BEGIN

IF @OldCustId <> @CustId

BEGIN

--表示客戶已經(jīng)變了，要插入小計(jì)

SET @Count = 0

INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)

SELECT -9999,FName + '小計(jì)',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount

FROM Organization

WHERE FItemID = @OldCustId

Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0

END

END

IF @Count = 0

BEGIN

Set @OldCustId=@CustId

--插入一行空行

INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName)

SELECT -1000,FName,FItemID,FNumber,FName

FROM Organization

WHERE FItemID = @CustId

--獲取期初的期末余額

SELECT @LastAmount=isnull(FReceivableAmount,0) - isnull(FPreAmount,0) - isnull(FReceiveAmount,0),@PreAmount=isnull(FPreAmount,0),@ReceivableAmount=isnull(FReceivableAmount,0),@ReceiveAmount=isnull(FReceiveAmount,0)

FROM InitialData

WHERE FCustId = @CustId

INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount)

VALUES(-1000,'期初余額',@CustId,'','',@LastAmount)

SELECT @Count = 1

SELECT @SumBalanceAmount = @LastAmount

END

--插入單據(jù)明細(xì)

INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)

SELECT 0,d.FCustId,o.FNumber,o.FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,@LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount

FROM DetailData d

INNER JOIN Organization o ON d.FCustId = o.FItemID

WHERE d.FCustId = @CustId AND FID = @Id

SELECT

@LastAmount = @LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount,

@SumPreAmount=@SumPreAmount + FPreAmount,@SumReceivableAmount=@SumReceivableAmount + FReceivableAmount,

@SumReceiveAmount=@SumReceiveAmount + FReceiveAmount

FROM DetailData

WHERE FCustId = @CustId AND FID = @Id

FETCH NEXT FROM Data_cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount

END

IF @Count > 0

BEGIN

INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)

SELECT -9999,FName + '小計(jì)',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount

FROM Organization

WHERE FItemID = @OldCustId

Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0

END

CLOSE Data_cursor

DEALLOCATE Data_cursor

SELECT * FROM #DATA

ORDER BY FCustId,FID

DROP TABLE #DATA

代碼說(shuō)明：創(chuàng)建了一個(gè)臨時(shí)表，使用游標(biāo)遍歷我們的DetailData數(shù)據(jù)表，為了呈現(xiàn)我們最終需要的數(shù)據(jù)樣式，插入客戶空行、期初余額、單據(jù)信息、客戶小計(jì)等，逐行計(jì)算期末余額值的情況，最終效果如下：

3、使用SUM() Over()的寫(xiě)法

SET NOCOUNT ON

--建立臨時(shí)表處理獲取數(shù)據(jù)

CREATE TABLE #DATA(

FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),

FClassTypeId INT NOT NULL,

FCustId INT NOT NULL,

FNumber NVARCHAR(255),

FName NVARCHAR(255),

FDate DATETIME NULL,

FBillType NVARCHAR(64) NULL,

FBillNo NVARCHAR(64) NULL,

FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --預(yù)收金額

FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --應(yīng)收金額

FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --實(shí)收金額

FBalanceAmount DECIMAL(28,10) NOT NULL DEFAULT(0) --期末余額

)

--插入空行

INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName)

SELECT -1000,FName,FItemID,FNumber,FName

FROM Organization o

INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID

--插入期初余額

INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount)

SELECT -1000,'期初余額',FItemID,'','',i.FReceivableAmount - i.FPreAmount -i.FReceiveAmount

FROM Organization o

INNER JOIN InitialData i ON o.FItemID = i.FCustId

INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID

--插入單據(jù)明細(xì)(關(guān)鍵代碼SUM() Over() )

INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)

SELECT 0,d.FCustId,o.FNumber,o.FName,d.FDate,d.FBillType,d.FBillNo,d.FPreAmount,d.FReceivableAmount,d.FReceiveAmount,

SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)

+ i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount

FROM DetailData d WITH(NOLOCK)

INNER JOIN Organization o WITH(NOLOCK) ON o.FItemID = d.FCustId

INNER JOIN InitialData i WITH(NOLOCK) ON o.FItemID = i.FCustId

ORDER BY d.FCustId,d.FDate,d.FID

--插入小計(jì)

INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)

SELECT -9999,FName + '小計(jì)',d.FCustId,FNumber,FName,SUM(FPreAmount),SUM(FReceivableAmount),SUM(FReceiveAmount),0

FROM dbo.DetailData d

INNER JOIN dbo.Organization o ON d.FCustId = o.FItemID

GROUP BY d.FCustId,o.FName,o.FNumber

--更新小計(jì)的期末余額

UPDATE d SET d.FBalanceAmount = d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount + i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount

FROM #DATA d

INNER JOIN InitialData i ON d.FCustId = i.FCustId

WHERE d.FClassTypeId = -9999

SELECT * FROM #DATA

ORDER BY FCustId,FID

DROP TABLE #DATA

代碼說(shuō)明：相比第二種，去除了游標(biāo)的寫(xiě)法，通過(guò)了

SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)

來(lái)計(jì)算我們需要的值，這個(gè)語(yǔ)法說(shuō)明一下，sum是累加計(jì)算，計(jì)算應(yīng)收金額 - 預(yù)收金額 - 實(shí)收金額（第二行計(jì)算出來(lái)的結(jié)果要加上第一行計(jì)算出來(lái)的結(jié)果，第三行計(jì)算出來(lái)的結(jié)果要加上第二行計(jì)算出來(lái)的結(jié)果，依次類(lèi)推，所以，其他聚合函數(shù)也是這種用法哦），PARTITION BY分組統(tǒng)計(jì)客戶，并通過(guò)Order by指定排序

這個(gè)PARTITION BY和Order By結(jié)果的用法就很關(guān)鍵了，不然計(jì)算就不是預(yù)期想要的

再舉個(gè)例子：比如使用Count() Over() 計(jì)算客戶的訂單號(hào)

SELECT DISTINCT FCustId,COUNT(FBillNo) OVER(PARTITION BY FCustId) FBillNum FROM DetailData

總結(jié)：

1、游標(biāo)的使用場(chǎng)景可以很廣，但是在數(shù)據(jù)量大的時(shí)候，就會(huì)顯得很慢，一行一行遍歷的速度還是挺久的

2、使用開(kāi)窗函數(shù)來(lái)實(shí)現(xiàn)一些功能，還是很方便能實(shí)現(xiàn)效果，并且它的速度也是很快，值得推薦。

到此這篇關(guān)于SQL Server 開(kāi)窗函數(shù) Over()代替游標(biāo)的使用的文章就介紹到這了,更多相關(guān)SQL Server 開(kāi)窗函數(shù) Over()內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

來(lái)源：腳本之家

鏈接：https://www.jb51.net/article/197562.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！