求各位大侠指点迷津?

d_wenzi 2013-05-28 04:25:08
怎样实现正确查询库存量:
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运行的结果: //明显不对..


...全文
39 点赞 收藏 1
写回复
1 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
hh7yx 2013-05-28
没有建表测试,不知道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
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-05-28 04:25
社区公告
暂无公告