前言：

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

語法介紹：

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

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

例如：SUM() Over() 累加值、AVG() Over() 平均數(shù)
MAX() Over() 最大值、MIN() Over() 最小值

具體介紹：

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

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

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

–客戶表
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′,’銷售商品返利’,’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′,’銷售發(fā)貨’,’XSD20200700006′,0,6169.50,6169.50)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,’2020-07-30′,’銷售總額返利’,’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′,’銷售收款’,’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′,’銷售退貨’,’XSD20200700014′,0,-46.80,0)
GO