問題描述

我在 SQL Server (DWH) 中寫下一個視圖，用例偽代碼是:

I am writing down a view in SQL server (DWH) and the use case pseudo code is:

-- Do some calculation and generate #Temp1
-- ... contains other selects

-- Select statement 1
SELECT * FROM Foo
JOIN #Temp1 tmp on tmp.ID = Foo.ID
WHERE Foo.Deleted = 1

-- Do some calculation and generate #Temp2
-- ... contains other selects

-- Select statement 2
SELECT * FROM Foo
JOIN #Temp2 tmp on tmp.ID = Foo.ID
WHERE Foo.Deleted = 1

視圖的結(jié)果應(yīng)該是:

Select Statement 1
UNION
Select Statement 2

預(yù)期行為與 C# 中的 yield return 相同.有沒有辦法告訴視圖哪些 SELECT 語句實際上是結(jié)果的一部分，哪些不是?因為我需要的前面的小計算也包含選擇.

The intended behavior is the same as the yield returnin C#. Is there a way to tell the view which SELECT statements are actually part of the result and which are not? since the small calculations preceding what I need also contain selects.

謝謝！

推薦答案

我找到了更好的解決方法.它可能對其他人有幫助.其實就是把所有的計算都包含在 WITH 語句中，而不是在視圖核心中進行:

I found a better work around. It might be helpful for someone else. It is actually to include all the calculation inside WITH statements instead of doing them in the view core:

WITH Temp1 (ID)
AS
(
    -- Do some calculation and generate #Temp1
    -- ... contains other selects
)

, Temp2 (ID)
AS
(
    -- Do some calculation and generate #Temp2
    -- ... contains other selects
)

-- Select statement 1
SELECT * FROM Foo
JOIN Temp1 tmp on tmp.ID = Foo.ID
WHERE Foo.Deleted = 1

UNION

-- Select statement 2
SELECT * FROM Foo
JOIN Temp2 tmp on tmp.ID = Foo.ID
WHERE Foo.Deleted = 1

結(jié)果當(dāng)然是所有外部 SELECT 語句的 UNION.

The result will be of course the UNION of all the outiside SELECT statements.

這篇關(guān)于SQL Server 中的等效收益返回的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！