--> 生成测试数据: @tb
DECLARE @tb TABLE (NM varchar(20),TM DATETIME,Data INT)
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80 需要得到的结果是
ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 04:00:00.000 20
ST0100 2008-10-10 08:00:00.000 35
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 16:00:00.000 50
ST0100 2008-10-10 20:00:00.000 60
ST0100 2008-10-11 00:00:00.000 70
ST0100 2008-10-11 04:00:00.000 80 1、不是整点的数据整编为整点
2、如果有重复数据,则取时间大的记录感谢小F ,josy,己解决,解决办法如下:select
NM,
convert(varchar(13),TM,120)+':00:00.000' as TM,
Data
from
@tb t
where
not exists(select 1
from @tb
where NM=t.NM
and convert(varchar(13),TM,120)=convert(varchar(13),t.TM,120)
and TM>t.TM --如果有重复数据,则取时间大的记录
)接下来的问题是:
不但要整编,还要能补充完整的时间列表显示ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 02:00:00.000 null
ST0100 2008-10-10 04:00:00.000 20
ST0100 2008-10-10 06:00:00.000 null
ST0100 2008-10-10 08:00:00.000 35
ST0100 2008-10-10 10:00:00.000 null
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 14:00:00.000 null
ST0100 2008-10-10 16:00:00.000 50
ST0100 2008-10-10 18:00:00.000 null
ST0100 2008-10-10 20:00:00.000 60
ST0100 2008-10-10 22:00:00.000 null
ST0100 2008-10-11 00:00:00.000 70 每1小时,每2小时,每4小时,或任意指定间隔的列表显示,如果存在该时间记录就显示,不存在该时间的就为null
DECLARE @tb TABLE (NM varchar(20),TM DATETIME,Data INT)
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80 需要得到的结果是
ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 04:00:00.000 20
ST0100 2008-10-10 08:00:00.000 35
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 16:00:00.000 50
ST0100 2008-10-10 20:00:00.000 60
ST0100 2008-10-11 00:00:00.000 70
ST0100 2008-10-11 04:00:00.000 80 1、不是整点的数据整编为整点
2、如果有重复数据,则取时间大的记录感谢小F ,josy,己解决,解决办法如下:select
NM,
convert(varchar(13),TM,120)+':00:00.000' as TM,
Data
from
@tb t
where
not exists(select 1
from @tb
where NM=t.NM
and convert(varchar(13),TM,120)=convert(varchar(13),t.TM,120)
and TM>t.TM --如果有重复数据,则取时间大的记录
)接下来的问题是:
不但要整编,还要能补充完整的时间列表显示ST0100 2008-10-10 00:00:00.000 10
ST0100 2008-10-10 02:00:00.000 null
ST0100 2008-10-10 04:00:00.000 20
ST0100 2008-10-10 06:00:00.000 null
ST0100 2008-10-10 08:00:00.000 35
ST0100 2008-10-10 10:00:00.000 null
ST0100 2008-10-10 12:00:00.000 40
ST0100 2008-10-10 14:00:00.000 null
ST0100 2008-10-10 16:00:00.000 50
ST0100 2008-10-10 18:00:00.000 null
ST0100 2008-10-10 20:00:00.000 60
ST0100 2008-10-10 22:00:00.000 null
ST0100 2008-10-11 00:00:00.000 70 每1小时,每2小时,每4小时,或任意指定间隔的列表显示,如果存在该时间记录就显示,不存在该时间的就为null
参考:
http://topic.csdn.net/u/20090906/17/a91cd31e-abf7-4190-8486-c5491aa63c71.html?seed=1380177511&r=59598572#r_59598572
from master..spt_values
where type='p'
and number between 0 and 23
and number%2=0 --隔几就%几
DECLARE @tb TABLE (NM varchar(20),TM DATETIME,Data INT)
INSERT INTO @tb
SELECT'ST0100', '2008-10-10 00:23:00',10 UNION ALL
SELECT 'ST0100','2008-10-10 04:22:00',20 UNION ALL
SELECT'ST0100', '2008-10-10 08:05:00',30 UNION ALL
SELECT 'ST0100','2008-10-10 08:35:00',35 UNION ALL
SELECT 'ST0100','2008-10-10 12:00:00',40 UNION ALL
SELECT 'ST0100','2008-10-10 16:30:00',50 UNION ALL
SELECT'ST0100', '2008-10-10 20:48:00',60 UNION ALL
SELECT 'ST0100','2008-10-11 00:50:00',70 union ALL
SELECT 'ST0100','2008-10-11 04:20:00',80
select isnull(b.nm,'ST0100') as nm,isnull(b.tm,'2008-10-10 '+a.tm+':00:00:000') as tm,b.data
from
(select tm=right(100+number,2)
from master..spt_values
where type='p'
and number between 0 and 23
and number%2=0) a
left join
(select
NM,
convert(varchar(13),TM,120)+':00:00.000' as TM,
Data
from
@tb t
where
not exists(select 1
from @tb
where NM=t.NM
and convert(varchar(13),TM,120)=convert(varchar(13),t.TM,120)
and TM>t.TM --如果有重复数据,则取时间大的记录
)
) b
on a.tm=datepart(Hh,b.tm)
order by tm这样有个问题,测站的名称,和那个时间,就固定了