这个sql循环怎么写？

[lyp524]

表a：
a_time a_value
2008-06-01 01:00:00 a
2008-06-01 02:00:00 b
2008-06-01 03:00:00 c
2008-06-01 04:00:00 d
.
.
.
.
2008-06-02 01:00:00 e
2008-06-02 02:00:00 f
2008-06-02 03:00:00 g
2008-06-03 05:00:00 w
2008-06-03 06:00:00 d
2008-06-03 07:00:00 d
2008-06-03 08:00:00 d

理论上a_time字段是每一个小时都会插入记录，但是有可能因为其他原因导致表a在整点时无记录插入。

想要实现是：
给一个变量@tima_begin,例如2008-06-20 02：00：00（事先知道这个时刻没有 记录插入）
返回比@tima_begin时间早而且是最贴近的某一天同一整点有记录的a_time值，
意思就是要以

DATEADD(day,-1@tima_begin)

为间隔往前推，直到找到同一时刻有记录的那天，并返回a_time

[lyp524]

DATEADD(day,-1,@tima_begin)

少写个逗号

[liangCK]

select top 5000 id=identity(int,1,1) into # from syscolumns a,syscolumns b

declare @min datetime
select @min=min(a_time) from tb where a_time<'2008-06-20 02:00:00'

select dateadd(hour,-b.id,'2008-06-20 02:00:00')
from tb a
join # b
on dateadd(hour,-b.id,'2008-06-20 02:00:00')>@min

[lyp524]

看不懂啊

不是用循环吗？
[Quote=引用 2 楼 liangCK 的回复:]
select top 5000 id=identity(int,1,1) into # from syscolumns a,syscolumns b

declare @min datetime
select @min=min(a_time) from tb where a_time <'2008-06-20 02:00:00'

select dateadd(hour,-b.id,'2008-06-20 02:00:00')
from tb a
join # b
on dateadd(hour,-b.id,'2008-06-20 02:00:00')>@min
[/Quote]

[lyp524]

可以稍加 解释一小下吗 尤其第一句

[hanjs]

不用循环吧，通过条件过滤就可以了

declare @t table(a_time varchar(20),a_value varchar(1))
Insert @t
select '2008-06-01 01:00:00',N'a' union all
select '2008-06-01 02:00:00',N'b' union all
select '2008-06-01 03:00:00',N'c' union all
select '2008-06-01 04:00:00',N'd' union all
select '2008-06-02 01:00:00',N'e' union all
select '2008-06-02 02:00:00',N'f' union all
select '2008-06-02 03:00:00',N'g' union all
select '2008-06-03 05:00:00',N'w' union all
select '2008-06-03 06:00:00',N'd' union all
select '2008-06-03 07:00:00',N'd' union all
select '2008-06-03 08:00:00',N'd'

select top 1 * from @t
 where charindex(right('2008-06-20 02:00:00',9),a_time)>0
order by a_time desc

2008-06-02 02:00:00	f

[hanjs]

declare @t table(a_time datetime,a_value varchar(1))
Insert @t
select '2008-06-01 01:00:00',N'a' union all
select '2008-06-01 02:00:00',N'b' union all
select '2008-06-01 03:00:00',N'c' union all
select '2008-06-01 04:00:00',N'd' union all
select '2008-06-02 01:00:00',N'e' union all
select '2008-06-02 02:00:00',N'f' union all
select '2008-06-02 03:00:00',N'g' union all
select '2008-06-03 05:00:00',N'w' union all
select '2008-06-03 06:00:00',N'd' union all
select '2008-06-03 07:00:00',N'd' union all
select '2008-06-03 08:00:00',N'd'


select top 1 * from @t
 where datepart(hh,a_time)=datepart(hh,cast('2008-06-20 02:00:00' as datetime))
order by a_time desc


2008-06-02 02:00:00.000	f

[hanjs]

对了，还需要加上 a_time< 你查询的条件

[hanjs]

对了，还需要加上 a_time< 你查询的条件

[lyp524]

之所以想用循环 是因为 基本上90%的可能性 只要往前推一天就会有相应的数据存在 尔所耗费的资源只是一次select
而如果用order by的话 因为这个表的数据上千万条 排个序的时间绝对等不起

我是这么认为的 不晓得正不正确

[duanzhi1984]

但是循环的话我想更等不起，循环是很浪费时间的。

用语句也是最简便的

select top 1 * from @t
where
a_time <'2008-06-02 02:00:00'

order by a_time desc

[lyp524]

那也不对啊
select top 1 * from @t
where
a_time <'2008-06-02 02:00:00'

order by a_time desc

取得也不是之前的同一时间啊