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

(0)

相关推荐

  • sqlserver数据库主键的生成方式小结(sqlserver,mysql)

    主键的生成方式主要有三种: 一. 数据库自动生成 二. GUID 三. 开发创建 严格讲这三种产生方式有一定的交叉点,其定位方式将在下面进行讲解. 第一种方式,主要将其定位在自增长的标识种子:可以设置起始数值,及增长步长.其优点在于使用时完全将并发任务交于数据库引擎管理,你不用担心存在多用户使用的时候会产生两个相同的ID的情况.其缺点也在于此,多数的数据库不提供直接获取标识ID的方式,对于开发人员来说产生ID的方式是透明的,开发人员几乎无法干预此项.对于数据的迁移也不是很方便. 由于存在上面的利

  • sqlserver 快速生成汉字的首拼字母的函数(经典)

    代码如下: 复制代码 代码如下: USE [tempdb] GO /****** Object: UserDefinedFunction [dbo].[fun_getPY] Script Date: 05/23/2012 18:03:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* 获取汉字的首拼音 如果是非汉字字符 */ ALTER function [dbo].[fun_getPY] ( @str nvarchar(

  • sqlserver实现树形结构递归查询(无限极分类)的方法

    SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式 百度百科 公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它.每个CTE仅被定义一次(但在其作用域内可以被引用任意次),并且在该查询生存期间将一直生存.可以使用CTE来执行递归操作.创建的语法是: with <name of you cte>(<column names>) as( <actual query> ) select * from

  • 使用SqlServer CTE递归查询处理树、图和层次结构

    CTE(Common Table Expressions)是从SQL Server 2005以后版本才有的.指定的临时命名结果集,这些结果集称为CTE. 与派生表类似,不存储为对象,并且只在查询期间有效.与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次.使用CTE能改善代码可读性,且不损害其性能. 递归CTE是SQL SERVER 2005中重要的增强之一.一般我们在处理树,图和层次结构的问题时需要用到递归查询. CTE的语法如下 WITH CTE AS ( SELECT Em

  • SQLSERVER2005 中树形数据的递归查询

    问题描述.借用了adinet的问题.参见:http://www.jb51.net/article/28670.htm 今天做项目遇到一个问题, 有产品分类A,B,C顶级分类, 期中A下面有a1,a2,a3子分类. 但是a1可能共同属于A和B,然后我的数据库是这样设计的       id           name         parnet   1 A 0 2 B 0 3 a1 1,2 如果想要查询A的所有子类的话就要查询parent中包含1的,所以就萌生了这个办法.呵呵,解决方案 复制代码

  • sqlserver中存储过程的递归调用示例

    递归式指代码片段调用自身的情况:危险之处在于:如果调用了自身一次,那么如何防止他反复地调用自身.也就是说提供递归检验来保证适当的时候可以跳出. 以阶层为例子说存储过程中递归的调用. 递归 CREATE PROC [dbo].[usp_spFactorial] @InputValue INT, @OuputValue INT OUTPUT AS BEGIN DECLARE @InValue INT; DECLARE @OutValue INT; IF(@InputValue!=1) BEGIN S

  • 使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法

    下面是一个简单的Family Tree 示例: 复制代码 代码如下: DECLARE @TT TABLE (ID int,Relation varchar(25),Name varchar(25),ParentID int) INSERT @TT SELECT 1,' Great GrandFather' , 'Thomas Bishop', null UNION ALL SELECT 2,'Grand Mom', 'Elian Thomas Wilson' , 1 UNION ALL SELE

  • SQLserver2008使用表达式递归查询

    复制代码 代码如下: --由父项递归下级 with cte(id,parentid,text) as (--父项 select id,parentid,text from treeview where parentid = 450 union all --递归结果集中的下级 select t.id,t.parentid,t.text from treeview as t inner join cte as c on t.parentid = c.id ) select id,parentid,t

  • sqlserver另类非递归的无限级分类(存储过程版)

    下面是我统计的几种方案: 第一种方案(递归式): 简单的表结构为: CategoryID int(4), CategoryName nvarchar(50), ParentID int(4), Depth int(4) 这样根据ParentID一级级的运用递归找他的上级目录. 还有可以为了方便添加CategoryLeft,CategoryRight保存他的上级目录或下级目录 第二种方案: 设置一个varchar类型的CategoryPath字段来保存目录的完整路径,将父目录id用符号分隔开来.比

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

    问题: 在数据库脚本开发中,有时需要生成一堆连续数字或者日期,例如yearly report就需要连续数字做年份,例如daily report就需要生成一定时间范围内的每一天日期. 而自带的系统表master..spt_values存在一定的局限性,只是从0到2047(验证脚本:select * from master..spt_values b where b.type = 'P'),也不能直接生成连续日期. 可能大部分人会想到一个笨办法,通过while循环去逐条插入数据到临时表,每次数字加1

  • SQL Server 公用表表达式(CTE)实现递归的方法

    公用表表达式简介: 公用表表达式 (CTE) 可以认为是在单个 SELECT.INSERT.UPDATE.DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集.CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效.与派生表的不同之处在于,公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE.递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式. 下面先创建一个表,并插入一些数据: crea

  • SQL Server使用T-SQL进阶之公用表表达式(CTE)

    在编写T-SQL代码时,往往需要临时存储某些结果集.前面我们已经广泛使用和介绍了两种临时存储结果集的方法:临时表和表变量.除此之外,还可以使用公用表表达式的方法. 公用表表达式(Common Table Expression)是SQL Server2005版本的引入的一个特性.CTE可以看组是一个临时的结果集,可以再接下来来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中多次引用. 一.3种方法比较 使用公用表达式CTE可以让语句更加清晰简练.与公用表达式作用类似的

  • MariaDB表表达式之公用表表达式(CTE)

    目录 前言 1.非递归CTE 2.递归CTE 2.1 语法 2.2 递归CTE示例(1) 2.2 递归CTE示例(2) 2.2 递归CTE示例(3) 总结 前言 公用表表达式(Common Table Expression,CTE)和派生表类似,都是虚拟的表,但是相比于派生表,CTE具有一些优势和方便之处. CTE有两种类型:非递归的CTE和递归CTE. CTE是标准SQL的特性,属于表表达式的一种,MariaDB支持CTE,MySQL 8才开始支持CTE. 1.非递归CTE CTE是使用WIT

  • SQL2005 学习笔记 公用表表达式(CTE)

    公用表表达式 (CTE) 可以认为是在单个 SELECT.INSERT.UPDATE.DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集. CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效. 与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次. CTE可用于: 1.创建递归查询(我个人认为CTE最好用的地方) 2.在同一语句中多次引用生成的表 CTE优点: 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点. 查询可以分为单独块.简

  • mysql8 公用表表达式CTE的使用方法实例分析

    本文实例讲述了mysql8 公用表表达式CTE的使用方法.分享给大家供大家参考,具体如下: 公用表表达式CTE就是命名的临时结果集,作用范围是当前语句. 说白点你可以理解成一个可以复用的子查询,当然跟子查询还是有点区别的,CTE可以引用其他CTE,但子查询不能引用其他子查询. 一.cte的语法格式: with_clause: WITH [RECURSIVE] cte_name [(col_name [, col_name] ...)] AS (subquery) [, cte_name [(co

  • 关于SQL中CTE(公用表表达式)(Common Table Expression)的总结

    一.WITH AS的含义 WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到.有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分. 特别对于UNION ALL比较有用.因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可.如果WITH AS短语所定义的表名

  • SqlServer使用公用表表达式(CTE)实现无限级树形构建

    SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式 公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它.每个CTE仅被定义一次(但在其作用域内可以被引用任意次),并且在该查询生存期间将一直生存.可以使用CTE来执行递归操作. DECLARE @Level INT=3 ;WITH cte_parent(CategoryID,CategoryName,ParentCategoryID,Level) AS ( SELECT c

  • PHP操作SQL Server数据库实现表的改查与统计

    目录 今天上午做表格数据的检查与修改,涉及到PHP对MS SQL Server数据表的查询.统计与修改. 平时我也不记编码和命令,到了用的时候都再查手册,这往往很耗费时间,现在写博客有个好处就是把有用的代码记录下来,下次就直接复制,修改一下就直接用了. 这样可以省去很多时间. 下面是检索三个表来更新数据,即主表有信息不全,检索两个其他的信息表进行补录. <?php require 'pspLinkConfig.php';//加载数据库 $sql = "select C24 from cwk

  • SQL server 2005的表分区

    下面来说下,在SQL SERVER 2005的表分区里,如何对已经存在的有数据的表进行分区,其实道理和之前在http://www.cnblogs.com/jackyrong/archive/2006/11/13/559354.html说到一样,只不过交换下顺序而已,下面依然用例子说明:   依然在c盘的data2目录下建立4个文件夹,用来做4个文件组,然后建立数据库 use masterIF  EXISTS (SELECT name FROM sys.databases WHERE name =

随机推荐