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通过递归方式编写通用函数自动生成连续数字和日期 ,希望对大家有所帮助