SQL Server 中的等效收益返回

2021-09-17 00:00:00 sql yield sql-server view

我在 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

视图的结果应该是:

Select Statement 1
UNION
Select Statement 2

预期行为与 C# 中的 yield return 相同.有没有办法告诉视图哪些 SELECT 语句实际上是结果的一部分，哪些不是?因为我需要的前面的小计算也包含选择.

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

结果当然是所有外部 SELECT 语句的 UNION.

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

相关文章