金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 总结复习,万能分页存储过程的详解_MsSql_脚本之

总结复习,万能分页存储过程的详解_MsSql_脚本之

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-23 02:25

有人提出游标不好,会锁定行,幸亏我锁定的是临时表,不是数据表,不影响数据表的写操作。 下面是14楼的回复,让我茅塞顿开,于是有了今天的改进版,取消了游标的使用,临时表还是存在的,谢谢。 其实你只要分成两次查询即可: 1, 还是用Row_Number查主表分页 2, Row_Number查主表分页 inner join 明细表..用不到游标的. 复制代码 代码如下: --不用游标的分页 --先将分页的主表放在临时表中,然后用临时表和子表联合查询,来获取子表信息 --既保证了分页的正确性,也包括了子表信息 CREATE TABLE #order ( number BIGINT, orderseqno VARCHAR insert into #order SELECT * FROM OVER (ORDER BY oi.CreateDate DESC) AS rownumber,oi.OrderSeqNO FROM OrderInfo oi WHERE oi.OrderSeqNO LIKE '%2%' ) AS o WHERE o.rownumber BETWEEN 10 AND 20 SELECT * FROM #order INNER JOIN OrderDetail od ON od.OrderSeqNO=#order.orderseqno DROP TABLE #order 复制代码 代码如下: --SELECT TOP 10 oi.OrderSeqNO, oi.GoodsName ,ci.CompanyName,od.* --FROM OrderInfo oi INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID --LEFT JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO --使用row_unmber()实现分页 --本来我们想要的结果是10条订单,结果却不是10条订单,而是10条明细 --其实是针对的子表进行分页了,订单并不是要显示的个数,出来的个数是明细的个数 --就是因为主表和子表联合查询的结果,主表记录和子表记录是1:N的关系,一个主表记录有多个明细 --建立聚集索引 -- CLUSTERED INDEX INDEX_OrderInfo ON OrderInfo --显示查询执行计划 --SET STATISTICS IO ON select * from OVER (ORDER BY oi.createdate DESC) AS rownumber,oi.orderseqno ,od.OrderDetailID FROM OrderInfo oi LEFT JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO WHERE oi.OrderSeqNO LIKE '%2%' ) AS o WHERE rownumber BETWEEN 10 AND 20 --不用游标的分页 --先将分页的主表放在临时表中,然后用临时表和子表联合查询,来获取子表信息 --既保证了分页的正确性,也包括了子表信息 CREATE TABLE #order ( number BIGINT, orderseqno VARCHAR insert into #order SELECT * FROM OVER (ORDER BY oi.CreateDate DESC) AS rownumber,oi.OrderSeqNO FROM OrderInfo oi WHERE oi.OrderSeqNO LIKE '%2%' ) AS o WHERE o.rownumber BETWEEN 10 AND 20 SELECT * FROM #order INNER JOIN OrderDetail od ON od.OrderSeqNO=#order.orderseqno DROP TABLE #order --解决上面的问题,有以下几种办法 --1、先根据条件查询主表记录,然后在C#代码中循环,再次到数据库查询每条主表记录的明细信息,然后赋值给属性 --2、在数据库的存储过程中使用游标,也是先查询主表记录,然后使用游标循环的过程中,查询子表信息,然后在C#中 --集中处理 --很显然,后一种减少了数据库的往来开销,一次获取了想要的数据,个人认为要比第一种好,欢迎大家一起讨论更好的办法 --需要注意的就是ROW_NUMBER()返回的类型是bigint,而不是int --下面是游标的存储过程 --建立主表临时表 CREATE TABLE #temp ( rownumber bigint, orderseqno VARCHAR, goodsname VARCHAR, companyname VARCHAR --建立子表临时表 CREATE TABLE #detail ( orderseqno VARCHAR, detailid UNIQUEIDENTIFIER, unitprice DECIMAL --插入主表数据到主表临时表 insert into #temp SELECT * --oo.rownumber, oo.OrderSeqNO, oo.GoodsName, oo.CompanyName FROM OVER (ORDER BY oi.createdate DESC) AS rownumber, oi.OrderSeqNO, oi.GoodsName ,ci.CompanyName FROM OrderInfo oi INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID WHERE oi.CreateDate

在我的使用SQL Server2005的新函数构造分页存储过程中,我提到了使用ROW_NUMBER()函数来代替top实现分页存储过程。 但是时间长了,又发现了新问题,就是主子表的分页查询。例如:订单表和订单明细表,要求是查询订单,第二页,每页10条 复制代码 代码如下: --使用row_unmber()实现分页 --本来我们想要的结果是10条订单,结果却不是10条订单,而是10条明细 --其实是针对的子表进行分页了,订单并不是要显示的个数,出来的个数是明细的个数 --就是因为主表和子表联合查询的结果,主表记录和子表记录是1:N的关系,一个主表记录有多个明细 select * from OVER (ORDER BY oi.createdate DESC) AS rownumber,oi.orderseqno ,od.OrderDetailID FROM OrderInfo oi LEFT JOIN OrderDetail od ON oi.OrderSeqNO=od.OrderSeqNO WHERE oi.OrderSeqNO LIKE '%2%' ) AS o WHERE rownumber BETWEEN 10 AND 20 结果如下图

–建立主表临时表CREATE TABLE #temp(rownumber bigint,orderseqno VARCHAR,goodsname VARCHAR,companyname VARCHAR–建立子表临 时表CREATE TABLE #detail(orderseqno VARCHAR,detailid UNIQUEIDENTIFIER,unitprice DECIMAL–插入主表数据到主表临时表insert into #tempSELECT oo.rownumber, oo.OrderSeqNO, oo.GoodsName, oo.CompanyName FROM OVER (ORDER BY oi.createdate DESC) AS rownumber,oi.OrderSeqNO, oi.GoodsName ,ci.CompanyNameFROM OrderInfo oi INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyIDWHERE oi.CreateDate

1. 排名函数与PARTITION BY 复制代码 代码如下: --所有数据 SELECT * FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid WHERE scorename = '语文' ------------------------------------------- --ROW_NUMBER() 的使用 生成列从1开始依次增加 ------------------------------------------- SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid WHERE scorename = '语文' --也可以在后面再加一个order by,则表示前面生成后的全部列又被以最后的列重新排列 SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid WHERE scorename = '语文' ORDER BY a.Id --要在分组统计后使用排名函数,则先进行分组,用cte或嵌套查询表整出结果集,再用row_number函数处理 WITH b AS ( SELECT stuid, SUM AS score FROM ScoreTB GROUP BY stuid ) SELECT * ,ROW_NUMBER() OVER (ORDER BY b.score desc) AS rownumer FROM

很明显不是10条订单,而是10条明细。

–定义游标金沙棋牌app手机下载,DECLARE @temp_cursor CURSOR–给游标赋值SET @temp_cursor=CURSOR FOR SELECT #temp.orderseqno,#temp.goodsname FROM #temp

dbo.student AS a INNER JOIN b ON a.id = b.stuid

--RANK() 用法与ROW_NUMER函数想同,只是在出现order by同级时,排名会设置成一样,而下一个会根据之前的记录数生成序号

下面是对查询的一点改进,使用了游标复制代码 代码如下: --解决上面的问题,有以下几种办法 --1、先根据条件查询主表记录,然后在C#代码中循环,再次到数据库查询每条主表记录的明细信息,然后赋值给属性 --2、在数据库的存储过程中使用游标,也是先查询主表记录,然后使用游标循环的过程中,查询子表信息,然后在C#中 --集中处理 --很显然,后一种减少了数据库的往来开销,一次获取了想要的数据,个人认为要比第一种好,欢迎大家一起讨论更好的办法 --需要注意的就是ROW_NUMBER()返回的类型是bigint,而不是int --下面是游标的存储过程 --建立主表临时表 CREATE TABLE #temp ( rownumber bigint, orderseqno VARCHAR, goodsname VARCHAR, companyname VARCHAR --建立子表临时表 CREATE TABLE #detail ( orderseqno VARCHAR, detailid UNIQUEIDENTIFIER, unitprice DECIMAL --插入主表数据到主表临时表 insert into #temp SELECT oo.rownumber, oo.OrderSeqNO, oo.GoodsName, oo.CompanyName FROM OVER (ORDER BY oi.createdate DESC) AS rownumber, oi.OrderSeqNO, oi.GoodsName ,ci.CompanyName FROM OrderInfo oi INNER JOIN CompanyInfo ci ON oi.CompanyID=ci.CompanyID WHERE oi.CreateDate

–定义游标循环过程中所需保存的临时数据DECLARE @orderseqno VARCHAR,@goodsname varchar

--例如前面三个是一样的,那么都是1,下一个则是4,示例略


--DENSE_RANK() 密集排名 用法与ROW_NUMER、RANK函数相同,只是在生成序号时是连续的,而rank函数生成的序号有可能不连续

上面的T-SQL只在SQL Server 2005上调试成功。推荐一篇MS SQL Server的查询计划的相关内容,可以利用它优化SQL,写的不错。引用:SqlServer 执行计划及Sql查询优化初探

–打开游标OPEN @temp_cursor

--例如前面三个是一样的,那么都是1,下一个则是2,示例略


--ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。 --为每条记录生成的序号就是这条记录所有的数组元素的索引。也可以将每一个分配记录的数组元素称为“桶”。

FETCH NEXT FROM @temp_cursor INTO @orderseqno,@goodsname–循环游标,查询子表数据,然后插入子表临时表WHILE @@FETCH_STATUS=0BEGININSERT INTO #detailSELECT od.OrderSeqNO,od.OrderDetailID, od.UnitPrice,od.QtyFROM OrderDetail odWHERE od.OrderSeqNO=@orderseqno

--它有一个参数,用来指定桶数,例如

SELECT ntile OVER (ORDER BY B.SCORE DESC) AS GROUPID ,A.NAME, ISNULL SCORE, a.Id FROM dbo.student AS a LEFT JOIN dbo.ScoreTB AS b ON a.Id =

FETCH NEXT FROM @temp_cursor INTO @orderseqno,@goodsnameEND

b.stuid AND scorename = '语文'

--PARTITION BY 类似于向排名函数应用一个group

–关闭游标CLOSE @temp_cursorDEALLOCATE @temp_cursor

by,分组后对每一个组单独排名

--统计各个学科的排名依次为: SELECT RANK() OVER (PARTITION BY b.scorename ORDER BY B.SCORE DESC) AS ROWNUMBER,b.scorename, b.score, A.NAME, a.Id FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid ORDER BY SCORENAME 2. TOP 新用法 复制代码 代码如下: DECLARE @num INT = 101 SELECT TOP * FROM Student ORDER BY Id --必须用括号括起来 SELECT TOP percent * FROM Student ORDER BY Id --只接受float并且1-100之间的数,如果传入其他则会报错 3. group by all 字段 / group by 字段 前者有点像left join ,right join的感觉,两者的主要区别体现在有where条件被过滤的聚合函数,会重新抓取出来放入查询的数据表中,只是聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。 当然从效率上来说,后者优于前者,就像inner join 优于left join一样 4. count 与 count 如果查询出来的字段中没有NULL值,那么俩种查询条件无任何区别,如果有NULL,后者统计出来的记录则是 总记录数 - NULL记录数 从性能上来说,前者高于后者,因为后者会逐行扫描字段中是否有NULL值,有NULL则不加以统计,减少了逻辑读的开销,从而性能达到提升 5. top n With ties 的用法 详见 举个例子 select top 1 with ties * from student order by score desc 等价于 select * from student where score=(select top 1 score from student order by score desc) 6. Apply运算符 复制代码 代码如下: View Code --准备数据 CREATE TABLE [dbo].[Student]( [Id] [int] NULL, [Name] [varchar] go INSERT INTO dbo.Student VALUES INSERT INTO dbo.Student VALUES INSERT INTO dbo.Student VALUES INSERT INTO dbo.Student VALUES go CREATE TABLE [dbo].[scoretb]( [stuId] [int] NULL, [scorename] [varchar] NULL, [score] INT NULL ) go INSERT INTO [scoretb] VALUES INSERT INTO [scoretb] VALUES INSERT INTO [scoretb] VALUES INSERT INTO [scoretb] VALUES INSERT INTO [scoretb] VALUES INSERT INTO [scoretb] VALUES INSERT INTO [scoretb] VALUES INSERT INTO [scoretb] VALUES INSERT INTO [scoretb] VALUES --创建表值函数 CREATE FUNCTION [dbo].[fGetScore] RETURNS @score TABLE ( [stuId] [int] NULL, [scorename] [varchar] NULL, [score] INT NULL ) as BEGIN INSERT INTO @score SELECT stuid,scorename,score FROM dbo.scoretb WHERE stuId = @stuid RETURN; END GO --开始使用 SELECT A.id,A.name,B.scorename,B.score FROM [Student] A CROSS APPLY [dbo].[fGetScore] B --相当于inner join效果 SELECT A.id,A.name,B.scorename,B.score FROM [Student] A OUTER APPLY [dbo].[fGetScore] B --相当于left join效果 --而不能这样使用 --SELECT A.id,A.name,B.scorename,B.score FROM [Student] A -- INNER JOIN [dbo].[fGetScore] B ON A.Id = B.stuid -- SELECT A.id,A.name,B.scorename,B.score FROM [Student] A -- INNER JOIN (SELECT * FROM [dbo].[fGetScore] B ON A.Id = B.stuid 7. INTERSECT和EXCEPT运算符 EXCEPT 只包含excpet关键字左边而且右边的结果集中不存在的那些行 INTERSECT 只包含两个结果集中都存在的那些行 往往EXISTS关键字可以代替上面的关键字,并且从性能中可以看到比他们更好,但EXCEPT/INTERSECT更便于阅读和直观。还是建议从性能更优入手。 8. 索引提高查询效率的原理 索引与EXISTS运算符在处理方式上很像,它们都可以在找到匹配值后立即退出查询运行,从而提高了查询性能 9. 表变量与临时表 主要区别: 1表变量不写日志,没有统计信息,频繁更改不会造成存储过程重新编译,不能建索引和统计信息,但是可以建立主键,变通实现索引查找,表变量不只是在内存中操作,数据量大的情况也会写tempdb,即物理磁盘的IO操作。 2.事务回滚对表变量无效 一般来说,数据量大,临时结果集需要和其他表二次关联用临时表 数据量小,单独操作临时结果集用表变量 10. 脚本和批处理 Go不是一条T-SQL命令,他只能被编译工具Management Studio, SQLCMD识别,如果用第三方工具,不一定支持GO命令。例如ADO.NET,ADO。 11. SQLCMD的运用 SQLCMD -Usa -Psa -Q "SELECT * FROM TESTDB.dbo.mytable" SQLCMD -Usa -Psa -i testsql.sql 运行文件里的SQL语句 12. EXEC 使用说明 在执行过EXEC之后,可以使用类似@@ROWCOUNT这样的变量查看影响行数;不能在EXEC的参数中,针对EXEC字符串运行函数,例如cast,对于EXEC的参数,只能用字符串相加,或者是整体的字符串。 13. WAITFOR 的含义 WAITFOR TIME 定时执行; WAITFOR DELAY 延迟执行 14. 存储过程 总结 1)用TRY/CATCH 替代 @@ERROR这种更科学,其一@@ERROR没有TRA/CATCH直观,其二遇到错误级别在11-19的错误,错误会使运行直接中断,导致@@ERROR判断错误与否无效。 2)使用RAISERROR 抛错 WITH LOG,当严重级别大于等于19时,需要使用这个选项 WITH SETERROR,使其重写@@ERROR值,方便外部调用 WITH NOWAIT 立刻将错误通知给客户端 15. 游标的复习 游标主要部分包括:1)声明 2)打开 3)使用或导航 4)关闭 5)释放 嵌套使用游标示例 复制代码 代码如下: DECLARE BillMsgCursor CURSOR FOR SELECT TypeNo,TabDetailName FROM dbo.BillType OPEN BillMsgCursor DECLARE @TypeNo CHAR DECLARE @DetailName VARCHAR FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @DataFieldName VARCHAR DECLARE ColumnName CURSOR FOR SELECT name FROM syscolumns WHERE id = OBJECT_ID OPEN ColumnName FETCH NEXT FROM ColumnName INTO @DataFieldName PRINT '单据编号:' + @TypeNo WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'ListDetailDataFiled.Add('''+@DataFieldName+''');' FETCH NEXT FROM ColumnName INTO @DataFieldName END CLOSE ColumnName DEALLOCATE ColumnName FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName END CLOSE BillMsgCursor DEALLOCATE BillMsgCursor @@fetch_status值的意义:0 FETCH 语句成功;-1 FETCH 语句失败或此行不在结果集中;-2 被提取的行不存在 FETCH [NEXT/PRIOR/FIRST/LAST] FROM ... INTO 针对游标为SCROLL类型的 16. 游标的分类 1)静态游标:相当于临时表,会保存在tempdb里的私有表中,犹如快照表复制一份 a.一旦创建了游标,它就与实际记录相分离并不再维持任何锁 b.游标就是独立的,不再以任何方式与原始数据相关联 2)键集驱动的游标:需要在一定程度上感知对数据的修改,但不必了解最新发生的所有插入 a.表必须具有唯一索引 b.只有键集在tempdb中,而非整个数据集,对整个服务器性能产生有利的影响 c.能感知到对己是键集一部分的行所做的修改动态游标 a.完全动态,非常敏感,对底层数据做的所有事情都会影响,性能当然也是最差的 b.它们会带来额外的并发性问题 c.每发出一次FETCH,都要重建游标 d.可允许运行没有唯一索引的表中,但弊端会造成SQLSERVER无法追踪它在游标的位置造成死循环,应避免这样使用 4)快进游标 在许多情况下,FAST_FORWARD游标会隐式转换为其他游标类型

SELECT * FROM #tempSELECT * FROM #detail–删除临时表DROP TABLE #tempDROP TABLE #detail上面的T-SQL只在SQL Server 2005上调试成功。

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:总结复习,万能分页存储过程的详解_MsSql_脚本之

关键词:

上一篇:没有了

下一篇:没有了