一时表

Memory所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失ALTERproc[dbo].[e_pro_cost]
@typetinyint,
@IsWherenvarchar(1000)
as
begin
ifexists(select*fromtempdb.dbo.sysobjectswhereid=object_id(Ntempdb.dbo.#t_equ_cost))
droptable#t_equ_cost
createtable#t_equ_cost
(
Batchvarchar(50),
eCostPricefloat,
MOIDvarchar(50),
DateDatetime,
PMCPlannervarchar(50),
PMCPlannerNamevarchar(50)
)
insertinto#t_equ_costexece_equ_cost@type
end
--将野生信息存进一时表
begin
ifexists(select*fromtempdb.dbo.sysobjectswhereid=object_id(Ntempdb.dbo.#t_hum_cost))
droptable#t_hum_cost
createtable#t_hum_cost
(
Batchvarchar(50),
hCostPricefloat,
MOIDvarchar(50),
DateDatetime,
PMCPlannervarchar(50),
PMCPlannerNamevarchar(50)
)
insertinto#t_hum_costexece_hum_cost@type
end
begin
ifexists(select*fromtempdb.dbo.sysobjectswhereid=object_id(Ntempdb.dbo.#t_met_cost))
droptable#t_met_cost
createtable#t_met_cost
(
Batchvarchar(50),
mCostPricefloat,
MOIDvarchar(50),
DateDatetime,
PMCPlannervarchar(50),
PMCPlannerNamevarchar(50)
)
insertinto#t_met_costexece_met_cost@type
end
declare@sqlvarchar(2000)
set@sql=select*,(eCostPrice+hCostPrice+mCostPrice)asTotalCostPricefrom(
select
isnull(A.Batch,B.Batch)asBatch,
isnull(A.MOID,B.MOID)asMOID,
isnull(A.Date,B.Date)asDate,
isnull(A.PMCPlanner,B.PMCPlanner)asPMCPlanner,
isnull(A.PMCPlannerName,B.PMCPlannerName)asPMCPlannerName,
isnull(A.eCostPrice,0)aseCostPrice,
isnull(A.hCostPrice,0)ashCostPrice,
isnull(B.mCostPrice,0)asmCostPricefrom
(
select
isnull(A.Batch,B.Batch)asBatch,
isnull(A.MOID,B.MOID)asMOID,
isnull(A.Date,B.Date)asDate,
isnull(A.PMCPlanner,B.PMCPlanner)asPMCPlanner,
isnull(A.PMCPlannerName,B.PMCPlannerName)asPMCPlannerName,
A.eCostPrice,B.hCostPricefrom#t_equ_costAfullouterjoin#t_hum_costBonA.Batch=B.Batch
)
Afullouterjoin#t_met_costBonA.Batch=B.Batch
)Awhere+@IsWhere;
execute(@sql)
droptable#t_met_cost
droptable#t_hum_cost
droptable#t_equ_cost
GO
使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性