有3个表,
T1 T1_DATE T1_QTY
20121001 1000
20121003 3000
20121004 4000T2 T2_DATE T2_QTY
20121002 2000
20121003 300
20121005 2500T3 T3_DATE T3_QTY
20120930 1000我想得到的结果是:
从20121001开始,往T3表中插入数据,
T3表中,T3_QTY = 20121001的数据等于T3中20120930的T3_QTY
+ T1中20121001的T1_QTY(如果无,就是0)
- T2中20121001的T1_QTY (如果无,就是0)T3的结果是:
T3_DATE T3_QTY
20120930 1000
20121001 2000
20121002 0
20121003 2700
20121004 6700
20121005 4200
T3表中,T3_QTY = 20121001的数据等于T3中20120930的T3_QTY
+ T1中20121001的T1_QTY(如果无,就是0)
- T2中20121001的T1_QTY (如果无,就是0)
--表示没明白什么意思。
T3表中,T3_QTY = 20121001的数据等于T3中20120930的T3_QTY
+ T1中20121001的T1_QTY(如果无,就是0)
- T2中20121001的T1_QTY (如果无,就是0)真心没看明白。。
select T3_DATE, sum(T3_QTY) over (order by T3_DATE) T3_QTY from (
select T3_DATE, T3_QTY from T3 union all
select T_DATE, nvl(T1_QTY,0)-nvl(T2_QTY,0) from
(select (20121001+rownum-1) T_DATE from dual connect by rownum <= 5)
left join T1 on T_DATE = T1_DATE left join T2 on T_DATE = T2_DATE)
) where T3_DATE != 20120930;
t.t3_date,
sum(sum(t.qty)) over(order by t.t3_date rows between unbounded preceding and current row) qty
from
(select t3_date,t3.qty from t3 union all
select t1_date,t1.qty from t1 union all
select t2_date,-t2.qty from t2 order by t3_date)t
group by t.t3_date
不行的话,望提示一下!
WITH t1 AS (
SELECT 20121001 t1_date,1000 t1_qty FROM DUAL UNION ALL
SELECT 20121003 t1_date,3000 t1_qty FROM DUAL UNION ALL
SELECT 20121004 t1_date,4000 t1_qty FROM DUAL
),
t2 AS (
SELECT 20121002 t2_date,2000 t2_qty FROM DUAL UNION ALL
SELECT 20121003 t2_date,300 t2_qty FROM DUAL UNION ALL
SELECT 20121005 t2_date,2500 t2_qty FROM DUAL
)
,t3 AS (
SELECT 20120930 t3_date,1000 t3_qty FROM DUAL
)
SELECT m.t_date,
SUM(m.t_qty) over(ORDER BY m.t_date) t_qty
FROM (SELECT NVL(t1.t1_date, t2.t2_date) t_date,
nvl(t1.t1_qty, 0) - nvl(t2.t2_qty, 0) t_qty
FROM t1
FULL JOIN t2 ON t1.t1_date = t2.t2_date
UNION ALL
SELECT t3.t3_date,t3.t3_qty FROM t3) m T_DATE T_QTY
---------- ----------
20120930 1000
20121001 2000
20121002 0
20121003 2700
20121004 6700
20121005 4200
select
t.t3_date,
sum(sum(t.qty)) over(order by t.id,t.t3_date rows between unbounded preceding and current row) qty
from
(select 1 id,t3_date,t3.qty from t3 union all
select 2,t1_date,t1.qty from t1 union all
select 2,t2_date,-t2.qty from t2)t
group by t.id,t.t3_date
t3表的数据要排在前面!