如何在含CASE表达式的子查询中引用外部查询的列别名？

问题原因 #

你遇到的「缺失列」错误，核心是作用域问题：计算goodFactor的子查询是独立的查询上下文，和同层级的crnt、average属于并行计算的列，它没办法直接访问这些还未定义的列。另外原查询的语法结构也存在错误（比如内层SELECT无FROM就直接声明列）。

修正方案 #

我们可以先把「历史每日平均订单数」和「今日订单总数」计算出来，再在同一层级用CASE表达式直接比较这两个值，不需要嵌套多余的子查询。

以下是修正后的SQL：

WITH daily_orders AS (
    -- 统计每日的订单数量（包含今日）
    SELECT 
        name,
        CAST(orderDate AS DATE) AS order_date,
        COUNT(*) AS daily_count
    FROM tacos
    GROUP BY name, CAST(orderDate AS DATE)
),
historical_average AS (
    -- 计算今日之前的每日平均订单数
    SELECT 
        name,
        AVG(daily_count) AS average
    FROM daily_orders
    WHERE order_date < CAST(GETDATE() AS DATE)
    GROUP BY name
)
-- 关联数据生成最终结果
SELECT 
    do.name,
    ha.average AS [Daily Average],
    do.daily_count AS [Current],
    CASE 
        WHEN do.daily_count > ha.average THEN 'pretty good'
        ELSE 'not good'
    END AS GoodFactor
FROM daily_orders do
JOIN historical_average ha ON do.name = ha.name
WHERE do.order_date = CAST(GETDATE() AS DATE);

代码说明 #

1. daily_orders CTE：按日期和商品名称统计每日订单总数，将日期转为DATE类型避免时间部分干扰统计。
2. historical_average CTE：基于每日订单数据，筛选出今日之前的记录，计算每日订单数的平均值。
3. 最后关联两个CTE，筛选今日订单数据，直接用CASE比较今日订单数和历史平均，生成GoodFactor。

验证结果 #

用你提供的示例数据执行后，会得到预期结果：

+-------------+---------------+---------+-------------+
|    name     | Daily Average | Current | GoodFactor  |
+-------------+---------------+---------+-------------+
| Crispy Taco |       5       |      12 | pretty good |
+-------------+---------------+---------+-------------+

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