如何在where子句中使用预先使用的子查询?

I'm putting together a long query that after much experimentation I've found I can only do through subqueries.

我正在整理一个长长的查询，经过大量的实验，我发现我只能通过子查询来完成。

The subqueries are joined against a root table containing a list of financial periods. I'd like to filter the results by referencing the subqueries in the where clause without repeating the subqueries.

子查询与包含财务周期列表的根表连接。我希望通过在where子句中引用子查询来过滤结果，而不重复子查询。

IE:

即:

Select  Year
        , Period
        , (Select.... ) as Col1
        , (Select.... ) as Col2
        , (Select.... ) as Col3
Where   Col1 > 0 or Col2 > 0 or Col3 > 0

I know I can't reference the subqueries by the given names and I've looked into referring by number but don't see any promise there. What I've ended up doing is putting the query in a stored procedure which uses it to populate a temporary table and select from that with the appropriate where clause.

我知道我不能用给定的名字来引用子查询，我也研究过用数字来引用，但是没有任何承诺。我最后做的是将查询放入一个存储过程中，该过程使用它来填充临时表，并根据where子句进行选择。

Select .... into #Temp
Select * From #Temp Where Col1 > 0 or Col2 > 0 or Col3 > 0

Is there a cleaner or more efficient way to go about this?

有更清洁或更有效的方法来解决这个问题吗?

Any thoughts?

任何想法吗?

1 个解决方案

#1

If you're using SQL Server 2005 or greater, you can use a CTE:

如果您正在使用SQL Server 2005或更高版本，您可以使用CTE:

;WITH Result (Year, Period, Col1, Col2, Col3) AS
(
    Select Year, Period, (Select.... ) as Col1, (Select.... ) as Col2...
)
Select *
From Result
Where Col1 > 0 or Col2 > 0 or Col3 > 0

Or, if you're using a version that doesn't support CTEs, you can treat your query as an intermediate result, and then have an outer query that applies the filtering:

或者，如果你使用的是不支持CTEs的版本，你可以把你的查询当作一个中间结果，然后有一个应用过滤的外部查询:

Select
    *
from
(
    Select Year, Period, (Select.... ) as Col1, (Select.... ) as Col2...
) q
Where Col1 > 0 or Col2 > 0 or Col3 > 0

#1