求教各位 一个问题 目前有两个表 比如 t1 进价表,t2 售价表,现在要合并成一个表,不知道怎么合并,按日期拆分
t1 进价表数据如下
开始日期 结束日期 进价
20010101 20120401 1
20120402 20120705 1.1
20120706 99991231 1.3t2售价表数据如下
开始日期 结束日期 售价
20010101 20120310 1.51
20120311 20120506 1.52
20120507 20120706 1.53
20120707 99991231 1.54要求实现的合并后的数据如下
开始日期 结束日期 进价 售价
20010101 20120310 1 1.51
20120311 20120401 1 1.52
20120402 20120506 1.1 1.52
20120507 20120705 1.1 1.53
20120706 20120706 1.3 1.53
20120707 99991231 1.3 1.54三个表的主键是:店铺id 商品id
t1 进价表数据如下
开始日期 结束日期 进价
20010101 20120401 1
20120402 20120705 1.1
20120706 99991231 1.3t2售价表数据如下
开始日期 结束日期 售价
20010101 20120310 1.51
20120311 20120506 1.52
20120507 20120706 1.53
20120707 99991231 1.54要求实现的合并后的数据如下
开始日期 结束日期 进价 售价
20010101 20120310 1 1.51
20120311 20120401 1 1.52
20120402 20120506 1.1 1.52
20120507 20120705 1.1 1.53
20120706 20120706 1.3 1.53
20120707 99991231 1.3 1.54三个表的主键是:店铺id 商品id
create table t1 (btime int,etime int, price decimal(4,2))
insert into t1
select 20010101, 20120401, 1
union all
select 20120402,20120705 ,1.1
union all
select 20120706, 99991231, 1.3 create table t2 (btime int,etime int, price decimal(4,2))
select * from t1
insert into t2
select
20010101, 20120310 ,1.51
union all
select
20120311, 20120506, 1.52
union all
select
20120507, 20120706, 1.53
union all
select
20120707, 99991231, 1.54
with data as(
select * from (
select * ,1 num from t1 union
select *,2 from t2
) a )
----先建立分组的时间分区。
--select *,(select MIN(etime) from data a where a.etime>b.btime) from data b
, b as(
select btime bt,(select MIN(etime) from data a where a.etime>=b.btime) M from data b
)
--- 让进价表 和售价表 都跟时间分区JOIN
select * from b
left join t1 on b.bt>=t1.btime and b.M<=t1.etime
left join t2 on b.bt>=t2.btime and b.M<=t2.etime
, (SELECT t1.price FROM t1 WHERE t.btime >= t1.btime AND t.btime <= t1.etime)
, (SELECT t2.price FROM t2 WHERE t.btime >= t2.btime AND t.btime <= t2.etime)
FROM
(SELECT b.btime, e.etime FROM
(SELECT ROWNUM rn, btime FROM (SELECT btime FROM t1 UNION SELECT btime FROM t2)) b,
(SELECT ROWNUM rn, etime FROM (SELECT etime FROM t1 UNION SELECT etime FROM t2)) e
WHERE b.rn = e.rn ) t