如何在SQL Server中通过别名列除法计算日均增重（ADG）？

在SQL里，SELECT子句中定义的别名无法直接在同层SELECT中引用——这是因为SQL执行顺序是先处理FROM/GROUP BY，再生成SELECT里的别名，所以直接用别名计算会报错。要得到ADG，得先把Weightdiff和Daydiff的结果预先计算好，再基于这些结果做除法，常用的方法是用公共表表达式（CTE）或子查询，同时还要处理除数为0的情况避免报错。

修改后的SQL代码（CTE写法） #

WITH DailyWeight AS (
    SELECT 
        CONVERT(varchar(20), Date, 107) AS Date,
        SUM(Weight) AS Weight,
        SUM(Weight) - LAG(SUM(Weight)) OVER (ORDER BY Date) AS Weightdiff,
        DATEDIFF(DAY, LAG(Date) OVER (ORDER BY Date), Date) AS Daydiff
    FROM Store
    GROUP BY Date
)
SELECT 
    Date,
    Weight,
    Weightdiff,
    Daydiff,
    -- 处理Daydiff为0的情况，返回0
    CASE 
        WHEN Daydiff = 0 THEN 0
        ELSE CAST(Weightdiff AS DECIMAL(10,2)) / Daydiff 
    END AS ADG
FROM DailyWeight
ORDER BY Date DESC;

修改后的SQL代码（子查询写法） #

如果更习惯子查询，也可以这么写：

SELECT 
    Date,
    Weight,
    Weightdiff,
    Daydiff,
    CASE 
        WHEN Daydiff = 0 THEN 0
        ELSE CAST(Weightdiff AS DECIMAL(10,2)) / Daydiff 
    END AS ADG
FROM (
    SELECT 
        CONVERT(varchar(20), Date, 107) AS Date,
        SUM(Weight) AS Weight,
        SUM(Weight) - LAG(SUM(Weight)) OVER (ORDER BY Date) AS Weightdiff,
        DATEDIFF(DAY, LAG(Date) OVER (ORDER BY Date), Date) AS Daydiff
    FROM Store
    GROUP BY Date
) AS DailyWeight
ORDER BY Date DESC;

关键说明 #

1. CTE/子查询的作用：先把每日的Weight、Weightdiff、Daydiff计算成中间数据集，再基于这个数据集计算ADG，就能正常引用两个别名对应的字段。
2. 数据类型转换：把Weightdiff转为DECIMAL(10,2)是为了避免整数除法截断（比如3/5会得到0.6而非0），保证结果是小数。
3. 除数为0处理：用CASE语句判断Daydiff是否为0，符合你期望的返回0的需求。

最终输出结果 #

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