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

最新推荐文章于 2021-04-14 11:57:57 发布

xyb100

最新推荐文章于 2021-04-14 11:57:57 发布

阅读量722

点赞数

分类专栏： oracle学习

本文链接：https://blog.csdn.net/xyb100/article/details/88862399

版权

oracle学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文通过一系列示例查询，展示了如何使用Oracle数据库查询物料库存、特定日期库存、按月出入库信息以及计算物料利润。同时，提到了对入库主表和出库主表的总额更新操作。

摘要由CSDN通过智能技术生成

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)