SQL Server 开窗函数 Over()代替游标的使用详解(2)-木庄网络博客

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)

来计算我们需要的值，这个语法说明一下，sum是累加计算，计算应收金额 - 预收金额 - 实收金额（第二行计算出来的结果要加上第一行计算出来的结果，第三行计算出来的结果要加上第二行计算出来的结果，依次类推，所以，其他聚合函数也是这种用法哦），PARTITION BY分组统计客户，并通过Order by指定排序
这个PARTITION BY和Order By结果的用法就很关键了，不然计算就不是预期想要的
再举个例子：比如使用Count() Over() 计算客户的订单号

SELECT DISTINCT FCustId,COUNT(FBillNo) OVER(PARTITION BY FCustId) FBillNum FROM DetailData

总结：

1、游标的使用场景可以很广，但是在数据量大的时候，就会显得很慢，一行一行遍历的速度还是挺久的

2、使用开窗函数来实现一些功能，还是很方便能实现效果，并且它的速度也是很快，值得推荐。

到此这篇关于SQL Server 开窗函数 Over()代替游标的使用的文章就介绍到这了,更多相关SQL Server 开窗函数 Over()内容请搜索

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » SQL Server 开窗函数 Over()代替游标的使用详解

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

数据库系统概念第6版

相关推荐

评论