insert into c1 ( ......)
select c.rkey,c.Part_Name,nal(a.balqty,0),nvl(a.qty1_a,0). ..., nvl(b.balqty,0)....
from (select rkey, par_name from a union rkey, par_name from b ) c,a,b
where c.reky = a.reky(+)
and c.reky = b.reky(+)
select c.rkey,c.Part_Name,nal(a.balqty,0),nvl(a.qty1_a,0). ..., nvl(b.balqty,0)....
from (select rkey, par_name from a union rkey, par_name from b ) c,a,b
where c.reky = a.reky(+)
and c.reky = b.reky(+)
解决方案 »
- oracle bdump目录下的trc日志,请问是什么问题,谢谢!!!
- Oracle的三层结构到底是什么意思啊?
- 随机数问题
- ORACLE exp能导入imp不能导入导入
- pl/sql 语句优化实现求救!谢谢
- 进入SQLPlus WorkSheet的服务老出错????
- SQL 语句不会
- 在ORACLE9i中改变计算机名和工作组名的问题
- ???????ora-00101:invalid specification for system parameter MTS-DISPATCHERS
- 优化SQL语句
- 请教在数据库存储过程中insert,update,delete语句后进行执行是否成功验证的必要性。(up有分)
- 怎样用游标返回临时表
select
v.rkey ,
v.Part_Name ,
nvl(a1.Balance_Qty,0) as Bal_Qty_A,
nvl(a1.Qty1 ,0) as Qty1_A ,
nvl(a1.Qty2 ,0) as Qty2_A ,
nvl(a1.Qty3 ,0) as Qty3_A ,
nvl(b1.Balance_Qty,0) as Bal_Qty_B,
nvl(b1.Qty1 ,0) as Qty1_B ,
nvl(b1.Qty2 ,0) as Qty2_B ,
nvl(b1.Qty3 ,0) as Qty3_B
from
(select rkey,Part_Name from A union select rkey from B) v
left join
A a1
on
v.rkey = a1.rkey
left join
B b1
on
v.rkey = b1.rkey
order by
v.rkey
a.rkey,
a.Part_Name,
a.Balance_Qty Bal_Qty_A,
a.Qty1 Qty1_A,
a.Qty2 Qty2_A,
a.Qty3 Qty3_A,
nvl(b.Balance_Qty,0) as Bal_Qty_B,
nvl(b.Qty1,0) Qty1_B,
nvl(b.Qty2,0) Qty2_B,
nvl(b.Qty3,0) Qty3_B
from a, b
where a.rkey = b.rkey(+)
union all
select
b.rkey,
b.Part_Name,
0,
0,
0,
0,
b.Balance_Qty,
b.Qty1,
b.Qty2,
b.Qty3,
from b
where not exists(select 1 from a where a.rkey=b.key);
a.rkey,
a.Part_Name,
a.Balance_Qty Bal_Qty_A,
a.Qty1 Qty1_A,
a.Qty2 Qty2_A,
a.Qty3 Qty3_A,
nvl(b.Balance_Qty,0) as Bal_Qty_B,
nvl(b.Qty1,0) Qty1_B,
nvl(b.Qty2,0) Qty2_B,
nvl(b.Qty3,0) Qty3_B
from a, b
where a.rkey = b.rkey(+)
union all
select
b.rkey,
b.Part_Name,
0,
0,
0,
0,
b.Balance_Qty,
b.Qty1,
b.Qty2,
b.Qty3
from b
where not exists(select 1 from a where a.rkey=b.rkey)
order by rkeya01 b6510c-a 100 80 20 0 30 10 20 0
a02 v0349c-f 0 0 0 0 200 100 98 2
a03 g9810r-c 0 0 0 0 398 54 300 44
a04 d4561q-c 600 150 450 0 0 0 0 0
a05 f9012c-d 23 3 0 23 300 180 0 120
a06 i9062c-d 50 0 0 50 0 0 0 0
nvl(a.Part_Name,b.Part_Name) Part_Name,
a.Balance_Qty Bal_Qty_A,
a.Qty1 Qty1_A,
a.Qty2 Qty2_A,
a.Qty3 Qty3_A,
b.Balance_Qty Bal_Qty_B,
b.Qty1 Qty1_B,
b.Qty2 Qty2_B,
b.Qty3 Qty3_B
from a full join b
on a.rkey=b.rkey;9I以上可以用
---------- ---------- ---------- ----------
1 5
3 1
2 1 2SQL> select * from a2; A VAL
---------- ----------
2
1 2
4 6SQL> select c.a,a.val,b.val
2 from (select a from a1 union select a from a2 ) c,a1 a,a2 b
3 where c.a = a.a(+)
4 and c.a = b.a(+)
5 / A VAL VAL
---------- ---------- ----------
1 5 2
2
3 1
4 6SQL> 所以用下面的语句没问题:select c.rkey,c.Part_Name,nvl(a.balqty,0),nvl(a.qty1_a,0).., nvl(b.balqty,0)....
from (select rkey, par_name from a union select rkey, par_name from b ) c,a,b
where c.reky = a.reky(+)
and c.reky = b.reky(+)
2.将合成的G表分别和A表和B表左联接.
3.选取G表的rkey,Part_Name;A表的Qty1,Qty2,Qty3;B表的Qty1,Qty2,Qty3即可的正确的结果。
下班了!
不明就留下qq.
insert into c
select rkey,Part_Name,sum(c1),sum(c2),sum(c3),sum(c4),
sum(c5),sum(c6),sum(c7),sum(c8) from
(select rkey,Part_Name,Balance_Qty c1,Qty1 c2,Qty2 c3,Qty3 c4,
0 c5,0 c6,0 c7,0 c8 from a
union
select rkey,Part_Name,0 c1,0 c2,0 c3,0 c4,
Balance_Qty c5,Qty1 c6,Qty2 c7,Qty3 c8 from b)
group by rkey,Part_Name