药库库存问题

Question

药库的入库单表和出库单表之间通过什么主键连接算出库存量～本来用的是药品批号，现在才知道药品批号不可行～ 那么应该怎么把入库单表和出库单表联系起来哪？

Answer 1

我现在做的库存是入库表中的数量减去出库表中的数量可行吗？
SELECT ISNULL(a.sl, 0) - ISNULL(b.sl, 0) AS kcl
FROM yk_rkyp a LEFT OUTER JOIN
      yk_ckyp b ON a.ypid = b.ypid

Answer 2

状态 料品编号 料品名称   料品规格  从仓区  数量  单位  到仓区
NA   A00018   太太口服液 10支/盒           10    盒    B01在状态NA(未批准)与OP(批准),根据  料品编号和仓区  更新库存表...

Answer 3

YiZhiNet(九斤半) : 状态NA(未批准)与OP(批准）是什么意思啊？ 料品编号＆仓区也不能建立主键啊？

Answer 4

select m.药品编号 , m.数量 - n.数量 as 结余 from
(select 药品编号 , sum(数量) as 数量 from 入库单表 group by 药品编号) m,
(select 药品编号 , sum(数量) as 数量 from 出库单表 group by 药品编号) n
where m.药品编号 = n.药品编号

Answer 5

dawugui(潇洒老乌龟) 留个MSN可以吗？

Answer 6

to dawugui(潇洒老乌龟) 你m，n对应的是出库表和入库表吗？  药品编号在表中非主键，连接两个表会不会有问题啊？

Answer 7

一般来说，药品入库时是批量购入，出库时是从某个入库药品id中分若干次每次出若干量，有单位分级的要考虑单位分级；所以，大部分人在设计入库和出库表时会是1:n关系（n>=0），因此，无论使用什么条件，直接关联两表，都会使入库表的入库量虚增n（n>=0）倍，这样的操作方式是错误的。
最好是先将出库表量按照1:n关系的键（一般是药品入库id）做分组，分组后用入库表和出库分组汇总结果集做左外连接，然后再做算术运算求得库存值

Answer 8

to  adaizi1980(阿代) 你这种方法和我的差不多，但是我做起来有点问题（就是你说的第二条）！

Answer 9

入库表是
"rkid","djh","ypbh","mc","sl","dw","jj","lsj","ph","scrq","yxq","lx","gg"
7,"200612301006 ",1,"眼药水",2,"瓶",1,2,"1212",,2006-12-13 00:00:00,"滴剂","瓶"
8,"200612301007 ",2,"老鼠药",1,"盒",10,12,"2222",,2006-12-30 00:00:00,"安眠药","瓶"
出库表是
"rpid","djh","ypbh","mc","sl","dw","jj","lsj","ph","scrq","yxq","lx","gg"
11,"200612312002 ",2,"老鼠药",5,"盒",10,12,"234234",,2006-12-13 00:00:00,"安眠药","瓶"
我查询的语句是
SELECT ISNULL(a.sl, 0) - ISNULL(b.sl, 0) AS kcl
FROM yk_rkyp a LEFT OUTER JOIN
      yk_ckyp b ON a.rpid = b.rpid

Answer 10

select m.药品编号 , m.数量 - n.数量 as 结余 from
(select 药品编号 , sum(数量) as 数量 from 入库单表 group by 药品编号) m,
(select 药品编号 , sum(数量) as 数量 from 出库单表 group by 药品编号) n
where m.药品编号 = n.药品编号to dawugui(潇洒老乌龟) 你m，n对应的是出库表和入库表吗？  药品编号在表中非主键，连接两个表会不会有问题啊？m,n是表别名,我是分别在你的入库单表和出库单表中求SUM,然后对应取结余,至于你说的药品编号在表中非主键,这个没有关系的.我不是按主键在统计,而是按你药品的编号在分组统计.不过上面的语句改为左连接是最好的.如下:select m.药品编号 , m.数量 - isnull(n.数量,0) as 结余 from
(select 药品编号 , sum(数量) as 数量 from 入库单表 group by 药品编号) m
left join
(select 药品编号 , sum(数量) as 数量 from 出库单表 group by 药品编号) n
on m.药品编号 = n.药品编号

Answer 11

入库表是
"rkid","djh","ypbh","mc","sl","dw","jj","lsj","ph","scrq","yxq","lx","gg"
7,"200612301006 ",1,"眼药水",2,"瓶",1,2,"1212",,2006-12-13 00:00:00,"滴剂","瓶"
8,"200612301007 ",2,"老鼠药",1,"盒",10,12,"2222",,2006-12-30 00:00:00,"安眠药","瓶"
出库表是
"rkid","djh","ypbh","mc","sl","dw","jj","lsj","ph","scrq","yxq","lx","gg"
8,"200612312002 ",2,"老鼠药",5,"盒",10,12,"234234",,2006-12-13 00:00:00,"安眠药","瓶"
我查询的语句是
SELECT ISNULL(a.sl, 0) - ISNULL(b.sl, 0) AS kcl
FROM yk_rkyp a LEFT OUTER JOIN
      yk_ckyp b ON a.rpid = b.rpid主键是rkid，是入库是自动添加的

Answer 12

adaizi1980(阿代)  当同一批号的药品出库2次的时候，就会显示两条记录了比如
入库表是
"rkid","djh","ypbh","mc","sl","dw","jj","lsj","ph","scrq","yxq","lx","gg"
8,"200612301007 ",2,"老鼠药",1,"盒",10,12,"2222",,2006-12-30 00:00:00,"安眠药","瓶"
出库表是
"rkid","djh","ypbh","mc","sl","dw","jj","lsj","ph","scrq","yxq","lx","gg"
8,"200612312002 ",2,"老鼠药",5,"盒",5,12,"234234",,2006-12-13 00:00:00,"安眠药","瓶"
8,"200612312002 ",2,"老鼠药",5,"盒",5,12,"234234",,2006-12-13 00:00:00,"安眠药","瓶"
的时候，也就是同一个rkid10盒药，分2次出库的时候

Answer 13

你的表关系是１：n(n<=0)
select rkid,ISNULL(m.sl, 0) - ISNULL(n.sl, 0)
from  yk_rkyp m left outer join
(select rkid, sum(ISNULL(sl, 0)) as sl from yk_ckyp ) n
on m.rkid = n.rkid
然后用临时表或在程序／过程／函数中对结果集做求和操作获得库存量，sql中不允许group　by嵌套，没办法

Answer 14

dawugui(潇洒老乌龟) 突然我感觉咱们两个求的库存不是一个概念，难道我理解错了，我的概念是对每一个批号的药品做库存统计，你的是对每一个编号的药品做库存统计～

Answer 15

select m.药品批号 , m.药品编号 , m.数量 - isnull(n.数量,0) as 结余 from
(select 药品批号 , 药品编号 , sum(数量) as 数量 from 入库单表 group by 药品批号,药品编号) m
left join
(select 药品批号 , 药品编号 , sum(数量) as 数量 from 出库单表 group by 药品批号,药品编号) n
on m.药品批号 = n.药品批号 and m.药品编号 = n.药品编号

Answer 16

看来我理解错了～ thanks，还有补充的吗？ 没有的话揭帖给分了啊

Answer 17

dawugui(潇洒老乌龟) 我再考虑一下下哈 有问题再开贴，大家等分吧