欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

快速生成基数的辅助表

程序员文章站 2022-03-22 12:46:33
原问题如下: Insus.NET的问题解决,分2步,1,创建一个辅助表,2,合并数据。 程式码搜寻出来如图: IF OBJECT_ID(N'TEMPDB.DBO.#search_data') IS NOT NULL DROP TABLE #search_data GO CREATE TABLE #s ......

原问题如下:

 

 

insus.net的问题解决,分2步,1,创建一个辅助表,2,合并数据。

程式码搜寻出来如图:

 

if object_id(n'tempdb.dbo.#search_data') is not null
drop table #search_data
go
 
create table #search_data ([times] datetime,[val] int)
 
insert into #search_data ([times],[val]) values
('2001-01-01 00:00:00',13),
('2001-01-01 00:10:00',22),
('2001-01-01 00:20:00',31),
('2001-01-01 00:40:00',5 ),
('2001-01-01 00:50:00',64),
('2001-01-01 02:30:00',73),
('2001-01-01 04:10:00',63),
('2001-01-01 04:50:00',12),
('2001-01-01 06:30:00',32)
 

 

 

接下来,我们可以创建一张辅助表:

 

 

declare @start_datetime datetime = '2000-01-01 00:00:00',
        @end_datetime datetime  = '2000-02-01 00:00:00'

;with dts([times]) 
as
(
    select  @start_datetime
    union all
    select [times] = dateadd(minute,10, [times])
    from [dts]
    where [times] < @end_datetime
)
select [times] from [dts] option (maxrecursion 0)

 

总共产生4465行记录。

更多相关辅助表,可以参考《使用cte生成辅助表(数字或时间)等》 和《快速生成基数的辅助表

 

为了更好操作,把辅助表的产生数据存入一张临时表中:

 

if object_id(n'tempdb.dbo.#base') is not null
    drop table #base
go
 
create table #base ([times] datetime)

declare @start_datetime datetime = '2000-01-01 00:00:00',
        @end_datetime datetime  = '2000-02-01 00:00:00'
;with dts([times]) 
as
(
    select  @start_datetime
    union all
    select [times] = dateadd(minute,10, [times])
    from [dts]
    where [times] < @end_datetime
)

insert into #base ([times]) select [times] from [dts] option (maxrecursion 0)

 

第2步,合并数据,把程序搜索结果与辅助表的数据进行合并merge:

 

merge #search_data as target
    using (select [times] from #base) as source
    on (target.[times] = source.[times])
 
    when not matched by target then
        insert ([times],[val]) values ([times],0);  

select [times],[val] from #search_data

 

ok,实现方法仅供参考,完整代码:

if object_id(n'tempdb.dbo.#search_data') is not null
drop table #search_data
go
 
create table #search_data ([times] datetime,[val] int)
 
insert into #search_data ([times],[val]) values
('2001-01-01 00:00:00',13),
('2001-01-01 00:10:00',22),
('2001-01-01 00:20:00',31),
('2001-01-01 00:40:00',5 ),
('2001-01-01 00:50:00',64),
('2001-01-01 02:30:00',73),
('2001-01-01 04:10:00',63),
('2001-01-01 04:50:00',12),
('2001-01-01 06:30:00',32)
 
 
 
if object_id(n'tempdb.dbo.#base') is not null
    drop table #base
go
 
create table #base ([times] datetime)

declare @start_datetime datetime = '2000-01-01 00:00:00',
        @end_datetime datetime  = '2000-02-01 00:00:00'
;with dts([times]) 
as
(
    select  @start_datetime
    union all
    select [times] = dateadd(minute,10, [times])
    from [dts]
    where [times] < @end_datetime
)

insert into #base ([times]) select [times] from [dts] option (maxrecursion 0)



merge #search_data as target
    using (select [times] from #base) as source
    on (target.[times] = source.[times])
 
    when not matched by target then
        insert ([times],[val]) values ([times],0);  

select [times],[val] from #search_data