加入收藏 | 设为首页 | 会员中心 | 我要投稿 核心网 (https://www.hxwgxz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 创业 > 正文

SQL Server 开窗函数 Over()代替游标的使用详解

发布时间:2020-10-26 22:30:29 所属栏目:创业 来源:网络整理
导读:这篇文章主要介绍了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

--获取期初的期末余额

(编辑:核心网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

热点阅读