求 SQL 查询方案

马老虎 2013-10-23 11:42:03 





create table #aaaa

(

	datet datetime,

	value varchar(11)

);



insert into #aaaa values ('2013-10-23 09:00','0.5');

insert into #aaaa values ('2013-10-23 09:05','0.3');

insert into #aaaa values ('2013-10-23 09:10','0.5');

insert into #aaaa values ('2013-10-23 09:15','0.6');

insert into #aaaa values ('2013-10-23 09:20','0.1');

insert into #aaaa values ('2013-10-23 09:20','0.1');



insert into #aaaa values ('2013-10-23 09:30','0.2');

insert into #aaaa values ('2013-10-23 10:00','0.9');

insert into #aaaa values ('2013-10-23 10:05','0.3');

insert into #aaaa values ('2013-10-23 10:10','0.3');



insert into #aaaa values ('2013-10-23 10:20','0.3');



select * from #aaaa;







drop table #aaaa;


以上是我提供的测试SQL.
现在在 临时表中的 datet 数据应该是每5分钟就有一条数据。但是实际上表中的数据部是以每5分钟存储的。
请问大家，如何查询出的数据是以每5分钟为一条的数据。
比如上面 没有 10:15的数据，那么查询出的结果 10:15对应的value 是0 。

数据有可能是跨好几天的数据查询，求方案。。
...全文
240 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
LongRui888 2013-10-23
  • 打赏
  • 举报
 回复
完整的，看看是不是你要的： 

if object_id('tb') is not null
   drop table tb
go

create table tb
(
    datet datetime,
    value varchar(11)
);
 
insert into tb values ('2013-10-23 09:00','0.5');
insert into tb values ('2013-10-23 09:05','0.3');
insert into tb values ('2013-10-23 09:10','0.5');
insert into tb values ('2013-10-23 09:15','0.6');
insert into tb values ('2013-10-23 09:20','0.1');
insert into tb values ('2013-10-23 09:20','0.1');
insert into tb values ('2013-10-23 09:30','0.2');
insert into tb values ('2013-10-23 10:00','0.9');
insert into tb values ('2013-10-23 10:05','0.3');
insert into tb values ('2013-10-23 10:10','0.3');
insert into tb values ('2013-10-23 10:20','0.3');



;with time_interval
as
(
select 0 as number

union all

select number + 1
from time_interval 
where number < 20000
)


select dateadd(minute,number * 5,'2013-10-23 09:00') as ti,
       tb.value
from time_interval t
left join tb
       on tb.datet = dateadd(minute,number * 5,'2013-10-23 09:00')
where dateadd(minute,number * 5,'2013-10-23 09:00') < '2013-10-23 11:00:00'
option(maxrecursion 20000)
/*
ti	                value
2013-10-23 09:00:00.000	0.5
2013-10-23 09:05:00.000	0.3
2013-10-23 09:10:00.000	0.5
2013-10-23 09:15:00.000	0.6
2013-10-23 09:20:00.000	0.1
2013-10-23 09:20:00.000	0.1
2013-10-23 09:25:00.000	NULL
2013-10-23 09:30:00.000	0.2
2013-10-23 09:35:00.000	NULL
2013-10-23 09:40:00.000	NULL
2013-10-23 09:45:00.000	NULL
2013-10-23 09:50:00.000	NULL
2013-10-23 09:55:00.000	NULL
2013-10-23 10:00:00.000	0.9
2013-10-23 10:05:00.000	0.3
2013-10-23 10:10:00.000	0.3
2013-10-23 10:15:00.000	NULL
2013-10-23 10:20:00.000	0.3
2013-10-23 10:25:00.000	NULL
2013-10-23 10:30:00.000	NULL
2013-10-23 10:35:00.000	NULL
2013-10-23 10:40:00.000	NULL
2013-10-23 10:45:00.000	NULL
2013-10-23 10:50:00.000	NULL
2013-10-23 10:55:00.000	NULL
*/
LongRui888 2013-10-23
  • 打赏
  • 举报
 回复 


;with time_interval
as
(
select 0 as number

union all

select number + 1
from time_interval 
where number < 20000
)


select dateadd(minute,number * 5,'2013-10-23 00:00')
from time_interval
where dateadd(minute,number * 5,'2013-10-23 10:00') < '2013-10-23 15:00:00'
option(maxrecursion 20000)
/*
(无列名)
2013-10-23 00:00:00.000
2013-10-23 00:05:00.000
2013-10-23 00:10:00.000
2013-10-23 00:15:00.000
2013-10-23 00:20:00.000
2013-10-23 00:25:00.000
2013-10-23 00:30:00.000
2013-10-23 00:35:00.000
2013-10-23 00:40:00.000
2013-10-23 00:45:00.000
2013-10-23 00:50:00.000
2013-10-23 00:55:00.000
2013-10-23 01:00:00.000
2013-10-23 01:05:00.000
2013-10-23 01:10:00.000
2013-10-23 01:15:00.000
2013-10-23 01:20:00.000
2013-10-23 01:25:00.000
2013-10-23 01:30:00.000
2013-10-23 01:35:00.000
2013-10-23 01:40:00.000
2013-10-23 01:45:00.000
2013-10-23 01:50:00.000
2013-10-23 01:55:00.000
2013-10-23 02:00:00.000
2013-10-23 02:05:00.000
2013-10-23 02:10:00.000
2013-10-23 02:15:00.000
2013-10-23 02:20:00.000
2013-10-23 02:25:00.000
2013-10-23 02:30:00.000
2013-10-23 02:35:00.000
2013-10-23 02:40:00.000
2013-10-23 02:45:00.000
2013-10-23 02:50:00.000
2013-10-23 02:55:00.000
2013-10-23 03:00:00.000
2013-10-23 03:05:00.000
2013-10-23 03:10:00.000
2013-10-23 03:15:00.000
2013-10-23 03:20:00.000
2013-10-23 03:25:00.000
2013-10-23 03:30:00.000
2013-10-23 03:35:00.000
2013-10-23 03:40:00.000
2013-10-23 03:45:00.000
2013-10-23 03:50:00.000
2013-10-23 03:55:00.000
2013-10-23 04:00:00.000
2013-10-23 04:05:00.000
2013-10-23 04:10:00.000
2013-10-23 04:15:00.000
2013-10-23 04:20:00.000
2013-10-23 04:25:00.000
2013-10-23 04:30:00.000
2013-10-23 04:35:00.000
2013-10-23 04:40:00.000
2013-10-23 04:45:00.000
2013-10-23 04:50:00.000
2013-10-23 04:55:00.000
*/
Andy__Huang 2013-10-23
  • 打赏
  • 举报
 回复 
create table #aaaa
(
	datet datetime,
	value varchar(11)
);

insert into #aaaa values ('2013-10-23 09:00','0.5');
insert into #aaaa values ('2013-10-23 09:05','0.3');
insert into #aaaa values ('2013-10-23 09:10','0.5');
insert into #aaaa values ('2013-10-23 09:15','0.6');
insert into #aaaa values ('2013-10-23 09:20','0.1');
insert into #aaaa values ('2013-10-23 09:20','0.1');
insert into #aaaa values ('2013-10-23 09:30','0.2');
insert into #aaaa values ('2013-10-23 10:00','0.9');
insert into #aaaa values ('2013-10-23 10:05','0.3');
insert into #aaaa values ('2013-10-23 10:10','0.3');
insert into #aaaa values ('2013-10-23 10:20','0.3');

select a.datet,b.value 
from 
	(select datet=DATEADD(minute,number*5,datet)
	from (select MIN(datet) as datet from #aaaa)a,master..spt_values
	where type='P' and number<=100
	)a 
left join #aaaa b on convert(varchar(16),a.datet,120)=convert(varchar(16),b.datet,120)
where exists(select 1 from #aaaa t where CONVERT(varchar(10),a.datet,120)=CONVERT(varchar(10),t.datet,120))

/*
datet	value
2013-10-23 09:00:00.000	0.5
2013-10-23 09:05:00.000	0.3
2013-10-23 09:10:00.000	0.5
2013-10-23 09:15:00.000	0.6
2013-10-23 09:20:00.000	0.1
2013-10-23 09:20:00.000	0.1
2013-10-23 09:25:00.000	NULL
2013-10-23 09:30:00.000	0.2
2013-10-23 09:35:00.000	NULL
2013-10-23 09:40:00.000	NULL
2013-10-23 09:45:00.000	NULL
2013-10-23 09:50:00.000	NULL
2013-10-23 09:55:00.000	NULL
2013-10-23 10:00:00.000	0.9
2013-10-23 10:05:00.000	0.3
2013-10-23 10:10:00.000	0.3
2013-10-23 10:15:00.000	NULL
2013-10-23 10:20:00.000	0.3
2013-10-23 10:25:00.000	NULL
2013-10-23 10:30:00.000	NULL
2013-10-23 10:35:00.000	NULL
2013-10-23 10:40:00.000	NULL
2013-10-23 10:45:00.000	NULL
2013-10-23 10:50:00.000	NULL
2013-10-23 10:55:00.000	NULL
2013-10-23 11:00:00.000	NULL
2013-10-23 11:05:00.000	NULL
2013-10-23 11:10:00.000	NULL
2013-10-23 11:15:00.000	NULL
2013-10-23 11:20:00.000	NULL
2013-10-23 11:25:00.000	NULL
2013-10-23 11:30:00.000	NULL
2013-10-23 11:35:00.000	NULL
2013-10-23 11:40:00.000	NULL
2013-10-23 11:45:00.000	NULL
2013-10-23 11:50:00.000	NULL
2013-10-23 11:55:00.000	NULL
2013-10-23 12:00:00.000	NULL
2013-10-23 12:05:00.000	NULL
2013-10-23 12:10:00.000	NULL
2013-10-23 12:15:00.000	NULL
2013-10-23 12:20:00.000	NULL
2013-10-23 12:25:00.000	NULL
2013-10-23 12:30:00.000	NULL
2013-10-23 12:35:00.000	NULL
2013-10-23 12:40:00.000	NULL
2013-10-23 12:45:00.000	NULL
2013-10-23 12:50:00.000	NULL
2013-10-23 12:55:00.000	NULL
2013-10-23 13:00:00.000	NULL
2013-10-23 13:05:00.000	NULL
2013-10-23 13:10:00.000	NULL
2013-10-23 13:15:00.000	NULL
2013-10-23 13:20:00.000	NULL
2013-10-23 13:25:00.000	NULL
2013-10-23 13:30:00.000	NULL
2013-10-23 13:35:00.000	NULL
2013-10-23 13:40:00.000	NULL
2013-10-23 13:45:00.000	NULL
2013-10-23 13:50:00.000	NULL
2013-10-23 13:55:00.000	NULL
2013-10-23 14:00:00.000	NULL
2013-10-23 14:05:00.000	NULL
2013-10-23 14:10:00.000	NULL
2013-10-23 14:15:00.000	NULL
2013-10-23 14:20:00.000	NULL
2013-10-23 14:25:00.000	NULL
2013-10-23 14:30:00.000	NULL
2013-10-23 14:35:00.000	NULL
2013-10-23 14:40:00.000	NULL
2013-10-23 14:45:00.000	NULL
2013-10-23 14:50:00.000	NULL
2013-10-23 14:55:00.000	NULL
2013-10-23 15:00:00.000	NULL
2013-10-23 15:05:00.000	NULL
2013-10-23 15:10:00.000	NULL
2013-10-23 15:15:00.000	NULL
2013-10-23 15:20:00.000	NULL
2013-10-23 15:25:00.000	NULL
2013-10-23 15:30:00.000	NULL
2013-10-23 15:35:00.000	NULL
2013-10-23 15:40:00.000	NULL
2013-10-23 15:45:00.000	NULL
2013-10-23 15:50:00.000	NULL
2013-10-23 15:55:00.000	NULL
2013-10-23 16:00:00.000	NULL
2013-10-23 16:05:00.000	NULL
2013-10-23 16:10:00.000	NULL
2013-10-23 16:15:00.000	NULL
2013-10-23 16:20:00.000	NULL
2013-10-23 16:25:00.000	NULL
2013-10-23 16:30:00.000	NULL
2013-10-23 16:35:00.000	NULL
2013-10-23 16:40:00.000	NULL
2013-10-23 16:45:00.000	NULL
2013-10-23 16:50:00.000	NULL
2013-10-23 16:55:00.000	NULL
2013-10-23 17:00:00.000	NULL
2013-10-23 17:05:00.000	NULL
2013-10-23 17:10:00.000	NULL
2013-10-23 17:15:00.000	NULL
2013-10-23 17:20:00.000	NULL
*/
LongRui888 2013-10-23
  • 打赏
  • 举报
 回复
这个是上面的语句生成的，速度非常快： 
(无列名)
2013-10-23 00:00:00.000
2013-10-23 00:05:00.000
2013-10-23 00:10:00.000
2013-10-23 00:15:00.000
2013-10-23 00:20:00.000
2013-10-23 00:25:00.000
2013-10-23 00:30:00.000
2013-10-23 00:35:00.000
2013-10-23 00:40:00.000
2013-10-23 00:45:00.000
2013-10-23 00:50:00.000
2013-10-23 00:55:00.000
2013-10-23 01:00:00.000
2013-10-23 01:05:00.000
2013-10-23 01:10:00.000
2013-10-23 01:15:00.000
2013-10-23 01:20:00.000
2013-10-23 01:25:00.000
2013-10-23 01:30:00.000
2013-10-23 01:35:00.000
2013-10-23 01:40:00.000
2013-10-23 01:45:00.000
2013-10-23 01:50:00.000
2013-10-23 01:55:00.000
2013-10-23 02:00:00.000
2013-10-23 02:05:00.000
2013-10-23 02:10:00.000
2013-10-23 02:15:00.000
2013-10-23 02:20:00.000
2013-10-23 02:25:00.000
2013-10-23 02:30:00.000
2013-10-23 02:35:00.000
2013-10-23 02:40:00.000
2013-10-23 02:45:00.000
2013-10-23 02:50:00.000
2013-10-23 02:55:00.000
2013-10-23 03:00:00.000
2013-10-23 03:05:00.000
2013-10-23 03:10:00.000
2013-10-23 03:15:00.000
2013-10-23 03:20:00.000
2013-10-23 03:25:00.000
2013-10-23 03:30:00.000
2013-10-23 03:35:00.000
2013-10-23 03:40:00.000
2013-10-23 03:45:00.000
2013-10-23 03:50:00.000
2013-10-23 03:55:00.000
2013-10-23 04:00:00.000
2013-10-23 04:05:00.000
2013-10-23 04:10:00.000
2013-10-23 04:15:00.000
2013-10-23 04:20:00.000
2013-10-23 04:25:00.000
2013-10-23 04:30:00.000
2013-10-23 04:35:00.000
2013-10-23 04:40:00.000
2013-10-23 04:45:00.000
2013-10-23 04:50:00.000
2013-10-23 04:55:00.000
2013-10-23 05:00:00.000
2013-10-23 05:05:00.000
2013-10-23 05:10:00.000
2013-10-23 05:15:00.000
2013-10-23 05:20:00.000
2013-10-23 05:25:00.000
2013-10-23 05:30:00.000
2013-10-23 05:35:00.000
2013-10-23 05:40:00.000
2013-10-23 05:45:00.000
2013-10-23 05:50:00.000
2013-10-23 05:55:00.000
2013-10-23 06:00:00.000
2013-10-23 06:05:00.000
2013-10-23 06:10:00.000
2013-10-23 06:15:00.000
2013-10-23 06:20:00.000
2013-10-23 06:25:00.000
2013-10-23 06:30:00.000
2013-10-23 06:35:00.000
2013-10-23 06:40:00.000
2013-10-23 06:45:00.000
2013-10-23 06:50:00.000
2013-10-23 06:55:00.000
2013-10-23 07:00:00.000
2013-10-23 07:05:00.000
2013-10-23 07:10:00.000
2013-10-23 07:15:00.000
2013-10-23 07:20:00.000
2013-10-23 07:25:00.000
2013-10-23 07:30:00.000
2013-10-23 07:35:00.000
2013-10-23 07:40:00.000
2013-10-23 07:45:00.000
2013-10-23 07:50:00.000
2013-10-23 07:55:00.000
2013-10-23 08:00:00.000
2013-10-23 08:05:00.000
2013-10-23 08:10:00.000
2013-10-23 08:15:00.000
2013-10-23 08:20:00.000
2013-10-23 08:25:00.000
2013-10-23 08:30:00.000
2013-10-23 08:35:00.000
2013-10-23 08:40:00.000
2013-10-23 08:45:00.000
2013-10-23 08:50:00.000
2013-10-23 08:55:00.000
2013-10-23 09:00:00.000
2013-10-23 09:05:00.000
2013-10-23 09:10:00.000
2013-10-23 09:15:00.000
2013-10-23 09:20:00.000
2013-10-23 09:25:00.000
2013-10-23 09:30:00.000
2013-10-23 09:35:00.000
2013-10-23 09:40:00.000
2013-10-23 09:45:00.000
2013-10-23 09:50:00.000
2013-10-23 09:55:00.000
2013-10-23 10:00:00.000
2013-10-23 10:05:00.000
2013-10-23 10:10:00.000
2013-10-23 10:15:00.000
2013-10-23 10:20:00.000
2013-10-23 10:25:00.000
2013-10-23 10:30:00.000
2013-10-23 10:35:00.000
2013-10-23 10:40:00.000
2013-10-23 10:45:00.000
2013-10-23 10:50:00.000
2013-10-23 10:55:00.000
2013-10-23 11:00:00.000
2013-10-23 11:05:00.000
2013-10-23 11:10:00.000
2013-10-23 11:15:00.000
2013-10-23 11:20:00.000
2013-10-23 11:25:00.000
2013-10-23 11:30:00.000
2013-10-23 11:35:00.000
2013-10-23 11:40:00.000
2013-10-23 11:45:00.000
2013-10-23 11:50:00.000
2013-10-23 11:55:00.000
2013-10-23 12:00:00.000
2013-10-23 12:05:00.000
2013-10-23 12:10:00.000
2013-10-23 12:15:00.000
2013-10-23 12:20:00.000
2013-10-23 12:25:00.000
2013-10-23 12:30:00.000
2013-10-23 12:35:00.000
2013-10-23 12:40:00.000
2013-10-23 12:45:00.000
2013-10-23 12:50:00.000
2013-10-23 12:55:00.000
2013-10-23 13:00:00.000
2013-10-23 13:05:00.000
2013-10-23 13:10:00.000
2013-10-23 13:15:00.000
2013-10-23 13:20:00.000
2013-10-23 13:25:00.000
2013-10-23 13:30:00.000
2013-10-23 13:35:00.000
2013-10-23 13:40:00.000
2013-10-23 13:45:00.000
2013-10-23 13:50:00.000
2013-10-23 13:55:00.000
2013-10-23 14:00:00.000
2013-10-23 14:05:00.000
2013-10-23 14:10:00.000
2013-10-23 14:15:00.000
2013-10-23 14:20:00.000
2013-10-23 14:25:00.000
2013-10-23 14:30:00.000
2013-10-23 14:35:00.000
2013-10-23 14:40:00.000
2013-10-23 14:45:00.000
2013-10-23 14:50:00.000
2013-10-23 14:55:00.000
2013-10-23 15:00:00.000
2013-10-23 15:05:00.000
2013-10-23 15:10:00.000
2013-10-23 15:15:00.000
2013-10-23 15:20:00.000
2013-10-23 15:25:00.000
2013-10-23 15:30:00.000
2013-10-23 15:35:00.000
2013-10-23 15:40:00.000
2013-10-23 15:45:00.000
2013-10-23 15:50:00.000
2013-10-23 15:55:00.000
2013-10-23 16:00:00.000
2013-10-23 16:05:00.000
2013-10-23 16:10:00.000
2013-10-23 16:15:00.000
2013-10-23 16:20:00.000
2013-10-23 16:25:00.000
2013-10-23 16:30:00.000
2013-10-23 16:35:00.000
2013-10-23 16:40:00.000
2013-10-23 16:45:00.000
2013-10-23 16:50:00.000
2013-10-23 16:55:00.000
2013-10-23 17:00:00.000
2013-10-23 17:05:00.000
2013-10-23 17:10:00.000
2013-10-23 17:15:00.000
2013-10-23 17:20:00.000
2013-10-23 17:25:00.000
2013-10-23 17:30:00.000
2013-10-23 17:35:00.000
2013-10-23 17:40:00.000
2013-10-23 17:45:00.000
2013-10-23 17:50:00.000
2013-10-23 17:55:00.000
2013-10-23 18:00:00.000
2013-10-23 18:05:00.000
2013-10-23 18:10:00.000
2013-10-23 18:15:00.000
2013-10-23 18:20:00.000
2013-10-23 18:25:00.000
2013-10-23 18:30:00.000
2013-10-23 18:35:00.000
2013-10-23 18:40:00.000
2013-10-23 18:45:00.000
2013-10-23 18:50:00.000
2013-10-23 18:55:00.000
2013-10-23 19:00:00.000
2013-10-23 19:05:00.000
2013-10-23 19:10:00.000
2013-10-23 19:15:00.000
2013-10-23 19:20:00.000
2013-10-23 19:25:00.000
2013-10-23 19:30:00.000
2013-10-23 19:35:00.000
2013-10-23 19:40:00.000
2013-10-23 19:45:00.000
2013-10-23 19:50:00.000
2013-10-23 19:55:00.000
2013-10-23 20:00:00.000
2013-10-23 20:05:00.000
2013-10-23 20:10:00.000
2013-10-23 20:15:00.000
2013-10-23 20:20:00.000
2013-10-23 20:25:00.000
2013-10-23 20:30:00.000
2013-10-23 20:35:00.000
2013-10-23 20:40:00.000
2013-10-23 20:45:00.000
2013-10-23 20:50:00.000
2013-10-23 20:55:00.000
2013-10-23 21:00:00.000
2013-10-23 21:05:00.000
2013-10-23 21:10:00.000
2013-10-23 21:15:00.000
2013-10-23 21:20:00.000
2013-10-23 21:25:00.000
2013-10-23 21:30:00.000
2013-10-23 21:35:00.000
2013-10-23 21:40:00.000
2013-10-23 21:45:00.000
2013-10-23 21:50:00.000
2013-10-23 21:55:00.000
2013-10-23 22:00:00.000
2013-10-23 22:05:00.000
2013-10-23 22:10:00.000
2013-10-23 22:15:00.000
2013-10-23 22:20:00.000
2013-10-23 22:25:00.000
2013-10-23 22:30:00.000
2013-10-23 22:35:00.000
2013-10-23 22:40:00.000
2013-10-23 22:45:00.000
2013-10-23 22:50:00.000
2013-10-23 22:55:00.000
2013-10-23 23:00:00.000
2013-10-23 23:05:00.000
2013-10-23 23:10:00.000
2013-10-23 23:15:00.000
2013-10-23 23:20:00.000
2013-10-23 23:25:00.000
2013-10-23 23:30:00.000
2013-10-23 23:35:00.000
2013-10-23 23:40:00.000
2013-10-23 23:45:00.000
2013-10-23 23:50:00.000
2013-10-23 23:55:00.000
LongRui888 2013-10-23
  • 打赏
  • 举报
 回复
引用 楼主 mmm306306 的回复:


create table #aaaa
(
	datet datetime,
	value varchar(11)
);

insert into #aaaa values ('2013-10-23 09:00','0.5');
insert into #aaaa values ('2013-10-23 09:05','0.3');
insert into #aaaa values ('2013-10-23 09:10','0.5');
insert into #aaaa values ('2013-10-23 09:15','0.6');
insert into #aaaa values ('2013-10-23 09:20','0.1');
insert into #aaaa values ('2013-10-23 09:20','0.1');

insert into #aaaa values ('2013-10-23 09:30','0.2');
insert into #aaaa values ('2013-10-23 10:00','0.9');
insert into #aaaa values ('2013-10-23 10:05','0.3');
insert into #aaaa values ('2013-10-23 10:10','0.3');

insert into #aaaa values ('2013-10-23 10:20','0.3');

select * from #aaaa;



drop table #aaaa;
以上是我提供的测试SQL. 现在在 临时表中的 datet 数据应该是每5分钟就有一条数据。但是实际上表中的数据部是以每5分钟存储的。 请问大家，如何查询出的数据是以每5分钟为一条的数据。 比如上面 没有 10:15的数据，那么查询出的结果 10:15对应的value 是0 。 数据有可能是跨好几天的数据查询，求方案。。
通过递归来来生成数据，不需要辅助表： 

;with time_interval
as
(
select 0 as number

union all

select number + 1
from time_interval 
where number < 20000
)


select dateadd(minute,number * 5,'2013-10-23 00:00')
from time_interval
where dateadd(minute,number * 5,'2013-10-23 00:00') < '2013-10-24 00:00:00'
option(maxrecursion 20000)
發糞塗牆 2013-10-23
  • 打赏
  • 举报
 回复
你自己补上
--初始化100条数据
CREATE TABLE #t (datet DATETIME,id INT IDENTITY(1,1))
INSERT INTO #t(datet) VALUES('2013-10-23 09:00')
DECLARE @i INT
SET @i=1 
WHILE @i<=100
BEGIN
	INSERT INTO #t(datet)
	SELECT DATEADD(mi,5,datet)
	FROM #t
	WHERE @i=id+1
	SET @i=@i+1
END 
 
 --DROP TABLE #T
 SELECT *
 FROM #aaaa a right JOIN #t t ON t.datet = a.datet 
 /*
 datet                   value       datet                   id
----------------------- ----------- ----------------------- -----------
2013-10-23 09:00:00.000 0.5         2013-10-23 09:00:00.000 1
2013-10-23 09:05:00.000 0.3         2013-10-23 09:05:00.000 2
2013-10-23 09:10:00.000 0.5         2013-10-23 09:10:00.000 3
2013-10-23 09:15:00.000 0.6         2013-10-23 09:15:00.000 4
2013-10-23 09:20:00.000 0.1         2013-10-23 09:20:00.000 5
2013-10-23 09:20:00.000 0.1         2013-10-23 09:20:00.000 5
NULL                    NULL        2013-10-23 09:25:00.000 6
2013-10-23 09:30:00.000 0.2         2013-10-23 09:30:00.000 7
NULL                    NULL        2013-10-23 09:35:00.000 8
NULL                    NULL        2013-10-23 09:40:00.000 9
NULL                    NULL        2013-10-23 09:45:00.000 10
NULL                    NULL        2013-10-23 09:50:00.000 11
NULL                    NULL        2013-10-23 09:55:00.000 12
2013-10-23 10:00:00.000 0.9         2013-10-23 10:00:00.000 13
2013-10-23 10:05:00.000 0.3         2013-10-23 10:05:00.000 14
2013-10-23 10:10:00.000 0.3         2013-10-23 10:10:00.000 15
NULL                    NULL        2013-10-23 10:15:00.000 16
2013-10-23 10:20:00.000 0.3         2013-10-23 10:20:00.000 17
NULL                    NULL        2013-10-23 10:25:00.000 18
NULL                    NULL        2013-10-23 10:30:00.000 19
NULL                    NULL        2013-10-23 10:35:00.000 20
NULL                    NULL        2013-10-23 10:40:00.000 21
NULL                    NULL        2013-10-23 10:45:00.000 22
NULL                    NULL        2013-10-23 10:50:00.000 23
NULL                    NULL        2013-10-23 10:55:00.000 24
NULL                    NULL        2013-10-23 11:00:00.000 25
NULL                    NULL        2013-10-23 11:05:00.000 26
NULL                    NULL        2013-10-23 11:10:00.000 27
NULL                    NULL        2013-10-23 11:15:00.000 28
NULL                    NULL        2013-10-23 11:20:00.000 29
NULL                    NULL        2013-10-23 11:25:00.000 30
NULL                    NULL        2013-10-23 11:30:00.000 31
NULL                    NULL        2013-10-23 11:35:00.000 32
NULL                    NULL        2013-10-23 11:40:00.000 33
NULL                    NULL        2013-10-23 11:45:00.000 34
NULL                    NULL        2013-10-23 11:50:00.000 35
NULL                    NULL        2013-10-23 11:55:00.000 36
NULL                    NULL        2013-10-23 12:00:00.000 37
NULL                    NULL        2013-10-23 12:05:00.000 38
NULL                    NULL        2013-10-23 12:10:00.000 39
NULL                    NULL        2013-10-23 12:15:00.000 40
NULL                    NULL        2013-10-23 12:20:00.000 41
NULL                    NULL        2013-10-23 12:25:00.000 42
NULL                    NULL        2013-10-23 12:30:00.000 43
NULL                    NULL        2013-10-23 12:35:00.000 44
NULL                    NULL        2013-10-23 12:40:00.000 45
NULL                    NULL        2013-10-23 12:45:00.000 46
NULL                    NULL        2013-10-23 12:50:00.000 47
NULL                    NULL        2013-10-23 12:55:00.000 48
NULL                    NULL        2013-10-23 13:00:00.000 49
NULL                    NULL        2013-10-23 13:05:00.000 50
NULL                    NULL        2013-10-23 13:10:00.000 51
NULL                    NULL        2013-10-23 13:15:00.000 52
NULL                    NULL        2013-10-23 13:20:00.000 53
NULL                    NULL        2013-10-23 13:25:00.000 54
NULL                    NULL        2013-10-23 13:30:00.000 55
NULL                    NULL        2013-10-23 13:35:00.000 56
NULL                    NULL        2013-10-23 13:40:00.000 57
NULL                    NULL        2013-10-23 13:45:00.000 58
NULL                    NULL        2013-10-23 13:50:00.000 59
NULL                    NULL        2013-10-23 13:55:00.000 60
NULL                    NULL        2013-10-23 14:00:00.000 61
NULL                    NULL        2013-10-23 14:05:00.000 62
NULL                    NULL        2013-10-23 14:10:00.000 63
NULL                    NULL        2013-10-23 14:15:00.000 64
NULL                    NULL        2013-10-23 14:20:00.000 65
NULL                    NULL        2013-10-23 14:25:00.000 66
NULL                    NULL        2013-10-23 14:30:00.000 67
NULL                    NULL        2013-10-23 14:35:00.000 68
NULL                    NULL        2013-10-23 14:40:00.000 69
NULL                    NULL        2013-10-23 14:45:00.000 70
NULL                    NULL        2013-10-23 14:50:00.000 71
NULL                    NULL        2013-10-23 14:55:00.000 72
NULL                    NULL        2013-10-23 15:00:00.000 73
NULL                    NULL        2013-10-23 15:05:00.000 74
NULL                    NULL        2013-10-23 15:10:00.000 75
NULL                    NULL        2013-10-23 15:15:00.000 76
NULL                    NULL        2013-10-23 15:20:00.000 77
NULL                    NULL        2013-10-23 15:25:00.000 78
NULL                    NULL        2013-10-23 15:30:00.000 79
NULL                    NULL        2013-10-23 15:35:00.000 80
NULL                    NULL        2013-10-23 15:40:00.000 81
NULL                    NULL        2013-10-23 15:45:00.000 82
NULL                    NULL        2013-10-23 15:50:00.000 83
NULL                    NULL        2013-10-23 15:55:00.000 84
NULL                    NULL        2013-10-23 16:00:00.000 85
NULL                    NULL        2013-10-23 16:05:00.000 86
NULL                    NULL        2013-10-23 16:10:00.000 87
NULL                    NULL        2013-10-23 16:15:00.000 88
NULL                    NULL        2013-10-23 16:20:00.000 89
NULL                    NULL        2013-10-23 16:25:00.000 90
NULL                    NULL        2013-10-23 16:30:00.000 91
NULL                    NULL        2013-10-23 16:35:00.000 92
NULL                    NULL        2013-10-23 16:40:00.000 93
NULL                    NULL        2013-10-23 16:45:00.000 94
NULL                    NULL        2013-10-23 16:50:00.000 95
NULL                    NULL        2013-10-23 16:55:00.000 96
NULL                    NULL        2013-10-23 17:00:00.000 97
NULL                    NULL        2013-10-23 17:05:00.000 98
NULL                    NULL        2013-10-23 17:10:00.000 99
NULL                    NULL        2013-10-23 17:15:00.000 100
 */
马老虎 2013-10-23
  • 打赏
  • 举报
 回复
引用 1 楼 DBA_Huangzj 的回复:
搞个参照表来匹配就可以了
引用 2 楼 SmithLiu328 的回复:
做一张参照表，每5分钟一条记录
谢谢2位大婶(神)。 唉，我考虑的也是这个办法，看看其他大婶 有木有其他方案
KevinLiu 2013-10-23
  • 打赏
  • 举报
 回复
做一张参照表，每5分钟一条记录
發糞塗牆 2013-10-23
  • 打赏
  • 举报
 回复
搞个参照表来匹配就可以了
--小F-- 2013-10-23
  • 打赏
  • 举报
 回复 

dateadd需要改成
DATE_ADD("2013-10-23 09:00:00", INTERVAL 5 MINUTE)
發糞塗牆 2013-10-23
  • 打赏
  • 举报
 回复
我的代码在mysql应该没问题
马老虎 2013-10-23
  • 打赏
  • 举报
 回复
引用 7 楼 hdhai9451 的回复:
create table #aaaa
(
	datet datetime,
	value varchar(11)
);

insert into #aaaa values ('2013-10-23 09:00','0.5');
insert into #aaaa values ('2013-10-23 09:05','0.3');
insert into #aaaa values ('2013-10-23 09:10','0.5');
insert into #aaaa values ('2013-10-23 09:15','0.6');
insert into #aaaa values ('2013-10-23 09:20','0.1');
insert into #aaaa values ('2013-10-23 09:20','0.1');
insert into #aaaa values ('2013-10-23 09:30','0.2');
insert into #aaaa values ('2013-10-23 10:00','0.9');
insert into #aaaa values ('2013-10-23 10:05','0.3');
insert into #aaaa values ('2013-10-23 10:10','0.3');
insert into #aaaa values ('2013-10-23 10:20','0.3');

select a.datet,b.value 
from 
	(select datet=DATEADD(minute,number*5,datet)
	from (select MIN(datet) as datet from #aaaa)a,master..spt_values
	where type='P' and number<=100
	)a 
left join #aaaa b on convert(varchar(16),a.datet,120)=convert(varchar(16),b.datet,120)
where exists(select 1 from #aaaa t where CONVERT(varchar(10),a.datet,120)=CONVERT(varchar(10),t.datet,120))

/*
datet	value
2013-10-23 09:00:00.000	0.5
2013-10-23 09:05:00.000	0.3
2013-10-23 09:10:00.000	0.5
2013-10-23 09:15:00.000	0.6
2013-10-23 09:20:00.000	0.1
2013-10-23 09:20:00.000	0.1
2013-10-23 09:25:00.000	NULL
2013-10-23 09:30:00.000	0.2
2013-10-23 09:35:00.000	NULL
2013-10-23 09:40:00.000	NULL
2013-10-23 09:45:00.000	NULL
2013-10-23 09:50:00.000	NULL
2013-10-23 09:55:00.000	NULL
2013-10-23 10:00:00.000	0.9
2013-10-23 10:05:00.000	0.3
2013-10-23 10:10:00.000	0.3
2013-10-23 10:15:00.000	NULL
2013-10-23 10:20:00.000	0.3
2013-10-23 10:25:00.000	NULL
2013-10-23 10:30:00.000	NULL
2013-10-23 10:35:00.000	NULL
2013-10-23 10:40:00.000	NULL
2013-10-23 10:45:00.000	NULL
2013-10-23 10:50:00.000	NULL
2013-10-23 10:55:00.000	NULL
2013-10-23 11:00:00.000	NULL
2013-10-23 11:05:00.000	NULL
2013-10-23 11:10:00.000	NULL
2013-10-23 11:15:00.000	NULL
2013-10-23 11:20:00.000	NULL
2013-10-23 11:25:00.000	NULL
2013-10-23 11:30:00.000	NULL
2013-10-23 11:35:00.000	NULL
2013-10-23 11:40:00.000	NULL
2013-10-23 11:45:00.000	NULL
2013-10-23 11:50:00.000	NULL
2013-10-23 11:55:00.000	NULL
2013-10-23 12:00:00.000	NULL
2013-10-23 12:05:00.000	NULL
2013-10-23 12:10:00.000	NULL
2013-10-23 12:15:00.000	NULL
2013-10-23 12:20:00.000	NULL
2013-10-23 12:25:00.000	NULL
2013-10-23 12:30:00.000	NULL
2013-10-23 12:35:00.000	NULL
2013-10-23 12:40:00.000	NULL
2013-10-23 12:45:00.000	NULL
2013-10-23 12:50:00.000	NULL
2013-10-23 12:55:00.000	NULL
2013-10-23 13:00:00.000	NULL
2013-10-23 13:05:00.000	NULL
2013-10-23 13:10:00.000	NULL
2013-10-23 13:15:00.000	NULL
2013-10-23 13:20:00.000	NULL
2013-10-23 13:25:00.000	NULL
2013-10-23 13:30:00.000	NULL
2013-10-23 13:35:00.000	NULL
2013-10-23 13:40:00.000	NULL
2013-10-23 13:45:00.000	NULL
2013-10-23 13:50:00.000	NULL
2013-10-23 13:55:00.000	NULL
2013-10-23 14:00:00.000	NULL
2013-10-23 14:05:00.000	NULL
2013-10-23 14:10:00.000	NULL
2013-10-23 14:15:00.000	NULL
2013-10-23 14:20:00.000	NULL
2013-10-23 14:25:00.000	NULL
2013-10-23 14:30:00.000	NULL
2013-10-23 14:35:00.000	NULL
2013-10-23 14:40:00.000	NULL
2013-10-23 14:45:00.000	NULL
2013-10-23 14:50:00.000	NULL
2013-10-23 14:55:00.000	NULL
2013-10-23 15:00:00.000	NULL
2013-10-23 15:05:00.000	NULL
2013-10-23 15:10:00.000	NULL
2013-10-23 15:15:00.000	NULL
2013-10-23 15:20:00.000	NULL
2013-10-23 15:25:00.000	NULL
2013-10-23 15:30:00.000	NULL
2013-10-23 15:35:00.000	NULL
2013-10-23 15:40:00.000	NULL
2013-10-23 15:45:00.000	NULL
2013-10-23 15:50:00.000	NULL
2013-10-23 15:55:00.000	NULL
2013-10-23 16:00:00.000	NULL
2013-10-23 16:05:00.000	NULL
2013-10-23 16:10:00.000	NULL
2013-10-23 16:15:00.000	NULL
2013-10-23 16:20:00.000	NULL
2013-10-23 16:25:00.000	NULL
2013-10-23 16:30:00.000	NULL
2013-10-23 16:35:00.000	NULL
2013-10-23 16:40:00.000	NULL
2013-10-23 16:45:00.000	NULL
2013-10-23 16:50:00.000	NULL
2013-10-23 16:55:00.000	NULL
2013-10-23 17:00:00.000	NULL
2013-10-23 17:05:00.000	NULL
2013-10-23 17:10:00.000	NULL
2013-10-23 17:15:00.000	NULL
2013-10-23 17:20:00.000	NULL
*/
各位大婶，如果mysql 好解决不？
Oracle11g客户端精简版
2. **SQL*Plus**：这是一个命令行工具，允许用户直接输入SQL语句进行查询、更新、管理数据库。它是数据库管理员日常工作的得力助手，支持脚本执行和报告生成。 3. **Oracle Developer Tools**：这些工具包括ODAC...
慢SQL解决方案
慢SQL是稳定性中重要的一部分，一般指查询时间超过1s或扫描行数百万的SQL操作，可以通过show variables like 'long_query_time'查看。对于交易这类对稳定性要求很高的业务来说治理慢SQL格外重要。这里总结下交易中...
EXPLAIN分析：如何让你的SQL查询更高效？
本文介绍了如何使用 EXPLAIN 命令分析 SQL 查询的执行计划，帮助开发者识别查询中的性能瓶颈。文章首先讲解了 EXPLAIN 的基本使用方法，接着详细分析了其返回字段的含义，如读取顺序、索引使用情况、查询成本等。...
sql跨表查询的三种方案
sql跨表查询的三种方案
SQL查询优化：如何提高查询效率
SQL查询优化可以应用于各种场景，例如查询大量数据的、查询历史记录数据的、查询大量关联数据的等。在实际应用场景中，需要根据具体的情况选择合适的技术方案。
疑难问题

22,301

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章