34,590
社区成员
发帖
与我相关
我的任务
分享
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 --如果有重复数据,则取时间大的记录
)
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
select tm=right(100+number,2)+':00'
from master..spt_values
where type='p'
and number between 0 and 23
and number%2=0 --隔几就%几