如何基于别名列计算获取NotAged与Aging字段数据？

尝试基于别名列Aged进行计算，现有SQL查询如下：

SELECT 
    Aged, NotAged, Aging
FROM
    (SELECT 
         DATEDIFF(DAY, CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                      CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) AS Aged,
         DATEDIFF(DAY, CASE WHEN Aged <= 25 THEN GETDATE() AS NotAged ELSE GETDATE() END) AS Aging
     FROM 
         DM.Claim 
     INNER JOIN
         DM.LDesc ON LDescKey = LDescKey) data

期望基于Aged别名列计算得到NotAged和Aging字段，输出效果为：Aged {1 2 35} NotAged {1 2} Aging {35}，请问该如何实现？

SQL中无法在同一层级的SELECT语句里直接引用别名（别名的解析逻辑在SELECT字段计算之后），可以通过以下方式实现需求：

方法1：用子查询/CTE预先计算Aged（推荐，维护性强） #

子查询写法 #

SELECT 
    Aged,
    CASE WHEN Aged <= 25 THEN Aged END AS NotAged,
    CASE WHEN Aged > 25 THEN Aged END AS Aging
FROM
    (SELECT 
         DATEDIFF(DAY, 
                  CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                  CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) AS Aged
     FROM 
         DM.Claim 
     INNER JOIN
         DM.LDesc ON Claim.LDescKey = LDesc.LDescKey) data

CTE写法（更易读） #

WITH ClaimAging AS (
    SELECT 
         DATEDIFF(DAY, 
                  CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                  CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) AS Aged
     FROM 
         DM.Claim 
     INNER JOIN
         DM.LDesc ON Claim.LDescKey = LDesc.LDescKey
)
SELECT 
    Aged,
    CASE WHEN Aged <=25 THEN Aged END AS NotAged,
    CASE WHEN Aged >25 THEN Aged END AS Aging
FROM ClaimAging

方法2：重复Aged的计算逻辑（不推荐，维护成本高） #

如果不想用子查询/CTE，可以直接重复Aged的计算逻辑来判断：

SELECT 
    DATEDIFF(DAY, 
             CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
             CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) AS Aged,
    CASE 
        WHEN DATEDIFF(DAY, 
                      CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                      CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) <=25 
        THEN DATEDIFF(DAY, 
                      CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                      CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) 
    END AS NotAged,
    CASE 
        WHEN DATEDIFF(DAY, 
                      CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                      CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) >25 
        THEN DATEDIFF(DAY, 
                      CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                      CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) 
    END AS Aging
FROM 
    DM.Claim 
INNER JOIN
    DM.LDesc ON Claim.LDescKey = LDesc.LDescKey

说明 #

• 上述代码中，NotAged会返回所有Aged≤25的值，其余为NULL；Aging返回Aged>25的值，其余为NULL，符合你期望的{1,2}和{35}的输出逻辑。
• 原SQL中的JOIN条件LDescKey = LDescKey存在歧义，必须明确指定表名（比如Claim.LDescKey = LDesc.LDescKey），避免数据库报错。

内容的提问来源于stack exchange，提问作者OEGM2002