从临时表变更 [英] Change from Temp Table

问题描述

我正在更新报告（不是由我创建的）并且有一个使用临时表。

1。有没有办法在不使用临时表的情况下更好地编写查询？

2。我应该在哪里添加代码以包含另一个表中的另一列？ （例如：-jOIN rcitem on rcitem.identity_column = glmast.identity_column）

 DECLARE @StartDate DATETIME 
 DECLARE @EndDate DATETIME 
 
选择rcmast.freceiver，rcmast.fdaterecv，rcmast.fpono，rcmast.fpacklist，rcmast.fvendno，
 rcitem.fpartno，rcitem.fpartrev，rcitem.fpoitemno，rcitem.fqtyrecv，rcitem.fmeasure， rcitem.fcategory 
 INTO＃rc1 
来自rcitem 
加入rcmast on rcitem.freceiver = rcmast.freceiver 
其中rcmast.fdaterecv介于（@StartDate）和（@EndDate）$ b之间
 $ b选择＃rc1.freceiver，fdaterecv，＃rc1.fpono，＃rc1.fpoitemno，fpacklist，fvendno，＃rc1.fpartno，
＃rc1.fpartrev，fqtyrecv，fucostonly，sum（fqtyrecv * fucostonly）作为最终成本，＃rc1.fmeasure，＃rc1.fcategory 
 INTO＃po1 
来自＃rc1 
加入poitem＃rc1.fpono +＃rc1.fpoitemno = poitem.fpono + poitem .fitemno 
 group by＃rc1.freceiver，fdaterecv，＃rc1.fpono，＃rc1.fpoitemno，fpacklist，fvendno，＃rc1.fpartno，
 #r c1.fpartrev，fqtyrecv，fucostonly，＃rc1.fmeasure，＃rc1.fcategory 
 
 
选择＃po1.freceiver，＃po1.fpono，fpoitemno，＃po1.fdaterecv，＃po1。 fvendno，rcmast.fcompany，＃po1.fpacklist，＃po1.fpartno，
 coalesce（inmast.fdescript，''）如fdescript，fqtyrecv，fucostonly，finalcost，＃po1.fmeasure，＃po1.fcategory，pocatg。 fglacct 
来自＃po1 
离开加入inmast＃po1.fpartno +＃po1.fpartrev = inmast.fpartno + inmast.frev 
加入rcmast＃po1.freceiver = rcmast.freceiver 
 
 
其中＃po1.fpono不喜欢'c％'和fucostonly<> 0和pocatg.fdesc不喜欢'％not use％'
由freceiver订购
 
 
 drop table＃rc1 
 drop table＃po1 

谢谢！

---

我听到了，我忘记了。我看，我记得。我明白了。

解决方案

不确定  pocatg.fdesc是什么。 

 DECLARE @StartDate DATETIME 
 DECLARE @EndDate DATETIME 
 
 SELECT po1.freceiver，
 po1.fpono，
 fpoitemno，
 po1.fdaterecv，
 po1.fvendno，
 rcmast.fcompany，
 po1.fpacklist，
 po1.fpartno， 
 coalesce（inmast.fdescript，''）为fdescript，
 fqtyrecv，
 fucostonly，
 finalcost，
 po1.fmeasure，
 po1.fcategory， 
 pocatg.fglacct 
 FROM（
 SELECT rc1.freceiver，
 fdaterecv，
 rc1.fpono，
 rc1.fpoitemno，
 fpacklist， 
 fvendno，
 rc1.fpartno，
 rc1.fpartrev，
 fqtyrecv，
 fucostonly，sum（fqtyrecv * fucostonly）as finalcost，
 rc1.fmeasure ，
 rc1.fcategory 
 FROM（
 SELECT rcmast.freceiver，
 rcmast.fdaterecv，
 rcmast.fpono，
 rcmast.fpacklist，
 rcmast.fvendno，
 rcitem.fpartno，
 rcitem。 fpartrev，
 rcitem.fpoitemno，
 rcitem.fqtyrecv，
 rcitem.fmeasure，
 rcitem.fcategory 
 FROM rcitem 
在rcitem.freceiver上加入rcmast = rcmast.freceiver 
 WHERE rcmast.fdaterecv介于（@StartDate）和（@EndDate）
）AS rc1 
 JOIN poitem on rc1.fpono + rc1.fpoitemno = poitem.fpono + poitem.fitemno 
 GROUP BY rc1.freceiver，
 fdaterecv，
 rc1.fpono，
 rc1.fpoitemno，
 fpacklist，
 fvendno，
 rc1。 fpartno，
 rc1.fpartrev，
 fqtyrecv，
 fucostonly，
 rc1.fmeasure，
 rc1.fcategory 
）AS po1 
 LEFT JOIN po1.fpartno上的inmast + po1.fpartrev = inmast.fpartno + inmast.frev 
在po1.freceiver上加入rcmast = rcmast.freceiver 
 WHERE po1.fpono not like' c％'和fucostonly<> 0和pocatg.fdesc不喜欢'％not use％'
 ORDER BY freceiver 

< /跨度>

I'm currently updating reports (not created by me) and have one that uses Temp Tables.

1. Is there a way to better write the query without using Temp Tables?

2. Where should I add code to include one more column from another table? (example: --JOIN rcitem on rcitem.identity_column = glmast.identity_column)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

select rcmast.freceiver, rcmast.fdaterecv, rcmast.fpono, rcmast.fpacklist, rcmast.fvendno,
		rcitem.fpartno, rcitem.fpartrev, rcitem.fpoitemno, rcitem.fqtyrecv, rcitem.fmeasure,rcitem.fcategory
	INTO #rc1
	from rcitem
	join rcmast on rcitem.freceiver = rcmast.freceiver
		where rcmast.fdaterecv between (@StartDate) and (@EndDate)

select #rc1.freceiver, fdaterecv, #rc1.fpono, #rc1.fpoitemno, fpacklist, fvendno, #rc1.fpartno, 
		#rc1.fpartrev, fqtyrecv, fucostonly, sum(fqtyrecv * fucostonly) as finalcost, #rc1.fmeasure, #rc1.fcategory
	INTO #po1
	from #rc1
	join poitem on #rc1.fpono + #rc1.fpoitemno = poitem.fpono + poitem.fitemno
		group by #rc1.freceiver, fdaterecv, #rc1.fpono, #rc1.fpoitemno, fpacklist, fvendno, #rc1.fpartno, 
				  #rc1.fpartrev, fqtyrecv, fucostonly, #rc1.fmeasure, #rc1.fcategory


select #po1.freceiver, #po1.fpono, fpoitemno, #po1.fdaterecv, #po1.fvendno, rcmast.fcompany, #po1.fpacklist, #po1.fpartno,
		coalesce(inmast.fdescript,' ') as fdescript, fqtyrecv, fucostonly, finalcost, #po1.fmeasure,#po1.fcategory,pocatg.fglacct
	from #po1
	left join inmast on #po1.fpartno + #po1.fpartrev = inmast.fpartno + inmast.frev
	join rcmast on #po1.freceiver = rcmast.freceiver

	
	where #po1.fpono not like 'c%' and fucostonly <> 0 and pocatg.fdesc Not Like '%not use%'
		Order by freceiver 


drop table #rc1
drop table #po1  

Thanks!

---

I hear and I forget. I see and I remember. I do and I understand.

解决方案

Not sure what pocatg.fdesc is. 

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT po1.freceiver, 
	   po1.fpono, 
	   fpoitemno, 
	   po1.fdaterecv, 
	   po1.fvendno, 
	   rcmast.fcompany, 
	   po1.fpacklist, 
	   po1.fpartno,
	   coalesce(inmast.fdescript,' ') as fdescript, 
	   fqtyrecv, 
	   fucostonly, 
	   finalcost, 
	   po1.fmeasure,
	   po1.fcategory,
	   pocatg.fglacct
FROM (
	SELECT rc1.freceiver, 
		   fdaterecv, 
		   rc1.fpono, 
		   rc1.fpoitemno, 
		   fpacklist, 
		   fvendno, 
		   rc1.fpartno, 
		   rc1.fpartrev, 
		   fqtyrecv, 
		   fucostonly, sum(fqtyrecv * fucostonly) as finalcost, 
		   rc1.fmeasure, 
		   rc1.fcategory
	FROM (
		SELECT rcmast.freceiver, 
			   rcmast.fdaterecv, 
			   rcmast.fpono, 
			   rcmast.fpacklist, 
			   rcmast.fvendno,
			   rcitem.fpartno, 
			   rcitem.fpartrev, 
			   rcitem.fpoitemno, 
			   rcitem.fqtyrecv, 
			   rcitem.fmeasure,
			   rcitem.fcategory
		FROM rcitem
		JOIN rcmast on rcitem.freceiver = rcmast.freceiver
		WHERE rcmast.fdaterecv between (@StartDate) and (@EndDate)
	) AS rc1 
	JOIN poitem on rc1.fpono + rc1.fpoitemno = poitem.fpono + poitem.fitemno
	GROUP BY rc1.freceiver, 
			 fdaterecv, 
			 rc1.fpono, 
			 rc1.fpoitemno, 
			 fpacklist, 
			 fvendno, 
			 rc1.fpartno, 
			 rc1.fpartrev, 
			 fqtyrecv, 
			 fucostonly, 
			 rc1.fmeasure, 
			 rc1.fcategory
) AS po1
LEFT JOIN inmast on po1.fpartno + po1.fpartrev = inmast.fpartno + inmast.frev
JOIN rcmast on po1.freceiver = rcmast.freceiver
WHERE po1.fpono not like 'c%' and fucostonly <> 0 and pocatg.fdesc Not Like '%not use%'
ORDER BY freceiver

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