这个sql怎么写啊,我晕了...有没有人指导一下
采购单(ID,采购编号)
入库单(ID,采购单ID,入库时间)
入库详情(ID,入库数量,零件ID)
库存(ID,零件ID,库存数量)<零件ID 唯一>
要达到的效果:
根据库存表 零件ID 比对入库详情 零件ID
按照入库时间降序 抽取入库详情第一条数据,当入库数量>=库存数量 就抽取这一条数据
当入库数量<库存数量 继续抽取该零件ID的第二条,第三条 直到sum(入库数量)>=库存数量
然后 再比对下一个零件ID
零件ID 入库量 入库时间 采购单
1234 70 2012-05-30 pur-01
1234 60 2012-05-23 pur-02
1234 50 2012-05-21 pur-03
1111 60 2012-05-30 pur-04
1111 50 2012-05-21 pur-05
零件ID 库存数量
1234 100
1111 50
显示库存数量:如零件ID 1234 第一条数据(入库:70 库存:70) 第二条(入库:60 库存:100-70=30)
查询结果为:
零件ID 入库量 入库时间 采购单 库存数量
1234 70 2012-05-30 pur-01 70
1234 60 2012-05-23 pur-02 30
1111 60 2012-05-30 pur-04 50
sql建表,insert语句
CREATE TABLE [dbo].[PurchaseOrder](
[ID] [int] NOT NULL,[PurchaseCode] [varchar](50) NOT NULL)
CREATE TABLE [dbo].[StockInOrder](
[ID] [int] NOT NULL,[PurchaseID] [int] NOT NULL,[StockInDate] [date] NOT NULL)
CREATE TABLE [dbo].[StockInOrderDetail](
[ID] [int] NOT NULL,[StockInID] [int] NOT NULL,
[StockInQty] [int] NOT NULL,[ItemID] [int] NOT NULL)
CREATE TABLE [dbo].[Reserve](
[ID] [int] NOT NULL,[ItemID] [int] NOT NULL,[ReserveQty] [int] NOT NULL)
GO
INSERT INTO PurchaseOrder (ID,PURCHASECODE) VALUES (1,'pur-001')
INSERT INTO PurchaseOrder (ID,PURCHASECODE) VALUES (2,'pur-002')
INSERT INTO PurchaseOrder (ID,PURCHASECODE) VALUES (3,'pur-003')
INSERT INTO PurchaseOrder (ID,PURCHASECODE) VALUES (4,'pur-004')
INSERT INTO PurchaseOrder (ID,PURCHASECODE) VALUES (5,'pur-005')
INSERT INTO StockInOrder(ID,PurchaseID,StockInDate)VALUES (1,1,'2012-05-21')
INSERT INTO StockInOrder(ID,PurchaseID,StockInDate)VALUES (2,1,'2012-05-23')
INSERT INTO StockInOrder(ID,PurchaseID,StockInDate)VALUES (3,1,'2012-05-30')
INSERT INTO StockInOrder(ID,PurchaseID,StockInDate)VALUES (4,2,'2012-05-30')
INSERT INTO StockInOrder(ID,PurchaseID,StockInDate)VALUES (5,2,'2012-05-30')
INSERT INTO StockInOrder(ID,PurchaseID,StockInDate)VALUES (6,3,'2012-05-30')
INSERT INTO StockInOrderDetail(ID,StockInID,StockInQty,ItemID)VALUES(1,1,50,1234)
INSERT INTO StockInOrderDetail(ID,StockInID,StockInQty,ItemID)VALUES(2,2,60,1234)
INSERT INTO StockInOrderDetail(ID,StockInID,StockInQty,ItemID)VALUES(3,1,100,1111)
INSERT INTO StockInOrderDetail(ID,StockInID,StockInQty,ItemID)VALUES(4,3,70,1234)
INSERT INTO StockInOrderDetail(ID,StockInID,StockInQty,ItemID)VALUES(6,4,80,1222)
INSERT INTO Reserve(ID,ItemID,ReserveQty)VALUES (1,1234,100)
INSERT INTO Reserve(ID,ItemID,ReserveQty)VALUES (2,1111,50)
INSERT INTO Reserve(ID,ItemID,ReserveQty)VALUES (3,1222,120)