SELECT t.ddate,decode(knum,NULL,0,knum)
(SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') ddate
FROM (SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 SDATE,
LAST_DAY(SYSDATE) EDATE
FROM DUAL) T
CONNECT BY SDATE + (ROWNUM - 1) <= EDATE) t
LEFT JOIN
(select date, count(distinct order_no) knum from tbl_sales group by date) k ON t.ddate=k.DATE
希望对你有帮助。。
我的思路就是先取正月每天日期,在左关联你统计的数据。。
解决方案 »
- oracle参数错误
- 一个Asp.net 网站,oracle 数据库装在服务器上,在局域网里的另一台电脑要连这个数据库,连接字符串中的用户名和密码应该写什么的用户名和密码呀??
- ORA-06550,PLS-00103错误,求助!
- 新手提问。关于存储过程的一个小问题,谢谢
- 删除2个表中相同的记录
- 获取存储过程的参数列表
- dbms_output.put_line 输出换行问题
- 怎么查看用户名和密码?
- D6i Form Builder下怎样把一个radio的背景色改成"未指定"
- 菜鸟的建表问题,高手请进,100分大赠送
- oracle PL/SQL 的 char 判断
- 把TIMESTAMP格式时间转换成UTC时间
SELECT t.ddate,decode(knum,NULL,0,knum)
(SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') ddate
FROM (SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 SDATE,
LAST_DAY(SYSDATE) EDATE
FROM DUAL) T
CONNECT BY SDATE + (ROWNUM - 1) <= EDATE) t LEFT JOIN
(select date, count(distinct order_no) knum from tbl_sales group by date) k ON t.ddate=k.DATE
编辑有问题,重新发一下。。
select to_date('2014-02-01','yyyy-mm-dd') tdate,2 order_no from dual
union all
select to_date('2014-02-01','yyyy-mm-dd') tdate,3 order_no from dual
union all
select to_date('2014-02-02','yyyy-mm-dd') tdate,4 order_no from dual
union all
select to_date('2014-02-04','yyyy-mm-dd') tdate,8 order_no from dual
union all
select to_date('2014-02-04','yyyy-mm-dd') tdate,10 order_no from dual
)
select edate,
decode((select sum(order_no)
from t
where tdate = t1.edate
group by tdate),
null,
0,
(select sum(order_no)
from t
where tdate = t1.edate
group by tdate))
from (select to_date('2014-02-01', 'yyyy-mm-dd') + (rownum - 1) edate
from dual
connect by rownum <= to_date('2014-02-28', 'yyyy-mm-dd') -
to_date('2014-02-01', 'yyyy-mm-dd') + 1) t1
select edate,
decode((select sum(order_no)
from t
where tdate = t1.edate
group by tdate),
null,
0,
(select sum(order_no)
from t
where tdate = t1.edate
group by tdate))
from (select trunc(sysdate,'mm') + rownum - 1 edate
from dual
connect by rownum <= trunc(last_day(sysdate)) -
trunc(sysdate,'mm') + 1) t1