原来的表排列为
ID TIME TMP
001 2011-11-28 00:00:01 50
002 2011-11-28 00:00:01 59
001 2011-11-28 00:00:02 60
002 2011-11-28 00:00:02 55
...
001 2011-11-28 12:00:01 44
002 2011-11-28 12:00:01 45
到这边数据没有了,下一条数据开始是隔天的数据
001 2011-11-29 00:00:01 68
002 2011-11-29 00:00:01 69
...我已经将它排列成
TIME 001 002
2011-11-28 00:00:01 50 59
2011-11-28 00:00:02 60 55
...
2011-11-28 12:00:01 44 45
到这里为止下一条是
2011-11-29 00:00:01 68 69
而不是2011-11-28 12:00:02 null null
(sql语句:select avg(case id when '001' then (tmp) end ) as '001',
avg(case id when '002' then (tmp) end ) as '002',
time from record
where (time>'2011-11-27 13:39:18' and time < {fn now()})
group by time order by time asc)
我想问的是在2011-11-28 12:00:01 到 2011-11-29 00:00:01 这段时间内不存在数据,我想把这段时间的内容填写为null,该如何实现?
想要的结果:
TIME 001 002
2011-11-28 00:00:01 50 59
2011-11-28 00:00:02 60 55
...
2011-11-28 12:00:01 44 45
2011-11-28 12:00:02 NULL NULL
2011-11-28 12:00:03 NULL NULL
...
到这边才开始继续数据
2011-11-29 00:00:01 68 69
...
ID TIME TMP
001 2011-11-28 00:00:01 50
002 2011-11-28 00:00:01 59
001 2011-11-28 00:00:02 60
002 2011-11-28 00:00:02 55
...
001 2011-11-28 12:00:01 44
002 2011-11-28 12:00:01 45
到这边数据没有了,下一条数据开始是隔天的数据
001 2011-11-29 00:00:01 68
002 2011-11-29 00:00:01 69
...我已经将它排列成
TIME 001 002
2011-11-28 00:00:01 50 59
2011-11-28 00:00:02 60 55
...
2011-11-28 12:00:01 44 45
到这里为止下一条是
2011-11-29 00:00:01 68 69
而不是2011-11-28 12:00:02 null null
(sql语句:select avg(case id when '001' then (tmp) end ) as '001',
avg(case id when '002' then (tmp) end ) as '002',
time from record
where (time>'2011-11-27 13:39:18' and time < {fn now()})
group by time order by time asc)
我想问的是在2011-11-28 12:00:01 到 2011-11-29 00:00:01 这段时间内不存在数据,我想把这段时间的内容填写为null,该如何实现?
想要的结果:
TIME 001 002
2011-11-28 00:00:01 50 59
2011-11-28 00:00:02 60 55
...
2011-11-28 12:00:01 44 45
2011-11-28 12:00:02 NULL NULL
2011-11-28 12:00:03 NULL NULL
...
到这边才开始继续数据
2011-11-29 00:00:01 68 69
...
[id] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[time] [datetime] NULL,
[tmp] [float] NULL
) ON [PRIMARY]