问题:

本文出处:

在数据库脚本开辟中,一时必要生成一批一而再再而三数字或然日期,举例yearly
report就须要三回九转数字做年份,例如daily
report就须要生成必然时间范围内的天天日期。

 

而自带的系统表master..spt_values存在一定的局限性,只是从0到2047,也不可能直接扭转三番两次日期。

大家在做开垦的时候,临时候会必要有的帮衬数据,必得供给三番四遍的数字,接二连三间距的时间点,一连的季度日期等等
相近超多个人选取master库的spt_values系统表,这一个本来没失常

唯恐大多数人会想到三个笨办法,通过while循环去各种插入数据到有时表,每一趟数字加1或许日期加1天,但这么和数据库服务器的并行就太频仍了。借使生成1W个三番两次数字,那将要跟数据库服务器人机联作1W次,骇人听闻!借使是有1000个客户端都供给调用那么些while循环,那正是1000W次!怕人!

例如上边那一个(没截完,结果是0-2047)

解决方案:

图片 1

能够选取公用表表明式CTE通过递归方式完成,并编写制定为三个通用表值函数方便调用,封装起来简化使用,重回表格式数据。

这么也足以利用,然则感到远远不够灵活,一是还是不是无论三个账号都能够访谈master数据库的,而是她那个中也只犹如此一个接连的数字了,
想要其他结果集就不太弄了,
看似数据足以用公用表表明式CTE的递归来变化
譬喻上述的0-2047的结果集

CTE是在内部存款和储蓄器中希图好数据,而不是历次一条往返服务器和客商端贰遍。若是需求再插入到不常表的话正是一切数额叁次性插入。

;with GenerateHelpDataas(  select 0 as id  union all  select id+1 from GenerateHelpData where id<2047)select id from GenerateHelpData option (maxrecursion 2047);

若果传入参数为数字,则变动一而再数字;借使传入参数为日期,则转移三回九转日期。是还是不是感到相当低价呢?

能够一贯让CTE参数逻辑运算,也足以更动一时表,到达数十次录用的目标,这样以为是不是也很娱心悦目?

函数脚本:

 

if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTablego/* 功能:连续字符串以table形式返回 作者:zhang502219048 2018-12-10 脚本来源: -- 示例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))asbegin --数字 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;endgo

1,生成三番五次数字(当然数字的开端值,间距值都能够自定义)

调用函数示例:

--生成连续数字;with GenerateHelpDataas(  select 0 as id  union all  select id+1 from GenerateHelpData where id<2047)select id from GenerateHelpData option (maxrecursion 2047);
-- 示例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')

图片 2

本子运维结果:

 

结论:

2,CTE递归生成两次三番日期

从地点多少个图能够看出,通过轻松调用fun_ConcatStringsToTable本条自定义表值函数,钦点起止数字或日期,就达到了转移三回九转数字和日期的指标。

--生成连续日期;with GenerateHelpDataas(  select cast('2016-10-01' as date) as [Date]  union all  select DATEADD(D,1,[Date]) from GenerateHelpData where [Date]<'2017-01-01')select [Date] from GenerateHelpData;

扩展:

图片 3

万后生可畏想生成三回九转月份吧?博主在那地也帮我们写了瞬间剧本,假若需求可以在这里底子上再自行做成表值函数:

 

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 YearMonthfrom cte_tableoption (maxrecursion 0)

3,生成三番四遍间距的年月点

总结

  一时候有些总括需求依据一个时辰或然半个钟头之类的时日间距做结合,举例计算某天内没半小时的小时数据等等

以上所述是笔者给大家介绍的sql
server使用公用表表明式CTE通过递归形式编写通用函数自动生成三番五次数字和日期
,希望对大家全数助于,即便大家有此外疑问请给小编留言,小编会及时过来大家的。在这里也特别谢谢我们对剧本之家网址的援救!借使您以为本文对您有扶持,款待转发,烦请评释出处,多谢!

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注