选择当月每天的销售单量，select出的结果有2天没数据的没显示，怎么让没数据的那几天显示0？
select date, count(distinct order_no) from tbl_sales group by date
数据结构如下：
《date》 《order_no》
2014-02-01 2
2014-02-01 3
2014-02-02 4
2014-02-04 8
2014-02-04 10
查询结果如下
2014-02-01 5
2014-02-02 4
2014-02-04 18
我想要的结果是：
2014-02-01 5
2014-02-02 4
2014-02-03 0
2014-02-04 18
------解决方案--------------------
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
希望对你有帮助。。
我的思路就是先取正月每天日期，在左关联你统计的数据。。
------解决方案--------------------
随便举个间隔时间的例子
with t as (
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