表A(如:id, count, date)中有日常销售数据,但是不保证每天都有销售数据,可能4月1日有,但是4月2日没有。
怎么用一条SQL得到当月的销售情况,结果要求:每天有一条数据,即3月有31条记录,4月有30条记录的情况请问如何用一条SQL实现
怎么用一条SQL得到当月的销售情况,结果要求:每天有一条数据,即3月有31条记录,4月有30条记录的情况请问如何用一条SQL实现
解决方案 »
- 转义字符%
- 关联删除的SQL,在线等
- 紧急求助,ORACLE同义词问题
- 两个表a和b, a.c1和b.c1是关联的,想建一个a和b当作同一个表的唯一索引或主键的
- 请高手指点这种分区方式属于哪一种(oracle分区问题)
- 无效的rowid错误,在线等.急
- The Network Adapter could not establish the connection
- 如何执行字段中的sql语句?
- sqlite3怎么样自定义一个int64位的类型,大家帮帮忙吧??????????
- oracle 对查询结果集循环修改
- 两个应用系统通过“中间表”进行数据交互,该如何设计?
- 省市区街道社区小区楼栋表如何设计,如何在查询时展示一个完整的树结构
(
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);
测试结果:
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
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