≡
  • 网络编程
  • 数据库
  • CMS技巧
  • 软件编程
  • PHP笔记
  • JavaScript
  • MySQL
位置:首页 > 数据库 > SQL Server

SQL Server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期的简单示例

人气:386 时间:2020-06-06

这篇文章主要为大家详细介绍了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通过递归方式编写通用函数自动生成连续数字和日期 ,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对四海网网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

本文来自:http://www.q1010.com/179/9010-0.html

注:关于SQL Server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期的简单示例的内容就先介绍到这里,更多相关文章的可以留意四海网的其他信息。

关键词:SQL SERVER

您可能感兴趣的文章

  • 解决SqlServer 各版本 sa帐户不能登录问题
  • Windows使用性能监视器监控SqlServer的常见指标示例
  • 调整SQLServer2000运行数据库结构的简单示例
  • 解决SQLServer远程连接失败的问题
  • 数据库触发器DB2和SqlServer区别解析
  • 解决在Azure上部署Sqlserver网络访问不了示例
  • 在sqlserver中如何使用CTE解决复杂查询问题
  • SqlServer应用之sys.dm_os_waiting_tasks 引发的疑问(下)
  • SqlServer如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息
  • SQLServer中merge函数用法分析
上一篇:SQL Server使用SQL语句从电脑导入图片到数据库的简单示例
下一篇:SQL Server delete误删数据使用SCN号恢复的简单示例
热门文章
  • SQL Server SQL获取第一条记录的方法
  • SQL Server出现System.OutOfMemoryException异常的解决方法
  • SQL Server的 update from 语句的简单示例
  • SQL Server 数据库备份方法菜鸟教程
  • SQL Server 多表关联时在where语句中慎用trim()方法
  • SQL Server数据类型及长度限制详细说明
  • mybaits非配置原因,导致SqlSession was not registered for synchronization异常解析
  • SQL Server 收缩后对数据库的使用有影响吗?
  • SQL Server 格式导致的Excel导入sql出现异常的解决方法
  • SQL Server 连接服务器出现错误 7391的解决方法
  • 最新文章
    • SQL Server存储过程基本语法的简单示例
    • sql查询时增加自动编号和分页的简单示例
    • sql轻松应付百万数据的高效数据分页存储过程的简单示例
    • sql获取一条数据中所有字段的名称和值的实现方法
    • sql分割函数的简单示例
    • SQL Server异常捕获的简单示例
    • SQL SERVER回滚恢复误操作数据的实现方法
    • SQL Server函数或存储过程中抛出异常的实现方法
    • SQL Server创建数据库的完整代码
    • SQL Server创建数据库的命令用法示例

四海网收集整理一些常用的php代码,JS代码,数据库mysql等技术文章。