select std_dt,poid,(a.value+b.value) as value from a,b where a.std_dt=b.std_dt
SELECT nvl(a.std_dt,b.std_dt), (nvl(a.VALUE, 0) + nvl(b.VALUE,0)) AS VALUE FROM a FULL OUTER JOIN b ON a.std_dt = b.std_dt
谢谢大伙了,弄出结果了,就是不知道好不好,贴出来大家提点意见SELECT CASE WHEN A.STD_DT IS NULL THEN B.STD_DT ELSE A.STD_DT END AS STD_DT, NVL(A.VALUE1,0) + NVL(B.VALUE2,0) AS VALUE FROM A FULL OUTER JOIN B ON A.STD_DT = B.STD_DT
(select std_dt,poid,value from a
unoin all
select std_dt,poid,value from b)
group by std_dt,poid;
效率不敢恭维,不得已而为之。本想使用full out join,但是发现楼主的需求,这个偶写不出。题外话,如果做报表,设计最好不要分表。虽然我因强烈反对这样做,被我们技术总监K了一顿………期待高手,给出给优化的方案。
--------------- ---------- ----------
06-JAN-10 1 600
08-JAN-10 1 800SQL> select * from b;STD_DT POID VALUE
--------------- ---------- ----------
06-JAN-10 1 600
07-JAN-10 1 700
08-JAN-10 1 800SQL> select std_dt,poid,sum(value)
2 from
3 (
4 select * from a
5 union all
6 select * from b
7 )
8 group by std_dt,poid;STD_DT POID SUM(VALUE)
--------------- ---------- ----------
07-JAN-10 1 700
06-JAN-10 1 1200
08-JAN-10 1 1600
from
(
select * from a
union all
select * from b
)
group by std_dt;
2010/01/06 1 1200
2010/01/07 1 700
2010/01/08 1 1600
你的ID是指poid?
若如此,则不必担心。
group by std_dt,poid
以std_dt+poid分组。
若不尽然,请明示!
from
(
select std_dt,value from a
union all
select std_dt,value from b
)
group by std_dt;
那么两条记录的value要加和起来吗
另一个是B_VALUE
那么value加起来以后日期那一列倒是好显示 因为两条记录一样
可id那一列如何显示呢?显示那一条记录的id你最好给个例子出来
这样大家也好帮你
FROM a
FULL OUTER JOIN b ON a.std_dt = b.std_dt
CASE WHEN A.STD_DT IS NULL THEN
B.STD_DT
ELSE
A.STD_DT
END AS STD_DT,
NVL(A.VALUE1,0) + NVL(B.VALUE2,0) AS VALUE
FROM A
FULL OUTER JOIN B ON A.STD_DT = B.STD_DT