問題描述

目前我的代碼是這樣的.

Currently my code is something like this.

select * 
from tblReq 
where ReqID in (select ReqID from tblLog where LogDate >= '2015/04/01' and LogDate < '2015/05/31')

只是想知道數(shù)據(jù)庫實(shí)際上是如何找到這個(gè)查詢的結(jié)果的?每次在子查詢中運(yùn)行時(shí)都會(huì)重新運(yùn)行嗎?是否有任何腳本可以將結(jié)果列表存儲(chǔ)在某個(gè)變量中并能夠重新使用它?(以下代碼)

Just wondering how actually the database find the result for this query? Is it re-run every time it ran in sub-query? And is there any script where I can store the list of result in some variable and able to use it back? (below code)

select @logs = tblLog.ReqID from tblLog where tblLog.LogDate >= '2015/04/01' and tblLog.LogDate < '2015/05/31'  
select * from tblReq where ReqID in (@logs)

推薦答案

是的，您可以將結(jié)果存儲(chǔ)在變量中并在以后多次重復(fù)使用.在您的情況下，它將是一個(gè) table 變量，因?yàn)槟梢杂卸鄠€(gè)項(xiàng)目.然后，簡單的 join 到初始查詢:

Yes, you can store the result in a variable and reuse it several times later. In your case, it will be a table variable as you could have multiple items. Then, simple join it to the initial query:

DECLARE @Logs TABLE
(
    [LogID] INT
);

INSERT INTO @Logs ([LogID])
Select tblLog.ReqID 
from tblLog 
where tblLog.LogDate >= '2015/04/01' 
    and tblLog.LogDate < '2015/05/31'  

select * 
from tblReq A
INNER JOIN  @Logs L
    ON A.ReqID = L.LogID

此外，這可能會(huì)損害您的查詢性能，因?yàn)楸碜兞坎幌癫樵儍?yōu)化器的黑匣子.如果您要存儲(chǔ)大量行，請(qǐng)改用 temporary 表以使用并行執(zhí)行計(jì)劃.

Also, this could harm your query performance, as table variables are not like black box for the query optimizer. If you are storing a large amount of rows, use temporary tables instead in order to use parallel execution plans.

這篇關(guān)于將 MS Sql 結(jié)果設(shè)置為變量并重用它的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！