如何用一条SQL语句实现按某一时间间隔查询？（在线等待！！！）

[godzi]

关键字为两个字段：时间、××名称；
比如实现按时间字段每隔三天取数据！

[txlicenhe]

datediff(day,时间,@开始时间)/3

[adinna]

select * from tablename where DATEDIFF(day, 时间, getdate()) = 3

[godzi]

不好意思，发错了地方，我这边是oracle，好像有的函数不认吧

[dafu71]

select 时间,名称 from tablename group by datediff(day,时间,@开始时间)/3,名称

[zjcxc]

SQL中就用:
datediff(day,时间,@开始时间)/3=0

oracle就转版

[zjcxc]

--上面写错了,应该是:
where datediff(day,时间,@开始时间)%3=0

下面是例子:
select * from(
select 日期=dateadd(day,a.id+b.id,getdate())
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 4
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 40
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
) a where datediff(day,日期,getdate())%3=0

/*--测试结果
日期
------------------------------------------------------
2004-01-12 11:00:45.087
2004-01-15 11:00:45.087
2004-01-18 11:00:45.087
2004-01-21 11:00:45.087
2004-01-24 11:00:45.087
2004-01-30 11:00:45.087
2004-02-02 11:00:45.087
2004-02-05 11:00:45.087
2004-02-05 11:00:45.087
2004-02-08 11:00:45.087
2004-02-11 11:00:45.087
2004-02-14 11:00:45.087
2004-02-17 11:00:45.087
2004-02-20 11:00:45.087
2004-02-23 11:00:45.087
2004-02-29 11:00:45.087
2004-02-23 11:00:45.087
2004-02-29 11:00:45.087
2004-03-12 11:00:45.087
2004-03-15 11:00:45.087
2004-03-18 11:00:45.087
2004-03-21 11:00:45.087
2004-03-24 11:00:45.087
2004-03-30 11:00:45.087
2004-04-02 11:00:45.087
2004-04-05 11:00:45.087
2004-04-05 11:00:45.087
2004-04-08 11:00:45.087
2004-04-11 11:00:45.087
2004-04-14 11:00:45.087
2004-04-17 11:00:45.087
2004-04-20 11:00:45.087

（所影响的行数为 32 行）


--*/

[adinna]

oracle 我记得直接减就可以了
默认就是用天的

select

[adinna]

sorry
一不小心点了按钮了

select * from tablename where (时间-开始时间)%3 = 0

[godzi]

好像在oracle中取余不是用的%,也不是mod啊

[csj43269]

这么多高手，逛顶。。。。

[godzi]

找到了
select distinct rq from table where mod((rq-to_date('20031112','yyyy-mm-dd')),3) = 0

谢谢各位！

结帖！

[leeboyan]

没用过，帮你顶！