Row_Number where子句中的列名无效

Question

下面我有一个存储过程，它应该只显示基于参数@From和@To的两个数字之间的记录。但是，当我运行这个命令时，我得到了一个错误:无效的列名RecordID。我希望能够运行这个过程，并根据where子句的内容调整RecordID列，我相信这段代码可以做到，但它不会运行。

 SELECT Alerts.*, ClaimRepairs.ClaimID,ClaimRepairs.VehicleRegistration,ClaimRepairs.RepairerName, ClaimRepairs.Status,Claims.HasComplaint,
     CASE 
        WHEN Deployments.DeploymentDate IS NOT NULL AND ClaimRepairs.ConfirmedOnSiteDate IS NOT NULL 
        THEN datediff(dd, Deployments.DeploymentDate, ClaimRepairs.ConfirmedOnSiteDate) - (datediff(wk, Deployments.DeploymentDate, ClaimRepairs.ConfirmedOnSiteDate) * 2) -
            case
                when datepart(dw, Deployments.DeploymentDate) = 1 then 1 else 0 end +
            case 
                when datepart(dw, ClaimRepairs.ConfirmedOnSiteDate) = 1 then 1 else 0 end
        WHEN Deployments.DeploymentDate IS NOT NULL AND ClaimRepairs.ConfirmedOnSiteDate IS NULL
        THEN datediff(dd, Deployments.DeploymentDate,GETDATE()) - (datediff(wk, Deployments.DeploymentDate, GETDATE()) * 2) -
            case 
                when datepart(dw, Deployments.DeploymentDate) = 1 then 1 else 0 end +
            case
                when datepart(dw, GETDATE()) = 1 then 1 else 0 end
        WHEN MRD.DeploymentDate IS NOT NULL AND ClaimRepairs.ConfirmedOnSiteDate IS NOT NULL
            THEN datediff(dd, MRD.DeploymentDate, ClaimRepairs.ConfirmedOnSiteDate) - (datediff(wk, MRD.DeploymentDate, ClaimRepairs.ConfirmedOnSiteDate) * 2) -
            case
                when datepart(dw, MRD.DeploymentDate) = 1 then 1 else 0 end +
            case 
                when datepart(dw, ClaimRepairs.ConfirmedOnSiteDate) = 1 then 1 else 0 end
            WHEN MRD.DeploymentDate IS NOT NULL AND ClaimRepairs.ConfirmedOnSiteDate IS NULL
        THEN datediff(dd, MRD.DeploymentDate,GETDATE()) - (datediff(wk, MRD.DeploymentDate, GETDATE()) * 2) -
            case 
                when datepart(dw, MRD.DeploymentDate) = 1 then 1 else 0 end +
            case
                when datepart(dw, GETDATE()) = 1 then 1 else 0 end
     END AS DaysBetweenDeploymentDateOnSite,
     CASE 
        WHEN ClaimRepairs.ConfirmedOnSiteDate IS NOT NULL AND ClaimRepairs.WorkStartedDate IS NOT NULL
        THEN datediff(dd, ClaimRepairs.ConfirmedOnSiteDate, ClaimRepairs.WorkStartedDate) - (datediff(wk,ClaimRepairs.ConfirmedOnSiteDate, ClaimRepairs.WorkStartedDate) * 2) -
            case
                 when datepart(dw, ClaimRepairs.ConfirmedOnSiteDate) = 1 then 1 else 0 end +
            case
                 when datepart(dw, ClaimRepairs.WorkStartedDate) = 1 then 1 else 0 end
        WHEN ClaimRepairs.ConfirmedOnSiteDate IS NOT NULL AND ClaimRepairs.WorkStartedDate IS NULL
        THEN datediff(dd, ClaimRepairs.ConfirmedOnSiteDate, GETDATE()) - (datediff(wk,ClaimRepairs.ConfirmedOnSiteDate, GETDATE()) * 2) -
            case
                 when datepart(dw, ClaimRepairs.ConfirmedOnSiteDate) = 1 then 1 else 0 end +
            case
                 when datepart(dw, GETDATE()) = 1 then 1 else 0 end
     END AS DaysBetweenOnSiteAndDateStarted,
     ROW_NUMBER() OVER(ORDER BY Alerts.AlertID DESC) AS RecordID
    FROM ALERTS
    INNER JOIN ClaimRepairs ON Alerts.ClaimRepairID = ClaimRepairs.ClaimRepairID
    LEFT OUTER JOIN Claims ON ClaimRepairs.ClaimID = Claims.ClaimID
    LEFT OUTER JOIN IDSSearches ON Claims.ClaimID = IDSSearches.ClaimID
    LEFT OUTER JOIN Deployments ON IDSSearches.DeploymentID = Deployments.DeploymentID
    LEFT OUTER JOIN ManufacturerRepairerDeployments MRD ON IDSSearches.ManufacturerRepairerDeploymentID = MRD.ManufacturerRepairerDeploymentID
    LEFT OUTER JOIN ManufacturerApprovedRepairers MAR ON MRD.ApprovedRepairerID = MAR.ApprovedRepairerId
    LEFT OUTER JOIN SiteProfiles ON Deployments.SiteProfileID = SiteProfiles.SiteProfileID
    LEFT OUTER JOIN Actions ON Alerts.AlertID = Actions.AlertID  
    WHERE RecordID >= ''' +  convert(nvarchar(50), @From) + ''' AND RecordID < ''' +  convert(nvarchar(50), @To) + ''' AND Alerts.AlertType != 1 AND Alerts.AlertType != 2 AND Alerts.AlertType != 3  AND (Alerts.Status = 0 AND Alerts.FollowUpDate <= GETDATE() OR Alerts.Status = 0 AND Alerts.FollowUpDate IS NULL)'

IF (@WorkProviderCode != '')
BEGIN
    SET @SQL_STATEMENT = @SQL_STATEMENT + ' AND (ClaimRepairs.WorkProviderCode LIKE ''' + @WorkProviderCode + ''' OR Claims.WorkProviderCode LIKE ''' + @WorkProviderCode + ''')'
END

以前我有以下Row_Number计数，但是，我需要where子句动态地匹配过程底部的where子句。

FROM (SELECT Alerts.*,
         (ROW_NUMBER() OVER(ORDER BY Alerts.AlertID DESC)) AS RecordID
    FROM Alerts 
    WHERE Alerts.AlertType != 1 AND Alerts.AlertType != 2 AND Alerts.AlertType != 3  AND (Alerts.Status = 0 AND Alerts.FollowUpDate <= GETDATE() OR Alerts.Status = 0 AND Alerts.FollowUpDate IS NULL)
   ) AS A

Answer 1

正如在注释中已经告诉您的那样，不能在WHERE子句中使用别名列名。解决这个问题的常用方法是一个通用表表达式(cte)：

;WITH cte AS
(
    SELECT ...
          ,ROW_NUMBER() OVER(...) AS RecordID --aliased column
    Your query here without the WHERE-clause
)
SELECT * 
FROM cte
WHERE RecordID >= ...

顺便说一下:您的代码似乎是动态构建语句的一部分，并且您显示它的方式是无效的。从这部分我可以看出这种方法没有真正的理由……

在极少数情况下(具有大量行)，出于性能原因，这可能是必要的，但我在这里对此表示怀疑……