需要临时表帮助 [英] Need help for Temporary Table

问题描述

create table #tempTable
(
NM char,
FactorName varchar (200),
FactorVal float,
docID int
)

exec
('insert into #tempTable 
   select
    case when charindex(''new'',lower(F.FactorName))>0 then ''N'' 
   else 
   case when charindex(''modif'',lower(F.FactorName))>0 then ''M''
   else ''O'' 
   end 
   end 
   as ''NM'', F.FactorName as ''FactorName'', 
   I.FactorVal as ''FactorVal'',
   p.DocID as ''docID'' 
  
       from pdInventory I, Factor F, Projects P , EstimatorDesign E
       where      I.PrjActualID=P.PrjActualID and
                  E.ModelType = ''H'' and F.ModelId=E.ModelID and
                  P.DocId in (''201'') and
                  F.FactorName not in (select FactorName from EstDefaultFactors)')

这是我编写的用于从Projects表中获取FactorNames，Factor Value，Factor Type(new''N''或经过修改的''M'')并存储在Temporary表中的代码.但是，这种返回因素不一而足，每个因素都会重复多次.

This is the code I have written to get FactorNames, Factor Value ,Factor Type(new''N'' or modified ''M'') from Projects Table and store in Temporary table . But this return lacs of factors and each factor is repeated multiple times.

推荐答案

您的联接不正确.当我更新查询时，表Factor或表EstimatorDesign没有匹配的联接以产生良好的结果.
根据您的查询，这就是您的要求

Your joins are incorrect. When I update your query the table Factor or table EstimatorDesign has no matching joins to have a good result.
Based on your query this is what you ask

select
	case when charindex('new',lower(F.FactorName))>0 then 'N' 
	else 
	case when charindex('modif',lower(F.FactorName))>0 then 'M'
	else 'O' 
	end as 'NM', 
	F.FactorName as 'FactorName', 
	I.FactorVal as 'FactorVal',
	p.DocID as 'docID' 
	into #tempTable
    from pdInventory as I
    JOIN Factor as F on f.?? = ??
    JOIN Projects as P on P.PrjActualID = I.PrjActualID 
    JOIN EstimatorDesign as E on f.modelID = e.ModelID 
     where f.Factorname  not in (select FactorName from EstDefaultFactors)
     and  E.ModelType = 'H'
     and p.DocID in ('201')

当您不再需要#temptable时，请不要以DROP TABLE #tempTable结尾.

don''t for get to end with DROP TABLE #tempTable when you do not need the #temptable anymore.

这篇关于需要临时表帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！