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)

（编辑：核心网）

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