求各位大侠指点迷津？

[d_wenzi]

怎样实现正确查询库存量：
select a.id, a.name,
(select nvl(sum(b.num),0) from tab_2 b where b.trans='入' and a.name = b.name) as intput,
(select nvl(sum(b.num),0) from tab_2 b where b.trans='出' and a.name = b.name) as output,
(select nvl(sum(
(select nvl(sum(b.num),0) from tab_2 b where b.trans='入' and a.name = b.name) -
(select nvl(sum(b.num),0) from tab_2 b where b.trans='出' and a.name = b.name)),0) from tab_2 b

=== intput -output = count // input:某物品总入库，output:某物品总出库，cout:库存；

where a.name=b.name ) as count
from tab_1 a
left join tab_2 b on a.name=b.name
group by a.id, a.name
order by id;

下面是tab_1表：


下面是tab_2表：


下面是上面SQL运行的结果： //明显不对..

[u010412956]

没有建表测试，不知道ok不：

select t1.id,
       t1.name,
       sum(decode(t2.trans, '入', t2.num, 0)) input,
       sum(decode(t2.trans, '出', t2.num, 0)) output,
       sum(decode(t2.trans, '入', t2.num, 0)) -
       sum(decode(t2.trans, '出', t2.num, 0)) cout
  from tab_1 t1, tab_2 t2
 where t1.name = t2.name
 group by t1.id, t1.name