笔试题

[larrychen_bmw]

表A（如：id, count, date）中有日常销售数据，但是不保证每天都有销售数据，可能4月1日有，但是4月2日没有。
怎么用一条SQL得到当月的销售情况，结果要求：每天有一条数据，即3月有31条记录，4月有30条记录的情况

请问如何用一条SQL实现

[我心飞翔]

测试数据：

CREATE TABLE T180
(
    SaleDate DATE,
    cnt NUMBER(3)
);

INSERT INTO T180 VALUES(to_date('20120401', 'YYYYMMDD'), 2);
INSERT INTO T180 VALUES(to_date('20120402', 'YYYYMMDD'), 4);
INSERT INTO T180 VALUES(to_date('20120415', 'YYYYMMDD'), 6);
INSERT INTO T180 VALUES(to_date('20120420', 'YYYYMMDD'), 8);

测试结果：

[ssqtjffcu]

with t as(
select 1 id,12 count,to_date('2012-03-01','yyyy-mm-dd') d from dual
union all
select 2,34,to_date('2012-03-04','yyyy-mm-dd') from dual
union all
select 3,56,to_date('2012-03-05','yyyy-mm-dd') from dual
union all
select 4,62,to_date('2012-03-07','yyyy-mm-dd') from dual
union all
select 5,23,to_date('2012-03-10','yyyy-mm-dd') from dual
union all
select 6,45,to_date('2012-03-14','yyyy-mm-dd') from dual
union all
select 7,64,to_date('2012-03-20','yyyy-mm-dd') from dual
union all
select 8,23,to_date('2012-03-25','yyyy-mm-dd') from dual
union all
select 9,92,to_date('2012-03-27','yyyy-mm-dd') from dual
union all
select 10,22,to_date('2012-04-03','yyyy-mm-dd') from dual
union all
select 11,63,to_date('2012-04-06','yyyy-mm-dd') from dual
)
select t3.d, nvl(t.count, 0)
  from t,
       (select m + r d
          from (select min(d) m from t) t1,
               (select rownum r
                  from dual
                connect by rownum < (select max(d) - min(d) from t)) t2) t3
 where t.d(+) = t3.d
 order by t3.d;
D           NVL(T.COUNT,0)
----------- --------------
2012-3-2                 0
2012-3-3                 0
2012-3-4                34
2012-3-5                56
2012-3-6                 0
2012-3-7                62
2012-3-8                 0
2012-3-9                 0
2012-3-10               23
2012-3-11                0
2012-3-12                0
2012-3-13                0
2012-3-14               45
2012-3-15                0
2012-3-16                0
2012-3-17                0
2012-3-18                0
2012-3-19                0
2012-3-20               64
2012-3-21                0
 
D           NVL(T.COUNT,0)
----------- --------------
2012-3-22                0
2012-3-23                0
2012-3-24                0
2012-3-25               23
2012-3-26                0
2012-3-27               92
2012-3-28                0
2012-3-29                0
2012-3-30                0
2012-3-31                0
2012-4-1                 0
2012-4-2                 0
2012-4-3                22
2012-4-4                 0
2012-4-5                 0
2012-4-6                63

[iqlife]

比较同意2楼的做法

但是再一楼的方法上加改进会获得更好的效果

[larrychen_bmw]

想得到的结果如下面代码，参考了一楼的思路和演示用的表：

select A.day, nvl(B.cnt, 0) cnt
from (
select trunc((last_day(sysdate) - level) + 1, 'dd') as day from dual connect by level <= to_char(last_day(sysdate), 'dd'))  A,
T180  B
where A.day = B.SaleDate(+)
order by A.day