rq no num1
2008-01 A 10 2008-09 A 20 2008-12 A 30 2008-03 B 202008-06 B 30想要的结果:
2008-01 A 10
2008-02 A 0
2008-03 A 0
...
2008-09 A 20
2008-10 A 0
...
2008-12 A 302008-01 B 0
2008-02 B 0
2008-03 B 20
...
2008-05 B 0
2008-06 B 30
2008-07 B 0
...
2008-12 B 0以上,如何实现呢?
2008-01 A 10 2008-09 A 20 2008-12 A 30 2008-03 B 202008-06 B 30想要的结果:
2008-01 A 10
2008-02 A 0
2008-03 A 0
...
2008-09 A 20
2008-10 A 0
...
2008-12 A 302008-01 B 0
2008-02 B 0
2008-03 B 20
...
2008-05 B 0
2008-06 B 30
2008-07 B 0
...
2008-12 B 0以上,如何实现呢?
然后left join一下
--没测试
with tmp as
(
select to_date('2008-01','yyyy-mm') dt, 'A' no, 10 num from dual union all
select to_date('2008-09','yyyy-mm') dt, 'A' no, 20 num from dual union all
select to_date('2008-12','yyyy-mm') dt, 'A' no, 30 num from dual union all
select to_date('2008-03','yyyy-mm') dt, 'B' no, 20 num from dual union all
select to_date('2008-06','yyyy-mm') dt, 'B' no, 30 num from dual
)
select dt, no, num
from(select b.dt, a.no, nvl(a.num,0) num
from tmp a,
(select distinct substr(dt,1,5)||lpad(level,2,0) dt
from tmp
connect by level <= (select max(substr(dt,6,2))-min(substr(dt,6,2))+1 from tmp)
)b
where a.dt(+) = b.dt
);
好像不可以诶
(
select to_date('2008-01','yyyy-mm') dt, 'A' no, 10 num from dual union all
select to_date('2008-09','yyyy-mm') dt, 'A' no, 20 num from dual union all
select to_date('2008-12','yyyy-mm') dt, 'A' no, 30 num from dual union all
select to_date('2008-03','yyyy-mm') dt, 'B' no, 20 num from dual union all
select to_date('2008-06','yyyy-mm') dt, 'B' no, 30 num from dual
),
a as (select min(dt) mindt, max(dt) maxdt from tmp),
b as (select mindt+level-1 as dt from a connect by level<=(select maxdt-mindt from a))
select b.dt, tmp.no, nvl(tmp.num,0) as num
from b left join tmp on b.dt=tmp.dt
order by b.dt asc;
with tmp as
(
select '2008-01' dt, 'A' no, 10 num from dual union all
select '2008-09' dt, 'A' no, 20 num from dual union all
select '2008-12' dt, 'A' no, 30 num from dual union all
select '2008-03' dt, 'B' no, 20 num from dual union all
select '2008-06' dt, 'B' no, 30 num from dual
),
a as (select min(dt) mindt, max(dt) maxdt from tmp),
b as (select to_char(add_months(last_day(to_date(a.mindt,'yyyy-mm'))+1,level-2),'yyyy-mm') as dt
from a connect by level<=( months_between(to_date(maxdt,'yyyy-mm'),to_date(mindt,'yyyy-mm') )+2 ) ),
c as (select b.dt, t.no
from b full join (select distinct no from tmp) t on 1=1)
select c.dt, c.no, nvl(tmp.num,0) as num
from c left join tmp on c.dt=tmp.dt
order by c.no,c.dt asc;
with tmp as
(
select '2008-01' dt, 'A' no, 10 num from dual union all
select '2008-09' dt, 'A' no, 20 num from dual union all
select '2008-12' dt, 'A' no, 30 num from dual union all
select '2008-03' dt, 'B' no, 20 num from dual union all
select '2008-06' dt, 'B' no, 30 num from dual
),
a as (select min(dt) mindt, max(dt) maxdt from tmp),
b as (select to_char(add_months(last_day(to_date(a.mindt,'yyyy-mm'))+1,level-2),'yyyy-mm') as dt
from a connect by level<( months_between(to_date(maxdt,'yyyy-mm'),to_date(mindt,'yyyy-mm') )+2 ) ),
c as (select b.dt, t.no
from b full join (select distinct no from tmp) t on 1=1)
select c.dt, c.no, nvl(tmp.num,0) as num
from c left join tmp on c.dt=tmp.dt
order by c.no,c.dt asc;
可以尝试一下,
select add_months('2008-01-01',level-1) from dual connect by level < 13
来获得一个12个月的表。
9i好像不行啊.
呵呵
--请问下楼主:
--如果一年中只有一条数据呢?
--比如:
-- rq no num1
-- 2007-06 A 100--那么这一年该如何填充??
例如:
2007-01 A 0
2007-02 A 0
...
2007-06 A 100
2007-07 A 0
...
2007-12 A 0
根据这个范围生成所有日期,
然后 cross join (select distinct No from $表),
然后求 union 再 sum/group by 一下。不过对楼主的那个 “A” 和 “B”楼主没交代怎么处理的,比如这一年同时有 A 和 B,是同时为 A 和 B 生成 0 的记录而另一年中只有 A 的记录时只生成 A 的填充记录?
--对,我也想问此问题,比如下面两条
--比如:
-- rq no num1
-- 2007-01 B 100
-- 2007-06 A 100--是否是要成这样填充?
-- rq no num1
-- 2007-01 B 100
-- 2007-02 B 0
-- .... .... ....
-- 2007-05 B 0
-- 2007-06 A 100
-- 2007-07 A 0
-- .... .... ....--如果是这样,那么下面SQL能满足,只是写的有点复杂,用PLSQL来做可能要简单点with tab1 as(
select '2007-01' dt, 'B' no, 100 num from dual union all
select '2007-06' dt, 'A' no, 200 num from dual union allselect '2008-03' dt, 'A' no, 10 num from dual union all
select '2008-06' dt, 'B' no, 20 num from dual union all
select '2008-09' dt, 'A' no, 30 num from dual union allselect '2009-06' dt, 'B' no, 50 num from dual
)
,
tmp as(
select dt||mon as rq from
(select lpad(level,2,0) mon from dual connect by level<=12 ),
(select distinct substr(dt,1,5) dt from tab1)
)
select rq,
case when (select no from tab1 where rq=dt and rownum=1) is not null
then (select no from tab1 where rq=dt and rownum=1)
when (select no from (select * from tab1 order by dt desc) where substr(rq,1,4)=substr(dt,1,4) and rq>dt and rownum=1) is not null
then (select no from (select * from tab1 order by dt desc) where substr(rq,1,4)=substr(dt,1,4) and rq>dt and rownum=1)
else (select no from tab1 where substr(rq,1,4)=substr(dt,1,4) and rownum=1)
end as no,
nvl(num,0) as num
from tmp left join tab1 on rq=dt
order by rq;
with tb as
(
select '2008-01'dt, 'A' no, 10 num from dual union all
select '2008-09'dt, 'A' no, 20 num from dual union all
select '2008-12'dt, 'A' no, 30 num from dual union all
select '2008-03'dt, 'B' no, 20 num from dual union all
select '2008-06'dt, 'B' no, 30 num from dual
),
tmp as(
select dt||mon dt,no from
(select lpad(level,2,0) mon from dual connect by level<13 ),
(select distinct substr(dt,1,5) dt from tb),
(select distinct no from tb)
)
select a.dt,a.no,nvl(b.num,0) num
from tmp a full outer join tb b on a.dt=b.dt and a.no=b.no
order by a.no,a.dt
SQL> with tb as
2 (
3 select '2008-01'dt, 'A' no, 10 num from dual union all
4 select '2008-09'dt, 'A' no, 20 num from dual union all
5 select '2008-12'dt, 'A' no, 30 num from dual union all
6 select '2008-03'dt, 'B' no, 20 num from dual union all
7 select '2008-06'dt, 'B' no, 30 num from dual
8 ),
9 tmp as(
10 select dt||mon dt,no from
11 (select lpad(level,2,0) mon from dual connect by level<13 ),
12 (select distinct substr(dt,1,5) dt from tb),
13 (select distinct no from tb)
14 )
15 select a.dt,a.no,nvl(b.num,0) num
16 from tmp a full outer join tb b on a.dt=b.dt and a.no=b.no
17 order by a.no,a.dt
18 /
DT NO NUM
-------------- -- ----------
2008-01 A 10
2008-02 A 0
2008-03 A 0
2008-04 A 0
2008-05 A 0
2008-06 A 0
2008-07 A 0
2008-08 A 0
2008-09 A 20
2008-10 A 0
2008-11 A 0
2008-12 A 30
2008-01 B 0
2008-02 B 0
2008-03 B 20
2008-04 B 0
2008-05 B 0
2008-06 B 30
2008-07 B 0
2008-08 B 0
DT NO NUM
-------------- -- ----------
2008-09 B 0
2008-10 B 0
2008-11 B 0
2008-12 B 0
24 rows selected
SQL> with tb as
2 (
3 select '2007-01' dt, 'B' no, 100 num from dual union all
4 select '2008-01'dt, 'A' no, 10 num from dual union all
5 select '2008-09'dt, 'A' no, 20 num from dual union all
6 select '2008-12'dt, 'A' no, 30 num from dual union all
7 select '2008-03'dt, 'B' no, 20 num from dual union all
8 select '2008-06'dt, 'B' no, 30 num from dual
9 ),
10 tmp as(
11 select dt||mon dt,no from
12 (select lpad(level,2,0) mon from dual connect by level<13 ),
13 (select distinct substr(dt,1,5) dt from tb),
14 (select distinct no from tb)
15 )
16 select a.dt,a.no,nvl(b.num,0) num
17 from tmp a full outer join tb b on a.dt=b.dt and a.no=b.no
18 order by a.no,a.dt
19 /
DT NO NUM
-------------- -- ----------
2007-01 A 0
2007-02 A 0
2007-03 A 0
2007-04 A 0
2007-05 A 0
2007-06 A 0
2007-07 A 0
2007-08 A 0
2007-09 A 0
2007-10 A 0
2007-11 A 0
2007-12 A 0
2008-01 A 10
2008-02 A 0
2008-03 A 0
2008-04 A 0
2008-05 A 0
2008-06 A 0
2008-07 A 0
2008-08 A 0
DT NO NUM
-------------- -- ----------
2008-09 A 20
2008-10 A 0
2008-11 A 0
2008-12 A 30
2007-01 B 100
2007-02 B 0
2007-03 B 0
2007-04 B 0
2007-05 B 0
2007-06 B 0
2007-07 B 0
2007-08 B 0
2007-09 B 0
2007-10 B 0
2007-11 B 0
2007-12 B 0
2008-01 B 0
2008-02 B 0
2008-03 B 20
2008-04 B 0
2008-05 B 0
DT NO NUM
-------------- -- ----------
2008-06 B 30
2008-07 B 0
2008-08 B 0
2008-09 B 0
2008-10 B 0
2008-11 B 0
2008-12 B 0
48 rows selected
SQL>