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

我试图在查询的 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

最佳答案

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

关于SQL Server - 如何将派生表用于计算字段？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14718505/