一年有365天 每天都有数据 我只想显示 每隔十天的数据

Question

一年有365天 每天都有数据 我只想显示 每隔十天的数据
这个oracle 语句咋写？请各位大侠指教指教。

Answer 1

with t as
(
    select trunc(sysdate,'yyyy') + rownum - 1 as sdate from dual
    connect by rownum <= trunc(sysdate,'yyyy') + interval'1'year - trunc(sysdate,'yyyy')
)
select sdate
  from (select sdate, rownum as rn from t
         where sdate >= trunc(sysdate))
 where mod(rn, 10) = 1;

Answer 2

with t as
(
    select trunc(sysdate,'yyyy') + rownum - 1 as sdate from dual
    connect by rownum <= trunc(sysdate,'yyyy') + interval'1'year - trunc(sysdate,'yyyy')
)
select sdate
  FROM t
wHERE mod(trunc((SYSDATE - sdate)/10),2)=0
;

Answer 3

你需求说的还是不清不楚的with t as
(
    select trunc(sysdate,'yyyy') + rownum - 1 as ddate from dual
    connect by rownum <= trunc(sysdate,'yyyy') + interval'1'year - trunc(sysdate,'yyyy')
)
select ddate from t
 where mod(to_number(to_char(ddate,'ddd')), 20) <= 10;
DDATE
-----------
2011-1-1
2011-1-2
2011-1-3
2011-1-4
2011-1-5
2011-1-6
2011-1-7
2011-1-8
2011-1-9
2011-1-10
2011-1-20
2011-1-21
2011-1-22
2011-1-23
2011-1-24
2011-1-25
2011-1-26
2011-1-27
2011-1-28
2011-1-29
2011-1-30
2011-2-9
2011-2-10
2011-2-11
2011-2-12
......

Answer 4

假设你每天数据是增量插入的declare
       v_accdate date;
       v_day     char(2);
begin
       v_accdate:=sysdate;--使用系统时间
       v_day:=subStr(to_char(v_accdate,'yyyymmdd'),7,2);
       if v_day in ('01','11','21')
       then EXECUTE IMMEDIATE 'SELECT * FROM TAB';
       END IF;
END;
/

Answer 5

-- 打印出从今天的第一天开始每隔10天的日期，然后去连接你的原表就OK啦：
scott@TBWORA> select trunc(sysdate,'yyyy')+level*10-1
  2  from dual
  3  connect by level<=36;TRUNC(SYSDATE,'YYYY
-------------------
2011-01-10 00:00:00
2011-01-20 00:00:00
2011-01-30 00:00:00
2011-02-09 00:00:00
2011-02-19 00:00:00
2011-03-01 00:00:00
2011-03-11 00:00:00
2011-03-21 00:00:00
2011-03-31 00:00:00
2011-04-10 00:00:00
2011-04-20 00:00:00
2011-04-30 00:00:00
2011-05-10 00:00:00
2011-05-20 00:00:00
2011-05-30 00:00:00
2011-06-09 00:00:00
2011-06-19 00:00:00
2011-06-29 00:00:00
2011-07-09 00:00:00
2011-07-19 00:00:00
2011-07-29 00:00:00
2011-08-08 00:00:00
2011-08-18 00:00:00
2011-08-28 00:00:00
2011-09-07 00:00:00
2011-09-17 00:00:00
2011-09-27 00:00:00
2011-10-07 00:00:00
2011-10-17 00:00:00
2011-10-27 00:00:00
2011-11-06 00:00:00
2011-11-16 00:00:00
2011-11-26 00:00:00
2011-12-06 00:00:00
2011-12-16 00:00:00
2011-12-26 00:00:00已选择36行。

Answer 6

scott@TBWORA> select trunc(sysdate,'yyyy')+(level-1)*10
  2  from dual
  3  connect by level<=37;TRUNC(SYSDATE,'YYYY
-------------------
2011-01-01 00:00:00
2011-01-11 00:00:00
2011-01-21 00:00:00
2011-01-31 00:00:00
2011-02-10 00:00:00
2011-02-20 00:00:00
2011-03-02 00:00:00
2011-03-12 00:00:00
2011-03-22 00:00:00
2011-04-01 00:00:00
2011-04-11 00:00:00
2011-04-21 00:00:00
2011-05-01 00:00:00
2011-05-11 00:00:00
2011-05-21 00:00:00
2011-05-31 00:00:00
2011-06-10 00:00:00
2011-06-20 00:00:00
2011-06-30 00:00:00
2011-07-10 00:00:00
2011-07-20 00:00:00
2011-07-30 00:00:00
2011-08-09 00:00:00
2011-08-19 00:00:00
2011-08-29 00:00:00
2011-09-08 00:00:00
2011-09-18 00:00:00
2011-09-28 00:00:00
2011-10-08 00:00:00
2011-10-18 00:00:00
2011-10-28 00:00:00
2011-11-07 00:00:00
2011-11-17 00:00:00
2011-11-27 00:00:00
2011-12-07 00:00:00
2011-12-17 00:00:00
2011-12-27 00:00:00已选择37行。

Answer 7

个人建议 
一个月按30天来算直接用日期来进行，1-10号，11-20号，21-月底--则直接对号数EXTRACT(DAY FROM SYSDATE) 进行分组即可select * 
from tb 
where round(EXTRACT(DAY FROM 日期)/10) = round(EXTRACT(DAY FROM SYSDATE)/10 )--取出当前日期，如果为31号 则转换到21-30号区间里，类似如下
select * 
from tb 
where decode(round(EXTRACT(DAY FROM 日期)/10,4,3) = round(EXTRACT(DAY FROM SYSDATE)/10 )