寻数据存储的解决方案，高手请进来，在线等，如解决立即给分

[lee_2020]

我需要用下面三张表建立一个数据存储，在Powerbuilder中调用，调用时传递过来一个调拨出库单编号，实现这样的功能，根据调拨出库单的编号在调拨出库单明细表中检索出这张出库单上的所有药品项，然后在 药品库存 表上把对应库房内的这种药品都检索出来，按照批次从小到大的顺序去扣库存数量，直到把调拨单上的数量扣完。因为同一库房同一药品如果是不同批号我是分开记录的，为了实现按照批次的先进先出。批次小的先出库，这个批次实际录入时会强制为060528这样的字样，其实就是一个日期拉。（所有库房的库存都放在这张库存表里，所以有库房这个数据项）




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[药品库存]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[药品库存]
GO

CREATE TABLE [dbo].[药品库存] (
[编号] [int] IDENTITY (1, 1) NOT NULL ,
[药品编号] [int] NULL ,
[名称] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[效期] [smalldatetime] NULL ,
[库存数量] [decimal](18, 2) NULL ,
[单位] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[库房] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[库存金额] [decimal](26, 2) NULL
) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[调拨出库单主单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[调拨出库单主单]
GO

CREATE TABLE [dbo].[调拨出库单主单] (
[调拨单编号] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[日期] [smalldatetime] NULL ,
[源库房] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[目标库房] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[制单人] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[审核状态] [bit] NULL ,
[金额] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[调拨出库单明细]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[调拨出库单明细]
GO

CREATE TABLE [dbo].[调拨出库单明细] (
[编号] [int] IDENTITY (1, 1) NOT NULL ,
[调拨单编号] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[药品编号] [int] NULL ,
[调拨数量] [decimal](12, 2) NULL ,
[单位] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[出库单价] [decimal](12, 2) NULL ,
[金额] [decimal](26, 2) NULL
) ON [PRIMARY]
GO

[昵称被占用了]

给点数据

[lee_2020]

那个库存表上的库存金额可以不做处理
库存表数据：
1,10001,"dddd","060508",2008-05-01 00:00:00,700.00,"瓶 ","西药房 ",34.00
2,10001,"dddd","060201",2020-03-30 00:00:00,900.00,"瓶","西药房 ",20.00
3,10001,"dddd","040403",2020-02-22 00:00:00,800.00,"瓶","中药房 ",3.00
7,10002,"cccc","060531",2008-05-01 00:00:00,200.00,"瓶","西药房",56.00

出库单明细：


1,"20060504001",10001,800.00,"瓶 ",1.00,800.00
2,"20060504001",10002,100.00,"瓶 ",1.00,100.00

出库单明细上对应的出库单主单上编号是“20060504001”，库房为“西药房”，这个是从调拨出库单主单上给出出库单编号就可以查出是从哪个库房出库的。

[巴拉莱卡]

你的出库单是从源库到目标库？？？
如果源库数量不够再从非源库里便调？？
那么你的目标库是不是也在源库中阿
到底是库房间批次合并调动，，还是调出来卖阿。。。。
欧家也是卖药的，，，，
这些库房间有很多种关系的，不知道是指那种阿

[lee_2020]

呵呵，我没有说清楚。不存在源库库存不够调拨这种情况。因为原库里同一种存在不同批次，所以要按照批号从小到大去出库。像我给出的数据要把那张调拨出库单上的药品从西药房调出来，那么想要的最终结果就是：库存表变成了：
1,10001,"dddd","060508",2008-05-01 00:00:00,700.00,"瓶","西药房",34.00
2,10001,"dddd","060201",2020-03-30 00:00:00,900.00,"瓶","西药房",20.00
3,10001,"dddd","040403",2020-02-22 00:00:00,0.00,"瓶","中药房",3.00
7,10002,"cccc","060531",2008-05-01 00:00:00,100.00,"瓶","西药房",56.00
如果上面那个出库单上10001的出库数量是1000，那么就再从第2条记录里扣除200，这样您明白了吗？