前言：

今天在優化工作中遇到的sql慢的問題，發現以前用了挺多游標來處理數據，這樣就導致在數據量多的情況下，需要一行一行去遍歷從而計算需要的數據，這樣處理的結果就是數據慢，容易卡死。

語法介紹：

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

2、與聚合函數結合使用，利用over子句的分組和排序，對需要的數據進行操作

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

具體介紹：

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

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

1、構建需要用到的表和數據（簡略版）

–客戶表
CREATE TABLE Organization(
FItemID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FNumber NVARCHAR(255),
FName NVARCHAR(255)
)

–期初數據表
CREATE TABLE InitialData(
FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FCustId INT NOT NULL,
FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), –預收金額
FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), –應收金額
FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0) –實收金額
)

–單據明細表
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), –預收金額
FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), –應收金額
FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0) –實收金額
)

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′,’委托結算’,’XSD20200700008′,0,1221.56,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,’2020-06-30′,’委托結算’,’XSD20200700009′,0,373.46,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,’2020-06-30′,’委托結算退貨’,’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′,’預收退款’,’SKD20200700002′,-755,0,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,’2020-06-20′,’銷售發貨’,’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′,’其他應收’,’QTYS20200900001′,0,6000.00,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,’2020-06-20′,’預收沖應收’,’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′,’應收轉應收’,’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