选择当月每天的销售单量，select出的结果有2天没数据的没显示，怎么让没数据的那几天显示0？

[charlesxu]

选择当月每天的销售单量，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

希望对你有帮助。。 我的思路就是先取正月每天日期，在左关联你统计的数据。。

[流浪川]

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

[_拙计]

当月 那你可以换一下：

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