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是在内存中准备好数据，而不是每次一条往返服务器和客户端一次。如果需要再插入到临时表的话就是全部数据一次性插入。

如果传入参数为数字，则生成连续数字；如果传入参数为日期，则生成连续日期。是不是觉得很方便呢？

函数脚本：

ifobject_id('dbo.fun_ConcatStringsToTable')isnotnulldropfunctiondbo.fun_ConcatStringsToTablego/*功能：连续字符串（数字或日期）以table形式返回作者：zhang5022190482018-12-10脚本来源：https://www.cnblogs.com/zhang502219048/p/11108991.html--示例1（数字）：select*fromdbo.fun_ConcatStringsToTable(1,10000)--示例2（数字文本）：select*fromdbo.fun_ConcatStringsToTable('1','10000')--示例3（日期）：declare@dateBegindatetime='2009-1-1',@dateEnddatetime='2018-12-31'select*fromdbo.fun_ConcatStringsToTable(@dateBegin,@dateEnd)--示例4（日期文本）：select*fromdbo.fun_ConcatStringsToTable('2009-1-1','2018-12-31')**/createfunction[dbo].[fun_ConcatStringsToTable](@strBeginasnvarchar(100),@strEndasnvarchar(100))returns@tempResulttable(vidnvarchar(100))asbegin--数字ifisnumeric(@strBegin)=1andisnumeric(@strEnd)=1begin--使用CTE递归批量插入数字数据;withcte_table(id)as(selectcast(@strBeginasint)unionallselectid+1fromcte_tablewhereid<@strEnd)insertinto@tempResultselectcast(idasnvarchar(100))fromcte_tableoption(maxrecursion0)end--日期elseifisdate(@strBegin)=1andisdate(@strEnd)=1begin--使用CTE递归批量插入日期数据;withcte_table(CreatedDate)as(selectcast(@strBeginasdatetime)unionallselectdateadd(day,1,CreatedDate)fromcte_tablewhereCreatedDate<@strEnd)insertinto@tempResultselectconvert(varchar(10),CreatedDate,120)fromcte_tableoption(maxrecursion0)endreturn;endgo

调用函数示例：

--示例1（数字）：select*fromdbo.fun_ConcatStringsToTable(1,10000)--示例2（数字文本）：select*fromdbo.fun_ConcatStringsToTable('1','10000')--示例3（日期）：declare@dateBegindatetime='2009-1-1',@dateEnddatetime='2018-12-31'select*fromdbo.fun_ConcatStringsToTable(@dateBegin,@dateEnd)--示例4（日期文本）：select*fromdbo.fun_ConcatStringsToTable('2009-1-1','2018-12-31')

脚本运行结果：

结论：

从上面几个图可以看到，通过简单调用fun_ConcatStringsToTable这个自定义表值函数，指定起止数字或日期，就达到了生成连续数字和日期的目的。

扩展：

如果想生成连续月份呢？博主在这里也帮大家写了一下脚本，如果需要可以在此基础上再自行做成表值函数：

withcte_table(CreatedDate)as(selectcast('2017-12-1'asdatetime)unionallselectdateadd(month,1,CreatedDate)fromcte_tablewhereCreatedDate<'2018-04-01')selectconvert(varchar(7),CreatedDate,120)asYearMonthfromcte_tableoption(maxrecursion0)

看完上述内容，你们掌握sql server使用公用表表达式CTE通过递归方式如何编写通用函数自动生成连续数字和日期的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！