计算库存量：
SELECT TOP 100 PERCENT dbo.Office_In.InCode, dbo.Office_In.InDate, dbo.Office_In.ComCode, dbo.Office_In.TypeCode, dbo.Office_In.OName, dbo.Office_In.Spec, dbo.Office_In.Unit, dbo.Office_In.Qty, dbo.Office_In.Price, dbo.Office_In.Provider, dbo.Office_In.Master, dbo.Office_In.Note, dbo.Company.ComName, dbo.Office_Type.TypeName, dbo.Office_In.Qty - (SELECT isnull(SUM(Office_Out.Qty), 0) FROM Office_Out WHERE Office_In.InCode = Office_Out.InCode) AS Stock, (SELECT isnull(SUM(office_out.qty), 0) FROM office_out WHERE office_in.inCode = office_out.incode) AS outQtyFROM dbo.Office_Type INNER JOIN dbo.Office_In ON dbo.Office_Type.TypeCode = dbo.Office_In.TypeCode LEFT OUTER JOIN dbo.Company ON dbo.Office_In.ComCode = dbo.Company.ComCodeORDER BY dbo.Company.ComName, dbo.Office_Type.TypeName 在这个SQL语句，主要是利用isnull函数，将从数据库中查询到的为null的值，转化为0，之后利用减法，将库存计算出来。
附：结构表
/**//****** 对象：表 dbo.Office_In ******//**//****** 说明：办公用品入库表 ******/If Exists (Select * From sysobjects where id = object_id('dbo.Office_In'))Drop Table dbo.Office_InGOCreate Table Office_In( InCode varchar(12) Not Null Primary key,--入库编号 InDate smalldatetime Not Null, --入库日期 ComCode varchar(4) Not Null, --公司单位代码 TypeCode varchar(4) Not Null, --种类代码 OName varchar(50) Not Null, --办公用品名称 Spec varchar(50) Null, --规格 Unit varchar(10) Null, --单位 Qty Int Not Null, --数量 Price Numeric(8,2) Not Null, --单价 Provider varchar(50) Null, --购买单位 Master varchar(20) Not Null, --经办人 Note Varchar(100) Null --备注)GOALTER TABLE dbo.Office_In ADD CONSTRAINT FK_Office_In_ComPany FOREIGN KEY ( ComCode ) REFERENCES dbo.Company ( ComCode ), CONSTRAINT FK_Office_In_Type FOREIGN KEY ( TypeCode ) REFERENCES dbo.Office_Type ( TypeCode )GO /**//****** 对象：表 dbo.Office_Out ******//**//****** 说明：办公用品领用表 ******/If Exists (Select * From sysobjects where id = object_id('dbo.Office_Out'))Drop Table dbo.Office_OutGOCreate Table Office_Out( OutCode varchar(12) Not Null Primary key,--领用编号 ComCode varchar(4) Not Null, --公司单位代码 InCode varchar(12) Not Null, --入库编号 OutDate smallDatetime Not NUll, --领用日期 Qty Int Not Null, --数量 Master varchar(20) Not Null, --经办人 Note Varchar(100) Null --备注)GOALTER TABLE dbo.Office_Out ADD CONSTRAINT FK_Office_Out_ComPany FOREIGN KEY ( ComCode ) REFERENCES dbo.Company ( ComCode ), CONSTRAINT FK_Office_Out_In FOREIGN KEY ( InCode ) REFERENCES dbo.Office_In ( InCode )GO
|