17,377
社区成员
发帖
与我相关
我的任务
分享
--现在有6张表,如下:
--表1:商品表(goods)
--字段:goods_id(商品编号),name(名称),type(类型)
--表2:入库表(Storage)
--字段:Bill_no(入库单单号),status(单据状态),remark(备注)
--表3:入库明细表(Storage_detail)
--字段:Bill_no(入库单单号),detail_id(序号,自动生成),goods_id(商品编号),qty(数量)
--表4:出库表(OutLibrary)
--字段:Bill_no(出库单单号),status(单据状态),remark(备注)
--表5:出库明细表(OutLibrary_detail)
--字段:Bill_no(入库单单号),detail_id(序号,自动生成),goods_id(商品编号),qty(数量)
--表6:出入库记录表(InOutDetail)
--字段:type(类型,表示出库or入库),Bill_no(出入库的单据号),goods_id(商品编号),qty(数量)
--问题:现在有一个商品只入库而没有出库,所以入库表和入库明细是没有数据的
--,但出入库记录表有商品的入库记录而没有出库记录,但是我语句必须关联入库和
--入库明细表和出入库记录表,我的语句如下,要怎样关联才能查出数据?
select * from goods a,Storage b,Storage_detail c,OutLibrary d,OutLibrary_detail e,InOutDetail f
where b.Bill_no = c.Bill_no and d.Bill_no = e.Bill_no and a.goods_id = c.goods_id and a.goods_id = e.goods_id and
a.goods_id = f.goods_id and (f.Bill_no=b.Bill_no or f.Bill_no = d.Bill_no)
select io.bill_no,
nvl(gd.name, gd2.name) as goods_name,
nvl(sd.qty, od.qty) as qty,
nvl(sd.type, od.type) as type,
nvl(s.status, o.status) as status,
from InOutDetail io
left join Storage s
on s.bill_no = io.bill_no
left join Storage_detail sd
on sd.bill_no = io.bill_no
left join goods gd
on gd.goods_id = sd.goods_id
left join OutLibrary o
on o.bill_no = io.bill_no
left join OutLibrary_detail od
on od.bill_no = io.bill_no
left join goods gd2
on gd2.goods_id = od.goods_id