oracle数据库个人公司笔试问题（二）

6.查询入库总数相等的物料信息，格式如下：

物料编号     物料名称     现有库存     入库总数    出库总数

  1           垫圈          176          300         124

  2           座椅          202          300          98

select distinct w.id as 物料编号,
                w.name as 物料名称,
                nvl(m.入库, 0) - nvl(n.出库, 0) as 现有库存,
                nvl(m.入库, 0) as 入库数,
                nvl(n.出库, 0) as 出库数
  from 物料表 w,  
   (select gid, nvl(sum(o.amount), 0) as 出库
               from 出库明细表 o group by gid) n,  
(select a.gid from (select gid,sum(i.amount) as x from 入库明细表 i
group by gid) a,(select gid,sum(i.amount) as y from 入库明细表 i group by gid) b
where a.x=b.y
group by a.gid
having count(a.gid)>1) s,
(select gid,sum(i.amount) as 入库 from 入库明细表 i
group by gid) m
where w.id=n.gid
and n.gid=s.gid
and s.gid=m.gid

7.查询所有物料2017年02月22日的当时的库存量，格式如下：

物料编号     物料名称     库存

  1            垫圈        300

select  w.id as 物料编号,
        w.name as 物料名称,
       nvl( a.入库-b.出库,0) as 库存量
        from 物料表 w,
      
      (select gid,sum(it.amount) as 入库 from 入库明细表 it
      where mainid in
      (select id from 入库主表 i
      where to_char(ipdate,'yyyy/mm/dd')< ='2017/02/22')
      group by gid) a,
       (select gid,sum(ot.amount) as 出库 from 出库明细表 ot
      where mainid in
      (select id from 出库主表 o
      where to_char(outdate,'yyyy/mm/dd')< ='2017/02/22')
      group by gid) b
    where w.id=a.gid
    and a.gid=b.gid

ps:可能和插入的数据不一样，但是主要理解的是思路

8.查询“垫圈”ID为3的物料的1、2、3月的出入库信息，格式如下：

入库日期         入库数量      出库日期     出库数量

2014-01          110                  0

2014-02          60                  70

2014-03          30                  100

select i.ipdate,
a.入库,
o.outdate,
b.出库
 from 入库主表 i,出库主表 o,
(select mainid,gid,nvl(sum(it.amount),0) as 入库 from 入库明细表 it
where
gid=3 and
 mainid in
(select id from 入库主表 i
where to_char(i.ipdate,'yyyy/mm/dd') 
between
'2017/01/01'
and
'2017/03/31'
)

9.查询所有物料目前产生的利润，格式如下：（利润 = 出库时候的金额 - 当初入库时候的金额）

物料编号      物料名称      出库总数     入库金额     出库金额    利润

  1             垫圈           124         1837.32      4238.83    2401.51

select 
w.id as 物料编号,
w.name as 物料名称,
nvl(a.入库数,0) as 入库总数,
c1.入库总额 as 入库总额,
nvl(b.出库数,0) as 出库总数,
d1.出库总额 as 出库总额,
nvl((d1.出库总额-c1.入库总额),0) as 利润
from 物料表 w,


(select gid,sum(c.每单总额) as 入库总额
 from (select mainid,gid,nvl(sum(amount*price) over(partition by mainid,gid),0) as 每单总额  from 入库明细表) c
group by gid ) c1,

(select gid,sum(d.每单总额) as 出库总额
 from (select mainid,gid,nvl(sum(amount*price) over(partition by mainid,gid),0) as 每单总额  from 出库明细表) d
group by gid ) d1,
(select gid,nvl(sum(i.amount),0) as 入库数 from 入库明细表 i
group by gid
) a
left join
(select gid,nvl(sum(o.amount),0) as 出库数 from 出库明细表 o
group by gid
) b 
on a.gid=b.gid
where w.id=a.gid
and c1.gid=d1.gid
and b.gid=c1.gid

10.更新“入库主表”的“total”列（入库总额），更新 “出库主表”的“total”列（出库总额）

update 入库主表 b
set  b.total=
(select 入库总额 from (select distinct mainid, sum(amount*price) over(partition by mainid) as 入库总额  from 入库明细表) a
where a.mainid=b.id)