那将要跟数据库服务器交互作用1W次,必需必要连接的数字

澳门新葡8455最新网站 5
if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTablego/* 功能:连续字符串以table形式返回 作者:zhang502219048 2018-12-10 脚本来源: -- 示例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
--生成连续数字
;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id+1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

以上所述是小编给大家介绍的sql
server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期
,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id+1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

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

 

结论:

create table ProuctInfo
(
    Id INT,
    ParentId INT,
    ProuctName VARCHAR(50)
)

INSERT INTO ProuctInfo VALUES (1,0,'镜片')
INSERT INTO ProuctInfo VALUES (2,0,'镜架')
INSERT INTO ProuctInfo VALUES (101,1,'高级镜片')
INSERT INTO ProuctInfo VALUES (102,1,'普通镜片')
INSERT INTO ProuctInfo VALUES (201,2,'高级镜架')
INSERT INTO ProuctInfo VALUES (202,2,'普通镜架')

INSERT INTO ProuctInfo VALUES (1001,101,'高级镜片1')
INSERT INTO ProuctInfo VALUES (1002,102,'普通镜片2')
INSERT INTO ProuctInfo VALUES (2001,201,'高级镜架1')
INSERT INTO ProuctInfo VALUES (2002,202,'普通镜架2')

脚本运行结果:

澳门新葡8455最新网站 1

问题:

 

总结

 

解决方案:

  澳门新葡8455最新网站 2

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

澳门新葡8455最新网站, 

在数据库脚本开发中,有时需要生成一堆连续数字或者日期,例如yearly
report就需要连续数字做年份,例如daily
report就需要生成一定时间范围内的每一天日期。

澳门新葡8455最新网站 3

可以使用公用表表达式CTE通过递归方式实现,并编写为一个通用表值函数方便调用,封装起来简化使用,返回表格式数据。

 

而自带的系统表master..spt_values存在一定的局限性,只是从0到2047,也不能直接生成连续日期。

--生成连续间隔的时间点
;with GenerateHelpData
as
(
    select 1 as id, cast('00:00:00' as time(0)) as timeSection
    union all
    select id+1 as id,  cast(dateadd(mi,30,timeSection) as time(0)) as timeSection 
     from GenerateHelpData  where id<49
)
select * from GenerateHelpData

可能大部分人会想到一个笨办法,通过while循环去逐条插入数据到临时表,每次数字加1或者日期加1天,但这样和数据库服务器的交互就太频繁了。如果生成1W个连续数字,那就要跟数据库服务器交互1W次,可怕!如果是有1000个客户端都需要调用这个while循环,那就是1000W次!可怕!

原始数据的样子,很普通

调用函数示例:

可以直接让CTE参数逻辑运算,也可以生成临时表,达到多次重用的目的,这样感觉是不是也很清爽?

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

需要注意的是,CTE递归的默认次数是100,如果不指定递归次数(option
(maxrecursion N);),超出默认最大递归次数之后会报错。

函数脚本:

DECLARE 
@begin_date date = '2014-12-31',
@end_date date = '2016-12-31'
;with GenerateHelpData as
(
    select 1 as id ,
        CAST(    CASE 
                    WHEN RIGHT(@begin_date,5)='12-30' 
                THEN DATEADD(DAY,1,@begin_date) 
                    ELSE @begin_date 
                END AS    DATE)
        AS EndingDate
    UNION ALL
    SELECT     id+1 as id,
        CASE WHEN RIGHT(DATEADD(QQ,1,EndingDate),5)='12-30' 
        THEN  DATEADD(DAY,1,DATEADD(QQ,1,EndingDate)) 
        ELSE DATEADD(QQ,1,EndingDate)
        END AS EndingDate
    from GenerateHelpData where EndingDate< @end_date
)
select 
A.EndingDate as DateFrom,
B.EndingDate as DateTo,
cast(A.EndingDate as varchar(10))+'~'+cast(B.EndingDate as varchar(10)) as timeSection
from GenerateHelpData  A inner join GenerateHelpData B on A.id= B.id-1

扩展:

测试数据:

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

CREATE FUNCTION dbo.FnGetParentInfo(@id int)
returns varchar(max)
as
begin

declare @name varchar(max)

--查询某一个节点的所有父节点
;with SubTab
as
(
    select [ID],[ParentID], cast(ProuctName as varchar(200)) as ProuctName
    from ProuctInfo WHERE Id = @id
    union all
    select a.[ID],a.[ParentID],cast(a.ProuctName+'--->'+b.ProuctName as varchar(200)) as ProuctName
    from ProuctInfo a,SubTab b
    where a.[ID]=b.[ParentID] 
)
select @name = ProuctName from SubTab  where ParentId = 0 

return @name
end
-- 示例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')

3,生成连续间隔的时间点

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)

1,生成连续数字(当然数字的起始值,间隔值都可以自定义)

澳门新葡8455最新网站 4

 

 

 

 

  有时候一些统计需要按照一个小时或者半个小时之类的时间间隔做组合,比如统计某天内没半个小时的小时数据等等

澳门新葡8455最新网站 5

 

 

DECLARE 
@begin_date date = '2014-12-31',
@end_date date = '2016-12-31'
;with GenerateHelpData as
(
    select 
        CAST(    CASE 
                    WHEN RIGHT(@begin_date,5)='12-30' 
                THEN DATEADD(DAY,1,@begin_date) 
                    ELSE @begin_date 
                END AS    DATE)
        AS EndingDate
    UNION ALL
    SELECT     
        CASE WHEN RIGHT(DATEADD(QQ,1,EndingDate),5)='12-30' 
        THEN  DATEADD(DAY,1,DATEADD(QQ,1,EndingDate)) 
        ELSE DATEADD(QQ,1,EndingDate)
        END AS EndingDate
    from GenerateHelpData where EndingDate< @end_date
)
select * from GenerateHelpData

这样也可以使用,但是感觉不够灵活,一是不是随便一个账号都可以访问master数据库的,而是他这里面也只有这么一个连续的数字了,
想要别的结果集就不太弄了,
类似数据可以用公用表表达式CTE的递归来生成
比如上述的0-2047的结果集

 

相关文章

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图