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

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

--插入单子明细

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 + '小计',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

代码声名：建设了一个姑且表，行使游标遍历我们的DetailData数据表，为了泛起我们最终必要的数据样式，插入客户空行、期初余额、单子信息、客户小计等，逐行计较期末余额值的环境，最终结果如下：

3、行使SUM() Over()的写法

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) --期末余额

)

--插入空行

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

--插入单子明细(要害代码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

--插入小计

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

SELECT -9999,FName + '小计',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

--更新小计的期末余额

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

代码声名：对比第二种，去除了游标的写法，通过了

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

（编辑：湖南网）

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