问题：在数据库脚本开发中，有时需要生成一堆连续数字或者日期，例如yearlyreport就需要连续数字做年份，例如dailyreport就需要生成一定时间范围内的每一天日期。而自带的系统表master..spt_values存在一定的局限性，只是从0到2047（验证脚本：select*frommaster..spt_valuesbwhereb.type='P'），也不能直接生成连续日期
问题：
在数据库脚本开发中，有时需要生成一堆连续数字或者日期，例如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_ConcatStringsToTablego/* 功能：连续字符串（数字或日期）以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))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（数字）： 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 YearMonthfrom cte_tableoption (maxrecursion 0)总结
以上所述是小编给大家介绍的sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期 ,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
标签: sqlserver 使用 公用 表达式 CTE 通过 递归 方式 编写
声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！
