select d.项目编号,isnull((select top(1) 客商名称 from ClientInfo where 客商编号 in(select 供应商编号 from
BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData where 入库单号 in(select
入库单号 from InStorageInfo where 项目编号 = d.项目编号 ))) group by 客商名称),'') as 供应商,
isnull((select top(1) 货品名称 from BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
货品名称),'') as 物料名称,
isnull((select top(1) 规格型号 from BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
规格型号),'') as 规格型号,
isnull((select top(1) 单位 from BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
单位),'') as 单位,
isnull((select top(1) 单价 from BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
单价),'') as 单价,
isnull((select top(1) 入库数量 from InStorageData where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 ) group by
入库数量),'') as 入库数量, --在此处增加"入库金额"
isnull((select top(1) 出库数量 from OutStorageData where 出库单号 in(select 出库单号 from OutStorageInfo where 项目编号 = d.项目编号 ) group by
出库数量),'') as 出库数量 ,--在此处增加"出库金额"
isnull((select top(1) 实盘数量 from CheckData where 盘点单号 in (select 盘点单号 from CheckInfo where 盘点日期 = '2010-06-1' ) and 物料编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
实盘数量),'') as 上月结存,--在此处增加"上月结存金额"isnull((select top(1) 实盘数量 from CheckData where 盘点单号 in (select 盘点单号 from CheckInfo where 盘点日期 = '2010-07-1' ) and 物料编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
实盘数量),'') as 本月结存 --在此处增加"本月结存金额"
from DesignBook d/*问题是这样的,我需要在,入库金额,出库金额,上月结存金额,本月结存金额 的列值为自动计算的值来补充。
入库金额 = 入库数量 * 单价
出库金额 = 出库数量 * 单价
上月结存金额 = 上月结存数量 * 单价
本月结存金额 = 本月结存数量 * 单价
*/
以下是表结构
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InStorageData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InStorageData](
[入库单号] [int] NULL,
[物料编号] [int] NULL,
[入库数量] [nchar](10) NULL,
[备注] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InStorageInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InStorageInfo](
[入库单号] [int] IDENTITY(1,1) NOT NULL,
[入库类别] [nchar](10) NULL,
[依据单号] [nvarchar](50) NULL,
[物料来源] [nvarchar](50) NULL,
[项目编号] [nvarchar](50) NULL,
[经手人] [nvarchar](50) NULL,
[送货人] [nvarchar](50) NULL,
[登记人] [nvarchar](50) NULL,
[收货日期] [datetime] NULL,
[登记日期] [datetime] NULL CONSTRAINT [DF_InStorageInfo_登记日期] DEFAULT (getdate()),
[备注] [nvarchar](max) NULL,
CONSTRAINT [PK_InStorageInfo] PRIMARY KEY CLUSTERED
(
[入库单号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OutStorageData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OutStorageData](
[出库单号] [nvarchar](50) NULL,
[物料编号] [int] NULL,
[出库数量] [nchar](10) NULL,
[备注] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OutStorageInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OutStorageInfo](
[出库单号] [nvarchar](50) NOT NULL,
[出库类别] [nchar](10) NULL,
[项目编号] [nvarchar](50) NULL,
[转移地点] [nvarchar](50) NULL,
[发货人] [nvarchar](50) NULL,
[接收人] [nvarchar](50) NULL,
[登记人] [nvarchar](50) NULL,
[发货日期] [datetime] NULL,
[登记日期] [datetime] NULL CONSTRAINT [DF_OutStorageInfo_登记日期] DEFAULT (getdate()),
[备注] [nvarchar](max) NULL,
CONSTRAINT [PK_OutStorageInfo] PRIMARY KEY CLUSTERED
(
[出库单号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CheckData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CheckData](
[盘点单号] [nvarchar](50) NULL,
[物料编号] [int] NULL,
[实盘数量] [nchar](10) NULL,
[盘亏数量] [nchar](10) NULL,
[备注] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CheckInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CheckInfo](
[盘点单号] [int] IDENTITY(1,1) NOT NULL,
[盘点区域] [nvarchar](50) NULL,
[盘点人] [nvarchar](50) NULL,
[盘点日期] [datetime] NULL,
[登记人] [nvarchar](50) NULL,
[登记日期] [datetime] NULL CONSTRAINT [DF_CheckInfo_登记日期] DEFAULT (getdate()),
[备注] [nvarchar](max) NULL,
CONSTRAINT [PK_CheckInfo] PRIMARY KEY CLUSTERED
(
[盘点单号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BuysProductInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BuysProductInfo](
[货品编号] [int] IDENTITY(1,1) NOT NULL,
[供应商编号] [int] NULL,
[货品名称] [nvarchar](50) NULL,
[货品类别] [nvarchar](50) NULL,
[规格型号] [nvarchar](50) NULL,
[单位] [nchar](10) NULL,
[现有库存] [nchar](10) NULL,
[密度] [nchar](10) NULL,
[重量] [nchar](10) NULL,
[单价] [nchar](10) NULL,
[存储区域] [nvarchar](50) NULL,
[货架编号] [nvarchar](50) NULL,
[安全库存] [nchar](10) NULL,
[最大库存] [nchar](10) NULL,
[生产批文] [nvarchar](100) NULL,
[质量标准] [nvarchar](50) NULL,
[备注] [nvarchar](max) NULL,
[登记日期] [datetime] NULL CONSTRAINT [DF_BuysProductInfo_登记日期] DEFAULT (getdate()),
[登记人] [nvarchar](50) NULL,
[仓库管理员] [nvarchar](50) NULL,
[采购员] [nvarchar](50) NULL,
CONSTRAINT [PK_BuysProductInfo] PRIMARY KEY CLUSTERED
(
[货品编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData where 入库单号 in(select
入库单号 from InStorageInfo where 项目编号 = d.项目编号 ))) group by 客商名称),'') as 供应商,
isnull((select top(1) 货品名称 from BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
货品名称),'') as 物料名称,
isnull((select top(1) 规格型号 from BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
规格型号),'') as 规格型号,
isnull((select top(1) 单位 from BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
单位),'') as 单位,
isnull((select top(1) 单价 from BuysProductInfo where 货品编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
单价),'') as 单价,
isnull((select top(1) 入库数量 from InStorageData where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 ) group by
入库数量),'') as 入库数量, --在此处增加"入库金额"
isnull((select top(1) 出库数量 from OutStorageData where 出库单号 in(select 出库单号 from OutStorageInfo where 项目编号 = d.项目编号 ) group by
出库数量),'') as 出库数量 ,--在此处增加"出库金额"
isnull((select top(1) 实盘数量 from CheckData where 盘点单号 in (select 盘点单号 from CheckInfo where 盘点日期 = '2010-06-1' ) and 物料编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
实盘数量),'') as 上月结存,--在此处增加"上月结存金额"isnull((select top(1) 实盘数量 from CheckData where 盘点单号 in (select 盘点单号 from CheckInfo where 盘点日期 = '2010-07-1' ) and 物料编号 in (select 物料编号 from InStorageData
where 入库单号 in(select 入库单号 from InStorageInfo where 项目编号 = d.项目编号 )) group by
实盘数量),'') as 本月结存 --在此处增加"本月结存金额"
from DesignBook d/*问题是这样的,我需要在,入库金额,出库金额,上月结存金额,本月结存金额 的列值为自动计算的值来补充。
入库金额 = 入库数量 * 单价
出库金额 = 出库数量 * 单价
上月结存金额 = 上月结存数量 * 单价
本月结存金额 = 本月结存数量 * 单价
*/
以下是表结构
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InStorageData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InStorageData](
[入库单号] [int] NULL,
[物料编号] [int] NULL,
[入库数量] [nchar](10) NULL,
[备注] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InStorageInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InStorageInfo](
[入库单号] [int] IDENTITY(1,1) NOT NULL,
[入库类别] [nchar](10) NULL,
[依据单号] [nvarchar](50) NULL,
[物料来源] [nvarchar](50) NULL,
[项目编号] [nvarchar](50) NULL,
[经手人] [nvarchar](50) NULL,
[送货人] [nvarchar](50) NULL,
[登记人] [nvarchar](50) NULL,
[收货日期] [datetime] NULL,
[登记日期] [datetime] NULL CONSTRAINT [DF_InStorageInfo_登记日期] DEFAULT (getdate()),
[备注] [nvarchar](max) NULL,
CONSTRAINT [PK_InStorageInfo] PRIMARY KEY CLUSTERED
(
[入库单号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OutStorageData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OutStorageData](
[出库单号] [nvarchar](50) NULL,
[物料编号] [int] NULL,
[出库数量] [nchar](10) NULL,
[备注] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OutStorageInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OutStorageInfo](
[出库单号] [nvarchar](50) NOT NULL,
[出库类别] [nchar](10) NULL,
[项目编号] [nvarchar](50) NULL,
[转移地点] [nvarchar](50) NULL,
[发货人] [nvarchar](50) NULL,
[接收人] [nvarchar](50) NULL,
[登记人] [nvarchar](50) NULL,
[发货日期] [datetime] NULL,
[登记日期] [datetime] NULL CONSTRAINT [DF_OutStorageInfo_登记日期] DEFAULT (getdate()),
[备注] [nvarchar](max) NULL,
CONSTRAINT [PK_OutStorageInfo] PRIMARY KEY CLUSTERED
(
[出库单号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CheckData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CheckData](
[盘点单号] [nvarchar](50) NULL,
[物料编号] [int] NULL,
[实盘数量] [nchar](10) NULL,
[盘亏数量] [nchar](10) NULL,
[备注] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CheckInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CheckInfo](
[盘点单号] [int] IDENTITY(1,1) NOT NULL,
[盘点区域] [nvarchar](50) NULL,
[盘点人] [nvarchar](50) NULL,
[盘点日期] [datetime] NULL,
[登记人] [nvarchar](50) NULL,
[登记日期] [datetime] NULL CONSTRAINT [DF_CheckInfo_登记日期] DEFAULT (getdate()),
[备注] [nvarchar](max) NULL,
CONSTRAINT [PK_CheckInfo] PRIMARY KEY CLUSTERED
(
[盘点单号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BuysProductInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BuysProductInfo](
[货品编号] [int] IDENTITY(1,1) NOT NULL,
[供应商编号] [int] NULL,
[货品名称] [nvarchar](50) NULL,
[货品类别] [nvarchar](50) NULL,
[规格型号] [nvarchar](50) NULL,
[单位] [nchar](10) NULL,
[现有库存] [nchar](10) NULL,
[密度] [nchar](10) NULL,
[重量] [nchar](10) NULL,
[单价] [nchar](10) NULL,
[存储区域] [nvarchar](50) NULL,
[货架编号] [nvarchar](50) NULL,
[安全库存] [nchar](10) NULL,
[最大库存] [nchar](10) NULL,
[生产批文] [nvarchar](100) NULL,
[质量标准] [nvarchar](50) NULL,
[备注] [nvarchar](max) NULL,
[登记日期] [datetime] NULL CONSTRAINT [DF_BuysProductInfo_登记日期] DEFAULT (getdate()),
[登记人] [nvarchar](50) NULL,
[仓库管理员] [nvarchar](50) NULL,
[采购员] [nvarchar](50) NULL,
CONSTRAINT [PK_BuysProductInfo] PRIMARY KEY CLUSTERED
(
[货品编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
解决方案 »
- 求一统计SQL语句
- 行转列
- 新手请教一个模糊查询问题
- 疑难问题:将一个表中删除的信息备份到其他表中
- insert到指定的行,怎么搞?
- 请问SQL Server 数据库的扩展名是什么?
- 多表(4)关联 插入语句 ^_ 表1,表2,表3 中的主键作为表4的字段,插入表4; 其中表4有自己的主键
- 怎样实现在SQL2005的查询分析器中写一个JOb可以自动查找一个表的昨天的记录数,然后生成Excel文件,再自动发送相关Email,请问可以吗?
- 急,昨天把数据库导出之后,然后重新导入,就出现很多表的id字段是不允许空的,可是原本的数据库就是不允许空的,为什么恢复了之后出问题???
- 如何用ms-sql的數據來更新mysql的數據
- 高分求助
- 要会员管理系统,进销存,和网站整合,有好多方法吗?
例如,select 数量,单价 from table
在存储过程中计算 数量 * 单价 = 总额
显示
数量 单价 总额注意,查询结果中的数据不至一行。
谢谢!