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