sql server使用公用表表达式CTE通过递归方式如何编写通用函数自动生成连续数字和日期

110次阅读

共计 2916 个字符，预计需要花费 8 分钟才能阅读完成。

行业资讯
数据库
sql server 使用公用表表达式 CTE 通过递归方式如何编写通用函数自动生成连续数字和日期

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_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 通过递归方式如何编写通用函数自动生成连续数字和日期的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！

正文完