SQL Server 开窗函数 Over()代替游标的使用详解
|
副问题[/!--empirenews.page--]
本日在优化事变中碰着的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 2、以往的游标写法 SET NOCOUNT ON --成立姑且表处理赏罚获取数据 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), --预收金额 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --应收金额 FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --实收金额 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) --行使游标 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 --暗示客户已经变了，要插入小计 SET @Count = 0 INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount) SELECT -9999,FName + '小计',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 --获取期初的期末余额 （编辑：湖南网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
