你是想得到11月一共30天 这一条记录,还是想得到1101--1130条记录? 如果是得到30天,那么 select add_months(trunc(to_date('2011-10-04','yyyy-mm-dd'),'month'),1) - trunc(to_date('2011-10-04','yyyy-mm-dd'),'month') MONTH from dual 如果得到30条记录,那么,将这个结果集connect by
select service_date, nvl(counted, 0) from (SELECT to_date('2011-11-01', 'YYYY-MM-DD') + ROWNUM - 1 service_date FROM dual CONNECT BY ROWNUM <= (to_date('2011-11-30', 'YYYY-MM-DD') - to_date('2011-11-01', 'YYYY-MM-DD') + 1)) a left join (select dateField, count(1) as counted from tableName group by dateField) b on a.service_date = b.dateField
取出对应值是计数?还是什么?用函数取出每天的值,用COUNT计数,加上WHERE.不就可以了.
如果是得到30天,那么
select add_months(trunc(to_date('2011-10-04','yyyy-mm-dd'),'month'),1) - trunc(to_date('2011-10-04','yyyy-mm-dd'),'month') MONTH from dual 如果得到30条记录,那么,将这个结果集connect by
from (SELECT to_date('2011-11-01', 'YYYY-MM-DD') + ROWNUM - 1 service_date
FROM dual
CONNECT BY ROWNUM <= (to_date('2011-11-30', 'YYYY-MM-DD') -
to_date('2011-11-01', 'YYYY-MM-DD') + 1)) a
left join (select dateField, count(1) as counted
from tableName
group by dateField) b on a.service_date = b.dateField