写的话WITH A AS (SELECT TO_DATE('2012/10/14','YYYY/MM/DD') AS DATA_DATE, 11 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/15','YYYY/MM/DD') AS DATA_DATE, 12 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/16','YYYY/MM/DD') AS DATA_DATE, 13 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/17','YYYY/MM/DD') AS DATA_DATE, 14 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/18','YYYY/MM/DD') AS DATA_DATE, 15 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/19','YYYY/MM/DD') AS DATA_DATE, 16 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/20','YYYY/MM/DD') AS DATA_DATE, 17 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/21','YYYY/MM/DD') AS DATA_DATE, 18 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/22','YYYY/MM/DD') AS DATA_DATE, 19 AS NUM FROM DUAL UNION ALL SELECT TO_DATE('2012/10/23','YYYY/MM/DD') AS DATA_DATE, 20 AS NUM FROM DUAL )SELECT B.DATA_DATE, SUM(A.NUM) FROM (SELECT LEVEL + TO_DATE('2012/10/13', 'YYYY/MM/DD') AS DATA_DATE FROM DUAL CONNECT BY LEVEL < 10) B, A WHERE B.DATA_DATE BETWEEN (A.DATA_DATE - 10) AND A.DATA_DATE GROUP BY B.DATA_DATE ORDER BY 1;
with temp as (select '2012-03-01' validate_time from dual union all select '2012-03-05' validate_time from dual union all select '2012-03-08' validate_time from dual union all select '2012-03-10' validate_time from dual union all select '2012-03-12' validate_time from dual)select t.validate_time,( select count(1) from temp t1 where to_date(t.validate_time, 'yyyy-MM-dd') <= to_date(t1.validate_time, 'yyyy-MM-dd') + 10 and t1.validate_time > t.validate_time) as sign from temp t楼主这个意思?
select * from (SELECT row_number() over(order by dd desc) as rn, TEST.* FROM TEST WHERE to_char(sysdate, 'DD') >= to_char(to_date(dd, 'YYYY-MM-DD'), 'DD') AND MOD(to_char(sysdate, 'DD'), 10) = 0) where rn <= 10
validate
2012-03-01
2012-03-05
2012-03-08
2012-03-10
2012-03-12
2012-03-01 4 注:1号后的10天有4条记录
2012-03-05 4 注:5号后的10天有4条记录
2012-03-08 3 注:8号后的10天有4条记录
2012-03-10 2 注:10号后的10天有4条记录
2012-03-12 1 注:12号后的10天有4条记录
UNION ALL SELECT TO_DATE('2012/10/15','YYYY/MM/DD') AS DATA_DATE, 12 AS NUM FROM DUAL
UNION ALL SELECT TO_DATE('2012/10/16','YYYY/MM/DD') AS DATA_DATE, 13 AS NUM FROM DUAL
UNION ALL SELECT TO_DATE('2012/10/17','YYYY/MM/DD') AS DATA_DATE, 14 AS NUM FROM DUAL
UNION ALL SELECT TO_DATE('2012/10/18','YYYY/MM/DD') AS DATA_DATE, 15 AS NUM FROM DUAL
UNION ALL SELECT TO_DATE('2012/10/19','YYYY/MM/DD') AS DATA_DATE, 16 AS NUM FROM DUAL
UNION ALL SELECT TO_DATE('2012/10/20','YYYY/MM/DD') AS DATA_DATE, 17 AS NUM FROM DUAL
UNION ALL SELECT TO_DATE('2012/10/21','YYYY/MM/DD') AS DATA_DATE, 18 AS NUM FROM DUAL
UNION ALL SELECT TO_DATE('2012/10/22','YYYY/MM/DD') AS DATA_DATE, 19 AS NUM FROM DUAL
UNION ALL SELECT TO_DATE('2012/10/23','YYYY/MM/DD') AS DATA_DATE, 20 AS NUM FROM DUAL )SELECT B.DATA_DATE, SUM(A.NUM)
FROM (SELECT LEVEL + TO_DATE('2012/10/13', 'YYYY/MM/DD') AS DATA_DATE
FROM DUAL
CONNECT BY LEVEL < 10) B,
A
WHERE B.DATA_DATE BETWEEN (A.DATA_DATE - 10) AND A.DATA_DATE
GROUP BY B.DATA_DATE
ORDER BY 1;
with temp as
(select '2012-03-01' validate_time
from dual
union all
select '2012-03-05' validate_time
from dual
union all
select '2012-03-08' validate_time
from dual
union all
select '2012-03-10' validate_time
from dual
union all
select '2012-03-12' validate_time from dual)select t.validate_time,(
select count(1)
from temp t1
where to_date(t.validate_time, 'yyyy-MM-dd') <= to_date(t1.validate_time, 'yyyy-MM-dd') + 10 and t1.validate_time > t.validate_time) as sign
from temp t楼主这个意思?
from (SELECT row_number() over(order by dd desc) as rn, TEST.*
FROM TEST
WHERE to_char(sysdate, 'DD') >=
to_char(to_date(dd, 'YYYY-MM-DD'), 'DD')
AND MOD(to_char(sysdate, 'DD'), 10) = 0)
where rn <= 10