题目:现有两张表,test_1和test_2,两个表的内容及结构如下:
test_1
pid product inputNum
1 A 20
3 C 10
5 E 30
test_2
pid product outputNum
1 A 30
2 B 8
4 D 15
现在要查询得出如下结果:
pid product inputNum outputNum
1 A 20 30
2 B 0 8
3 C 10 0
4 D 0 15
5 E 30 0
::::answer:::::
select pid,product,sum(inputNum) as inputNum,sum(outputnum) as outputNum
from (
select pid,product,inputNum,cast(0 as int) as outputNum
from test_1
union all
select pid,product,0 as inputNum,outputNum
from test_2
) group by pid,product
test_1
pid product inputNum
1 A 20
3 C 10
5 E 30
test_2
pid product outputNum
1 A 30
2 B 8
4 D 15
现在要查询得出如下结果:
pid product inputNum outputNum
1 A 20 30
2 B 0 8
3 C 10 0
4 D 0 15
5 E 30 0
::::answer:::::
select pid,product,sum(inputNum) as inputNum,sum(outputnum) as outputNum
from (
select pid,product,inputNum,cast(0 as int) as outputNum
from test_1
union all
select pid,product,0 as inputNum,outputNum
from test_2
) group by pid,product
SQL> select nvl(temp1.pid,temp2.pid) as iid,nvl(temp1.product,temp2.product),nvl
(temp1.inputNum,0),nvl(temp2.outputNum,0) from temp1 full join temp2 on temp1.pi
d=temp2.pid order by iid;
查询结果: IID NV NVL(TEMP1.INPUTNUM,0) NVL(TEMP2.OUTPUTNUM,0)
---------- -- --------------------- ----------------------
1 a 20 30
2 b 0 8
3 c 10 0
4 d 0 15
5 e 30 0