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

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

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

函数脚本:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

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

2

3

4

5

6

7

8

9

— 示例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')

脚本运行结果:

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

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

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

结论:

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

扩展:

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

?

1

2

3

4

5

6

7

8

9

10

11

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

原文链接:https://www.cnblogs.com/zhang502219048/archive/2019/06/30/11108991.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/24468/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年9月21日 下午3:03
下一篇 2022年9月22日 上午11:16
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!