药库库存问题

药品编号吗？

我现在做的库存是入库表中的数量减去出库表中的数量可行吗？
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

药品编号有问题吧，药品编号不是唯一主键，怎么能把两个表连起来哪？

状态 料品编号 料品名称 料品规格 从仓区 数量 单位 到仓区
NA A00018 太太口服液 10支/盒 10 盒 B01

在状态NA(未批准)与OP(批准),根据 料品编号和仓区 更新库存表...

lz最好把库表的一部分贴出来看看，
因为其中的编码方式和数据情况我们无法判断~

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

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

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

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.药品编号

入库表是
"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，是入库是自动添加的

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次出库的时候

你的表关系是１：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嵌套，没办法

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

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.药品编号

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