日期:2014-05-18 浏览次数:20924 次
[color=#FF0000]数据库为SQL2000[/color] CREATE TABLE [dbo].[P_加工]( [ID] [int] IDENTITY(1,1) NOT NULL, [PID] [int] NULL, [BillNo] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, [Type] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, [Qty] [numeric](18, 2) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[p_原料]( [ID] [int] IDENTITY(1,1) NOT NULL, [BillNo] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, [Type] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, [Qty] [numeric](18, 2) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[p_库存]( [BillNo] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL, [Type] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL, [Qty] [numeric](18, 2) NULL, [Price] [numeric](18, 2) NULL ) ON [PRIMARY] INSERT INTO [dbo].[P_原料]([PID],[BillNo],[Type],[Qty]) VALUES (1,'B20111201','OUT',50.00} INSERT INTO [dbo].[P_原料]([PID],[BillNo],[Type],[Qty]) VALUES (1,'B20111202','OUT',30.00} INSERT INTO [dbo].[P_原料]([PID],[BillNo],[Type],[Qty]) VALUES (2,'B20111203','IN',10.00} INSERT INTO [dbo].[P_原料]([PID],[BillNo],[Type],[Qty]) VALUES (2,'B20111202','OUT',15.00} INSERT INTO [dbo].[P_原料]([PID],[BillNo],[Type],[Qty]) VALUES (3,'B20111201','OUT',21.00} INSERT INTO [dbo].[p_加工]([BillNo],[Type],[Qty]) VALUES ('I20111201','OUT',75.00) INSERT INTO [dbo].[p_加工]([BillNo],[Type],[Qty]) VALUES ('I20111202','IN',50.00) INSERT INTO [dbo].[p_加工]([BillNo],[Type],[Qty]) VALUES ('I20111203','OUT',20.00) INSERT INTO [dbo].[p_库存]([BillNo],[Type],[Qty],[Price])VALUES('B20111201','OUT',200.00,150.00) INSERT INTO [dbo].[p_库存]([BillNo],[Type] ,[Qty] ,[Price]) VALUES('B20111202','OUT',100.00,120.00) INSERT INTO [dbo].[p_库存] ([BillNo],[Type],[Qty],[Price])VALUES('B20111203','IN',150.00,300.00) 表间关係:P_原料 表与 P_加工表通过PID关联,其中,PID为 p_加工表主键ID, 一条P_加工 表数据可以对应多条 P_原料表记录 P_原料 表与 P_库存 表通过 BillNO,Type字段关联 P_加工 表与 P_库存表无直接关係 逻辑关係:一种或多种原料加工成產品,產品可继续加工成其他產品 原料 產品 A + B C C + D E E + F G 需求:因只有库存表存储价格,单价为 QTY/Price,同时,加工存在损耗, 现在需要求出 各种原料以及產品的单价,即A,B,C,D,E,F,G的对应成本 举例说明 加工表有此记录 1 I20111201 OUT 75.00 对应原料表 1 1 B20111201 OUT 50.00 2 1 B20111202 OUT 30.00 同时,原料表的这2条记录对应 库存表记录 B20111201 OUT 200.00 150.00 B20111202 OUT 100.00 120.00 则其成本为: (50 * (200/150) + 30 * ( 100 / 120) ) / 75 就是 计算出库存表单价之后,用原料表所使用的数量 * 单价 ,然后 把所用所有原料 相加之后 除以加工表数量,就得出成本
;with scte as ( select a.PID,sum(a.qty*(b.qty/b.price)) s from p_库存 b,p_原料 a,P_加工 c where a.BillNo=b.BillNo and c.pid=a.PID group by a.PID ) select a.s/b.qty as 成本 from scte a,P_加工 b where a.PID=b.PID
------解决方案--------------------