问题是这样的:
现在有2张表,一张入库表(ruku)和一张出库表(chuku)
ruku:
rukuid(ID),name(入库商品名称),shuliang(入库数量)
chuku:
chukuid(ID),name(出库商品名称),shuliang(出库数量)
测试数据:
入库表
rukuid name shuliang
1 美女 10
2 漂亮MM 20
3 帅哥 1
出库表
chukuid name shuliang
1 美女 5
2 漂亮MM 2
我现在要得到的结果是
name shuliang
美女 5
漂亮MM 18
帅哥 1
union all 行不行呀?不知道怎么写,来个大侠帮忙下呀
现在有2张表,一张入库表(ruku)和一张出库表(chuku)
ruku:
rukuid(ID),name(入库商品名称),shuliang(入库数量)
chuku:
chukuid(ID),name(出库商品名称),shuliang(出库数量)
测试数据:
入库表
rukuid name shuliang
1 美女 10
2 漂亮MM 20
3 帅哥 1
出库表
chukuid name shuliang
1 美女 5
2 漂亮MM 2
我现在要得到的结果是
name shuliang
美女 5
漂亮MM 18
帅哥 1
union all 行不行呀?不知道怎么写,来个大侠帮忙下呀
group by a.name
应该是
select a.name,a.shuliang-b.shuliang shuliang from ruku a join chuku b on a.rukuid = b.chukuid;
是要查剩余数量对吧?
from ruku a,chuku b
where a.rukuid = b.chukuid(+)
已写入 file afiedt.buf 1 select a.name name,nvl(a.shuliang,0)-nvl(b.shuliang,0) shuliang
2 from ruku a,chuku b
3* where a.rukuid=b.chukuid(+)
SQL> /NAME SHULIANG
-------------------- ----------
美女 5
漂亮MM 18
帅哥 1
SQL> edi
已写入 file afiedt.buf 1 select name,sum(shuliang) 库存
2 from (select name,shuliang from ruku
3 union all
4 select name,-shuliang from chuku)
5* group by name
SQL> /NAME 库存
-------------------- ----------
美女 5
帅哥 1
漂亮mm 18
select a.name,a.shuliang-b.shuliang shuliang from ruku a join chuku b where a.rukuid = b.chukuid;
当id不唯一时:
select name,sum(shuliang) shuliang from (select name,shuliang from ruku union all select name,-shuliang from chuku) group by name
已写入 file afiedt.buf 1 select name,sum(shuliang) 库存
2 from (select name,shuliang from ruku
3 union all
4 select name,-shuliang from chuku)
5* group by name
S……
[/Quote]支持此方法