sqlserver中有几种锁定模式(SQL Server 开窗函数 Over代替游标的使用详解)
sqlserver中有几种锁定模式
SQL Server 开窗函数 Over代替游标的使用详解前言:
今天在优化工作中遇到的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 --获取期初的期末余额 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)
来计算我们需要的值,这个语法说明一下,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()内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
- mysql 性能调优技巧(4 款 MySQL 调优工具,公司大神都在用!)
- centos安装sql server(Centos 7.3下SQL Server安装配置方法图文教程)
- mysql 5.5.27 winx64安装配置方法图文教程(mysql 5.5.27 winx64安装配置方法图文教程)
- laravel提交数据时间(laravel实现查询最后执行的一条sql语句的方法)
- sql 查询结果别名(SQL查询中需要使用别名问题)
- sql join速度慢(SQL Server 使用join all优化 or 查询速度)
- sqlserver 存储过程(SQL Server解析XML数据的方法详解)
- mysql 查询语句group by用法(MySQL group by和order by如何一起使用)
- mysql数据表怎么复制(MySQL 复制表的方法)
- sqlserver 空间数据类型(SQL Server数据类型转换方法)
- phpmysql完全学习手册教程(Windows下搭建PHP开发环境Apache+PHP+MySQL)
- mysql中常用的三种存储引擎的区别(MySQL 存储过程的优缺点分析)
- SQL Server获取时间段内的所有月份
- mysql开启审计日志会导致性能下降(MySQL 一则慢日志监控误报的问题分析与解决)
- mysql双向同步原理(详解MySQL的半同步)
- mysqldata数据如何恢复(mysql5.7.33误删除ibdata文件找回数据的方法)
- 高马尾扎发(高马尾扎发教程视频)
- 这里输入关键词(请手动输入关键词)
- 小说 顾瑾岚拿出一套飞行棋,别说你连飞行棋都不会哦(顾瑾岚拿出一套飞行棋)
- 金品公司 界界乐中秋限定飞行棋礼盒 露营藤篮礼盒全新上市(界界乐中秋限定飞行棋礼盒)
- 必看 8月,相比七夕,更需要注意的是这些事(必看8月相比七夕)
- 8月23日11时16分将迎处暑,逐渐进入气象意义上的秋天(8月23日11时16分将迎处暑)
热门推荐
- python爬微信好友(python抓取需要扫微信登陆页面)
- docker容器启动失败怎么查找原因(解决docker容器启动后马上退出的问题)
- phpredis使用场景(php和redis实现秒杀活动的流程)
- docker容器内服务怎么启动(docker部署confluence的完整步骤)
- vue左右滑动切换(vue实现界面滑动效果)
- docker-compose 开发代码(Docker Compose多容器部署的实现)
- css无效的常见原因
- 织梦dedecms建站详细流程(修改织梦dedecms后台默认admin账号的方法)
- django框架全面讲解(Django uwsgi Nginx 的生产环境部署详解)
- 怎么用python分析足球(使用Python进行体育竞技分析预测球队成绩)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9