首页 新闻 会员 周边 捐助

sql查一小时内60分钟的数据 把缺少的补全

0
悬赏园豆：10 [已解决问题] 解决于 2016-01-18 15:06

数据表Table 字段有【CreateTime】【CrteaterId】【AvgValue】

在正常的情况下，一分钟向Table 插入一条数据，查询一个小时内的数据应该有60条。

查询结果如下：

【CreateTime】【CrteaterId】【AvgValue】

2015-11-17 09:00:00　　01　　100.12

2015-11-17 09:01:00　　01　　100.12

2015-11-17 09:02:00　　01　　100.12

.....

2015-11-17 09:59:00　　01　　100.12

 

但是由于特殊原因，会有数据丢失的情况，比如：02分钟的数据丢失了，所以想造一条丢失的记录，只要把值变成0即可，形成完整的60条记录。

2015-11-17 09:00:00　　01　　100.12

2015-11-17 09:01:00　　01　　100.12

2015-11-17 09:02:00　　01　　100.12　　2015-11-17 09:02:00　　01　　0.00

.....

2015-11-17 09:59:00　　01　　100.12

cppwen的主页 cppwen | 初学一级 | 园豆：55
提问于：2015-11-17 13:55
< >
分享
最佳答案
1 
DECLARE @t TABLE(createtime DATETIME,v int);

INSERT INTO @T (createtime,v) VALUES
('2015-11-19 09:00:00',1),
('2015-11-19 09:01:00',1),
('2015-11-19 09:02:00',1),
('2015-11-19 09:03:00',1),
('2015-11-19 09:05:00',1),
('2015-11-19 09:06:00',1),
('2015-11-19 09:07:00',1),
('2015-11-19 09:59:00',1)


; with cte as
(
select n
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(n)
)
,cte_h as 
(
SELECT a.n+b.n*10 as n
from cte a
cross join cte b
where a.n+b.n*10<60
)

merge @t t
using cte_h s
    on DATEPART(minute,t.createtime)=s.n 

when not MATCHED
    then insert (createtime,v)
    values('2015-11-19 09:'+cast(n as varchar(2))+':00',1);


select *
from @t
order by createtime


你看看这段代码，适当修改一下，能否解决你的问题？

 

收获园豆：10
悦光阴 | 老鸟四级 |园豆：2251 | 2015-11-23 11:07
其他回答(2)
-1

那就造啊，没有人挡住你不让你做吧？找人砍他？

爱编程的大叔 | 园豆：30844 (高人七级) | 2015-11-17 14:27

是要形成完整的60条记录

支持(0) 反对(0) cppwen | 园豆：55 (初学一级) | 2015-11-17 14:30

还是大叔幽默

支持(0) 反对(0) 明Lam | 园豆：615 (小虾三级) | 2015-11-17 14:44
0 
DECLARE @t TABLE(createtime DATETIME,v int);

INSERT INTO @T (createtime,v) VALUES
('2015-11-19 09:00:00',1),
('2015-11-19 09:01:00',1),
('2015-11-19 09:02:00',1),
('2015-11-19 09:03:00',1),
('2015-11-19 09:05:00',1),
('2015-11-19 09:06:00',1),
('2015-11-19 09:07:00',1),
('2015-11-19 09:59:00',1),
('2015-11-19 10:10:00',1),
('2015-11-19 10:20:00',1)
;WITH cte AS (
    SELECT r1=DATEDIFF(mi,'2015-11-19 09:00:00',createtime),r2=ROW_NUMBER() OVER(ORDER BY createtime),* FROM @T
), c AS (
SELECT rn=ROW_NUMBER() OVER(ORDER BY r1-r2),r1-r2 AS ct,MAX(createtime) AS createtime FROM cte GROUP BY r1-r2
)
SELECT DATEADD(mi,x.number+1,u.createtime) AS t,0 AS v FROM 
(SELECT b.ct-a.ct AS ti,a.createtime FROM c a JOIN c b ON a.rn=b.rn-1) u
 JOIN master..spt_values x ON x.number<u.ti AND x.TYPE='p'

注意边界的地方，如果时间太长了，spt_values不够可以考虑自己构造一个表来处理。

gw2010 | 园豆：1487 (小虾三级) | 2015-11-19 15:50
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。