sql - 如何在 SQL Server 的 where 子句中使用新的输出

Question

我正在执行这个查询，我想添加where Age > 120，但由于Age不是列，我不能这样做。

你能帮我做吗

select 
    a.NUM_CHASSIS, a.MARQUE, a.MODELE, a.FINITION,
    b.CMD_Vente_Incadea, b.Fact_Av_Incadea,
    a.DATE_FIN_OF, DATEDIFF(DAY, a.DATE_FIN_OF, GETDATE()) as Age   
from
    [DashboardPowerBi].[dbo].[BI_SVC_PROD_FIN_OF] as a
join 
    BI_SVC_INCADEA_STOCK_VN as b on a.NUM_CHASSIS = b.VIN
where 
    b.CMD_Vente_Incadea = 0 
    and b.Fact_Av_Incadea = 1

标签： sqlsql-server

Answer 1

您可以将表达式添加到您的WHERE：

SELECT PF.NUM_CHASSIS,
       PF.MARQUE,
       PF.MODELE,
       PF.FINITION,
       ISV.CMD_Vente_Incadea,
       ISV.Fact_Av_Incadea,
       PF.DATE_FIN_OF,
       DATEDIFF(DAY, PF.DATE_FIN_OF, GETDATE()) AS Age
FROM [DashboardPowerBi].[dbo].[BI_SVC_PROD_FIN_OF] AS PF
     JOIN BI_SVC_INCADEA_STOCK_VN AS ISV ON PF.NUM_CHASSIS = ISV.VIN
WHERE ISV.CMD_Vente_Incadea = 0
  AND ISV.Fact_Av_Incadea = 1
  AND DATEDIFF(DAY, PF.DATE_FIN_OF, GETDATE()) > 120;

或使用 CTE/子查询：

WITH CTE AS(
    SELECT PF.NUM_CHASSIS,
           PF.MARQUE,
           PF.MODELE,
           PF.FINITION,
           ISV.CMD_Vente_Incadea,
           ISV.Fact_Av_Incadea,
           PF.DATE_FIN_OF,
           DATEDIFF(DAY, PF.DATE_FIN_OF, GETDATE()) AS Age
    FROM [DashboardPowerBi].[dbo].[BI_SVC_PROD_FIN_OF] AS PF
         JOIN BI_SVC_INCADEA_STOCK_VN AS ISV ON PF.NUM_CHASSIS = ISV.VIN
    WHERE ISV.CMD_Vente_Incadea = 0
      AND ISV.Fact_Av_Incadea = 1)
SELECT *
FROM CTE
WHERE CTE.Age > 120;

另外，请注意我将您的别名更改为更相关的名称。使用代表对象名称的别名非常重要，因为它使您的代码更具可读性。Aaron Bertrand 就这个主题写了一篇很棒的文章：要踢的坏习惯：使用表别名，如 (a, b, c) 或 (t1, t2, t3)。