sqlserver如何可视化表(sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日)
sqlserver如何可视化表
sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日问题:
在数据库脚本开发中,有时需要生成一堆连续数字或者日期,例如yearly report就需要连续数字做年份,例如daily report就需要生成一定时间范围内的每一天日期。
而自带的系统表master..spt_values存在一定的局限性,只是从0到2047(验证脚本:select * from master..spt_values b where b.type = 'P'
),也不能直接生成连续日期。
可能大部分人会想到一个笨办法,通过while循环去逐条插入数据到临时表,每次数字加1或者日期加1天,但这样和数据库服务器的交互就太频繁了。如果生成1W个连续数字,那就要跟数据库服务器交互1W次,可怕!如果是有1000个客户端都需要调用这个while循环,那就是1000W次!可怕!
解决方案:
可以使用公用表表达式CTE通过递归方式实现,并编写为一个通用表值函数方便调用,封装起来简化使用,返回表格式数据。
CTE是在内存中准备好数据,而不是每次一条往返服务器和客户端一次。如果需要再插入到临时表的话就是全部数据一次性插入。
如果传入参数为数字,则生成连续数字;如果传入参数为日期,则生成连续日期。
是不是觉得很方便呢?
函数脚本:
if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTable go /* 功能:连续字符串(数字或日期)以table形式返回 作者:zhang502219048 2018-12-10 脚本来源:https://www.cnblogs.com/zhang502219048/p/11108991.html -- 示例1(数字): select * from dbo.fun_ConcatStringsToTable(1, 10000) -- 示例2(数字文本): select * from dbo.fun_ConcatStringsToTable('1', '10000') -- 示例3(日期): declare @dateBegin datetime = '2009-1-1', @dateEnd datetime = '2018-12-31' select * from dbo.fun_ConcatStringsToTable(@dateBegin, @dateEnd) -- 示例4(日期文本): select * from dbo.fun_ConcatStringsToTable('2009-1-1', '2018-12-31') **/ create function [dbo].[fun_ConcatStringsToTable] ( @strBegin as nvarchar(100), @strEnd as nvarchar(100) ) returns @tempResult table (vid nvarchar(100)) as begin --数字 if isnumeric(@strBegin) = 1 and isnumeric(@strEnd) = 1 begin --使用CTE递归批量插入数字数据 ;with cte_table(id) as ( select cast(@strBegin as int) union all select id + 1 from cte_table where id < @strEnd ) insert into @tempResult select cast(id as nvarchar(100)) from cte_table option (maxrecursion 0) end --日期 else if isdate(@strBegin) = 1 and isdate(@strEnd) = 1 begin --使用CTE递归批量插入日期数据 ;with cte_table(CreatedDate) as ( select cast(@strBegin as datetime) union all select dateadd(day, 1, CreatedDate) from cte_table where CreatedDate < @strEnd ) insert into @tempResult select convert(varchar(10), CreatedDate, 120) from cte_table option (maxrecursion 0) end return; end go
调用函数示例:
-- 示例1(数字): select * from dbo.fun_ConcatStringsToTable(1, 10000) -- 示例2(数字文本): select * from dbo.fun_ConcatStringsToTable('1', '10000') -- 示例3(日期): declare @dateBegin datetime = '2009-1-1', @dateEnd datetime = '2018-12-31' select * from dbo.fun_ConcatStringsToTable(@dateBegin, @dateEnd) -- 示例4(日期文本): select * from dbo.fun_ConcatStringsToTable('2009-1-1', '2018-12-31')
脚本运行结果:
结论:
从上面几个图可以看到,通过简单调用fun_ConcatStringsToTable
这个自定义表值函数,指定起止数字或日期,就达到了生成连续数字和日期的目的。
扩展:
如果想生成连续月份呢?博主在这里也帮大家写了一下脚本,如果需要可以在此基础上再自行做成表值函数:
with cte_table(CreatedDate) as ( select cast('2017-12-1' as datetime) union all select dateadd(month, 1, CreatedDate) from cte_table where CreatedDate < '2018-04-01' ) select convert(varchar(7), CreatedDate, 120) as YearMonth from cte_table option (maxrecursion 0)
总结
以上所述是小编给大家介绍的sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期 ,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对开心学习网网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!
- sqlserver定时计划(sql server deadlock跟踪的4种实现方法)
- sqlserver代理无法启动错误1607(SQL Server代理:理解SQL代理错误日志处理方法)
- SQLServer2019安装教程图文详解(SQLServer2019安装教程图文详解)
- sqlserver乐观锁与悲观锁(sql server中死锁排查的全过程分享)
- mysql 命令与sqlserver的区别大么(MySQL系列之执行SQL 语句时发生了什么?)
- sqlserver索引实例(SQL Server索引的原理深入解析)
- sqlserver恢复delete数据(SQL Server数据库的三种恢复模式:简单恢复模式、完整恢复模式和大容量日志恢)
- sqlserver小结(基于sqlserver的四种分页方式总结)
- sqlserver函数条件判断(Sql Server 开窗函数Over的使用实例详解)
- sqlserverdatetime时区(解析SQL Server中datetimeset转换datetime类型问题)
- sqlserver仲裁状态(使用 SQL 服务器时,"评估期已过期"错误消息解决方法)
- sqlserver非唯一索引汇总性能(详解SQL Server的聚焦过滤索引)
- sqlserver2019使用教程图文(SQLServer2019 数据库环境搭建与使用的实现)
- sqlserver模糊查询使用定义的字段(SQL Server模糊查询的常见方法总结)
- sql server优化性能(SQLServer地址搜索性能优化)
- sql2008提供身份认证模式(SqlServer2000+ 身份证合法校验函数的示例代码)
- 刚红就耍大牌,《琉璃》角色滤镜碎一地,心疼工作人员(琉璃角色滤镜碎一地)
- 袁冰妍郑业成这对可以处,有脸红情话他们是真的敢说(袁冰妍郑业成这对可以处)
- 《祝卿好》台词又土又甜,就喜欢这么直接的恋爱(祝卿好台词又土又甜)
- 大女主 汤唯垂青电视圈,搭档朱亚文出演《大明皇妃孙若微传》(汤唯垂青电视圈)
- 红色代表什么(红色代表什么情感和含义)
- 高中数学题(高中数学题型总结及解题方法)
热门推荐
- sql server存储过程详解(SQL Server存储过程中编写事务处理的方法小结)
- dedecms可以长期使用吗(dedecms5.7 文章不能发布问题探讨及解决方法)
- python3和python2 兼容(Python2和Python3的共存和切换使用)
- 织梦调用栏目内容(织梦标签channel实现列表页调用当前栏目的子类)
- python 装饰器模式(python重试装饰器的简单实现方法)
- sql server2012表的数据删除(SQL Server删除表及删除表中数据的方法)
- python怎么自动生成报告(python根据文章标题内容自动生成摘要的实例)
- html5div例子(html5 外链式实现加减乘除的代码)
- idea发布项目在tomcat哪个目录下(idea发布web项目后Tomcat服务器找不到该项目的问题及解决方法)
- php获取数据方法(php定期拉取数据对比方法实例)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9