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表空间文件删除了,数据怎么恢复啊
- OracleOraHome92TNSListener
- oracle菜鸟提问存储过程的问题,总是编译不过。。。
- 求各位大拿赐教 oracle first last 为什么查不到5000
- [求助]挑战你的SQL,双倍给分(200分)
- 如何找到数据库中第二小ID的那一行数据 ?(在线等等)
- 关于Oracle存储大量二进制数据的问题,
- pro*c能调用oracle中的存储过程吗?
- 急!!!!!
- 怪问题ORA-00937: not a single-group group function
- 请教在数据库存储过程中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