如果是日期字段包括了时间:select 站号,日期,雨量
from 降雨量表 a
where 日期>dateadd(day,-5,convert(char(10),getdate(),111))
and not exists (
select 1 from 降雨量表
where 站号=a.站号
and convert(char(10),日期,111)=convert(char(10),a.日期,111)
and 日期<a.日期
)
from 降雨量表 a
where 日期>dateadd(day,-5,convert(char(10),getdate(),111))
and not exists (
select 1 from 降雨量表
where 站号=a.站号
and convert(char(10),日期,111)=convert(char(10),a.日期,111)
and 日期<a.日期
)
稍微改下就可以select 站号,日期,雨量
from 降雨量表 a
where 日期>dateadd(day,-5,convert(char(10),dateadd(hour,-6,getdate()),111))
and not exists (
select 1 from 降雨量表
where 站号=a.站号
and convert(char(10),dateadd(hour,-6,日期),111)=convert(char(10),dateadd(hour,-6,a.日期),111)
and 日期<a.日期
)
insert into tb(pid,dt,value) values(1, '2007-05-10 06:00:00', 100)
insert into tb(pid,dt,value) values(1, '2007-05-10 08:00:00', 0)
insert into tb(pid,dt,value) values(1, '2007-05-10 14:00:00', 200)
insert into tb(pid,dt,value) values(1, '2007-05-10 20:00:00', 220)
insert into tb(pid,dt,value) values(1, '2007-05-09 14:00:00', 300)
insert into tb(pid,dt,value) values(1, '2007-05-09 20:00:00', 30)
insert into tb(pid,dt,value) values(1, '2007-05-08 08:00:00', 300)
insert into tb(pid,dt,value) values(1, '2007-05-08 14:00:00', 30)
insert into tb(pid,dt,value) values(1, '2007-05-08 20:00:00', 200)
insert into tb(pid,dt,value) values(1, '2007-05-07 06:00:00', 30)
insert into tb(pid,dt,value) values(1, '2007-05-07 08:00:00', 300)
insert into tb(pid,dt,value) values(1, '2007-05-07 20:00:00', 0)
insert into tb(pid,dt,value) values(1, '2007-05-06 08:00:00', 100)
insert into tb(pid,dt,value) values(1, '2007-05-06 14:00:00', 20)select pid,dt,value into #t from (select pid,case when datepart(hour,dt)=6 or datepart(hour,dt)=8 then dt else null end as dt, value
from tb a
where dt>dateadd(day,-4,convert(char(10),getdate(),111)) and pid=1) b where b.dt is not null select pid as 站号,dt as 日期,value as 雨量 from #t A where (select count(*) from #t where datepart(day,dt)=datepart(day,a.dt) and dt<a.dt ) =0drop table tb
drop table #t
/*
----------------------------
站号 日期 雨量1 2007-05-10 06:00:00.000 100
1 2007-05-08 08:00:00.000 300
1 2007-05-07 06:00:00.000 30
----------------------------
*/不知道是不是要这样的?
站号 日期 雨量
60114700
60114700 2007-05-05 08:00:00.000 0
60114700
60114700
60114700
60114700 2007-05-06 08:00:00.000 0
60114700
60114700
60114700
60114700 2007-05-07 08:00:00.000 0
60114700
60114700
60114700
60114700 2007-05-08 08:00:00.000 0
60114700
60114700
60114700
60114700 2007-05-09 08:00:00.000 0
60114700
60114700
60114700
60114700 重复出现空记录