SQL Server - 如何将派生表用于计算字段？

Question

我试图在我的查询中的 WHERE 子句中使用计算字段值，经过一些研究我知道我需要创建一个派生表。但是我不确定语法，因为我的计算字段使用 CASE 语句，例如:

        CASE T.IsReassigned
        WHEN 1 THEN DATEDIFF(MINUTE,
                    (SELECT top 1 SXAVWFTaskHistory.CreatedWhenUTC from SXAVWFTaskHistory where TaskID = T.TaskID and StatusID = 7 order by TaskHistoryID desc),
                    SYSDATETIMEOFFSET())                        
        ELSE 
        CASE Stat.StatusID      
            WHEN 1 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET())    -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now 
            WHEN 2 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET())    -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now
            ELSE DATEDIFF(MINUTE,TSK.CreatedWhenUTC, TH.CreatedWhenUTC) 
        END 
    END as TaskItemAge,

然后我想在 where 子句中使用 TaskItemAge 值，例如:

WHERE TaskItemAge > @TaskAgeStart AND TaskItemAge < @TaskAgeEnd

如何将该 CASE 语句放入子选择中？

********* ***** 编辑 ************抱歉，我仍然对它的工作原理感到困惑。这是我的查询的较大片段:

    SELECT TSK.TaskID, --0
    TH.IsLatest,
    TH.CreatedWhenUTC as TaskHistoryCreationDate, --10
    TS.Name AS STATE,
    CASE T.IsReassigned
        WHEN 1 THEN DATEDIFF(MINUTE,
                    (SELECT top 1 SXAVWFTaskHistory.CreatedWhenUTC from SXAVWFTaskHistory where TaskID = T.TaskID and StatusID = 7 order by TaskHistoryID desc),
                    SYSDATETIMEOFFSET())                        
        ELSE 
        CASE Stat.StatusID      
            WHEN 1 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET())    -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now 
            WHEN 2 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET())    -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now
            ELSE DATEDIFF(MINUTE,TSK.CreatedWhenUTC, TH.CreatedWhenUTC) 
        END 
    END as TaskItemAge,
    ctx.ContextTypeName,  
    ctx.ContextDescription, --15
    TH.TouchedWhenUTC as TaskHistoryModifiedDate
INTO #ii        
FROM SXAVWFTask TSK 
INNER JOIN SXAVWFTaskHistory TH ON TSK.TaskID = TH.TaskID
INNER JOIN @PagedTemp T on TH.TaskHistoryID = T.TaskHistoryID

Where TaskItemAge > @TaskAgeStart AND TaskItemAge < @TaskAgeEnd

Answer 1

select  *
from    (
        select  case ... end as CaseColumn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   CaseColumn between 1 and 2