如何在不使用临时表的情况下简化查询 [英] How to simplify the query without using temp table

问题描述

如何避免在下面的查询中使用Temp表

How to avoid the usage of Temp table in the below query

declare @TEMP table (QID int, Remainder varchar(max), RemainderDate varchar(max) )
insert into @Temp 

select  Agent_QID, Remainderdes, MAX(Rdate) as RemainderDate from tbl_TimeAlrets group by Agent_QID ,Remainderdes 



select cq.QuoteID, cq.LOBID, cu.UserName, q.ProcessedDate, cq.AgentStatus ,q.PolicyExpiredDate, q.Make,q.Model ,t.Remainder  
from  tr_CRM_QuoteInfo cq  left join tr_QuoteInfo q  on q.QuoteID = cq.QuoteID 

 left join tr_CRM_Users1 cu on cu. UserID = cq.AssignTo 
 left join @TEMP t   on cq.QuoteID = t.QID   where  (q.ProcessedDate >  DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) and q.ProcessedDate < CONVERT(date, getdate())) and (cq.AgentStatus like '%bought%'  or  cq.AgentStatus like '%Not Inte%')

什么我试过了：



刚写完查询，查询工作正常。



威斯康星州h在不使用临时表的情况下实现相同的功能

What I have tried:

Just now written the query, query working fine.

Wish to achieve the same functionality without using temp table

推荐答案

您可以尝试以下内容 -

You can try something like following-

select cq.QuoteID, cq.LOBID, cu.UserName, q.ProcessedDate, cq.AgentStatus ,q.PolicyExpiredDate, q.Make,q.Model ,t.Remainder 
from  tr_CRM_QuoteInfo cq  left join tr_QuoteInfo q  on q.QuoteID = cq.QuoteID 
left join tr_CRM_Users1 cu on cu. UserID = cq.AssignTo 
left join (
select  Agent_QID AS QID, Remainderdes AS Remainder, MAX(Rdate) as RemainderDate from tbl_TimeAlrets group by Agent_QID ,Remainderdes 
) t   on cq.QuoteID = t.QID   where  (q.ProcessedDate >  DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) and q.ProcessedDate < CONVERT(date, getdate())) and (cq.AgentStatus like '%bought%'  or  cq.AgentStatus like '%Not Inte%')

如果没有帮助，请让我知道。



谢谢

If it doesn't help, please let me know.

Thanks

这篇关于如何在不使用临时表的情况下简化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！