Question

oracle查询最近一周记录每日统计，如某天无数据则为0，怎样写sql

比如下表
date value
2013-11-07 1
2013-11-06 1
2013-11-05 1
2013-11-05 1
2013-11-03 1
2013-11-03 1
2013-11-02 1
2013-11-01 1
效果如下：
date value
2013-11-07 1
2013-11-06 1
2013-11-05 2
2013-11-04 0
2013-11-03 2
2013-11-02 1
2013-11-01 1

Answer 1

SELECT TO_CHAR(SYSDATE - TO_NUMBER(TO_CHAR(SYSDATE, 'D')) + LEVEL+1, 'YYYY-MM-DD') DAY
  FROM DUAL
CONNECT BY LEVEL <= 7
 ORDER BY DAY

这句获得本周一到周日的日期，剩下左联业务主表 统计就好

Answer 2

with t1 as (select sysdate-level+1 d from dual connect by level<=7 order by d)
select t1.d, sum(t2.value) value
from t1 left join t2
on t1.d=t2.date
group by t1.d