CTE（公共表表达式）凭借其分步计算和结果复用的特性，能很好地解决窗口函数在复杂查询中因引用顺序、依赖关系导致的逻辑混乱或语法错误问题。下面，我们一起来详细拆解：

一、窗口函数为什么会有引用依赖问题？

窗口函数（如：rank()、sum() over()等）需基于特定数据集计算结果，而当查询中存在多个窗口函数，且后一个函数的计算依赖前一个函数的结果时，直接在同一查询层级中引用会出现问题：

• 语法限制：SQL中同一SELECT子句内的窗口函数无法直接引用彼此（因执行顺序无先后）。

• 逻辑复杂：多层嵌套的窗口计算会导致查询可读性差，难以维护。

CTE通过将计算拆分为多个临时结果集，让后一步的窗口函数可以直接引用前一步CTE的结果，从而打破依赖限制。

二、用CTE解决窗口函数引用依赖的方法

1、拆分计算步骤
将包含窗口函数的复杂逻辑按依赖关系拆分为多个CTE，每个CTE专注于单一计算目标（如：先算基础窗口结果，再基于此算二次窗口结果）。

2、按依赖顺序定义CTE
后定义的CTE可引用之前所有已定义的CTE，确保依赖关系从左到右、从上到下依次满足，避免循环引用（如：CTE A→CTE B→CTE A的循环是不允许的）。

3、在最终查询中整合结果
主查询直接引用最后一个CTE，或组合多个CTE的结果，完成最终筛选、聚合等操作。

问题：无法在同一SELECT中引用窗口函数别名

-- 错误示例：尝试直接引用前一个窗口函数结果SELECT 
    employee_id,
    department,
    salary,    -- 计算部门工资排名    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,    -- 尝试引用排名计算“是否前3名”    CASE WHEN dept_rank <= 3 THEN 'Top3' ELSE 'Other' END AS rank_flag -- 报错！dept_rank 不存在FROM employees;

数据库报错缘由：执行引擎在计算SELECT列表时，每个表达式独立计算，无法感知同级别其他表达式的结果（包括窗口函数生成的别名）。

解决方案：使用CTE分阶段计算

-- 第1步：在CTE中先计算排名WITH RankData AS (    SELECT 
        employee_id,
        department,
        salary,        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank    FROM employees
)-- 第2步：在主查询中引用CTE的列SELECT 
    employee_id,
    department,
    salary,
    dept_rank,    CASE WHEN dept_rank <= 3 THEN 'Top3' ELSE 'Other' END AS rank_flag -- 可安全引用FROM RankData;

技术拆解：CTE如何解决依赖？

三、进阶示例：多层窗口函数依赖等嵌套依赖

示例1：假设需解决如下问题：有销售表sales（含user_id、sale_date、amount），需计算：

1、每个用户的每日销售额（基础数据）；
2、每个用户的累计销售额（窗口函数1：按日期累加）；
3、每个用户累计销售额的周环比增长率（窗口函数2：依赖累计销售额的结果）。

直接在一个查询中计算会因“周环比依赖累计销售额”而无法实现，用CTE可分步解决：

-- CTE1：计算每个用户的每日销售额（基础数据，无窗口函数）with daily_sales as (    select 
        user_id,
        sale_date,        sum(amount) as daily_amount  -- 按日聚合    from sales    group by user_id, sale_date
),-- CTE2：计算每个用户的累计销售额（窗口函数1）cumulative_sales as (    select 
        *,        sum(daily_amount) over (            partition by user_id 
            order by sale_date 
            rows between unbounded preceding and current row
        ) as cum_amount  -- 按用户累计每日销售额    from daily_sales
),-- CTE3：计算累计销售额的周环比增长率（窗口函数2，依赖CTE2）weekly_growth as (    select 
        *,        -- 引用CTE2的cum_amount，计算与上周同期的增长率
        (cum_amount - lag(cum_amount, 7) over (partition by user_id order by sale_date)) 
        / lag(cum_amount, 7) over (partition by user_id order by sale_date) * 100 
        as week_over_week_growth    from cumulative_sales
)-- 主查询：筛选增长率为正的记录select user_id, sale_date, cum_amount, round(week_over_week_growth, 2) as growth_ratefrom weekly_growthwhere week_over_week_growth > 0order by user_id, sale_date;

解析：daily_sales处理基础聚合，为后续窗口计算提供干净的数据源；cumulative_sales通过窗口函数计算累计值，结果被weekly_growth直接引用；每一步CTE只处理单一逻辑，避免了窗口函数的直接依赖冲突。

示例2：计算每个员工：
1、在部门内的累计工资 (running_total)
2、相比上一名员工的工资差距 (diff_from_prev)
3、与部门最高工资的差距 (diff_from_max)

WITH Calc AS (    SELECT 
        employee_id,
        department,
        salary,        -- 累计工资        SUM(salary) OVER (            PARTITION BY department 
            ORDER BY hire_date 
            ROWS UNBOUNDED PRECEDING
        ) AS running_total,        -- 部门最高工资        MAX(salary) OVER (PARTITION BY department) AS dept_max_salary    FROM employees
)SELECT 
    employee_id,
    department,
    salary,
    running_total,    -- 计算与上一行累计值的差
    salary - LAG(salary, 1) OVER (        PARTITION BY department 
        ORDER BY hire_date
    ) AS diff_from_prev,    -- 计算与部门最高工资的差距
    dept_max_salary - salary AS diff_from_maxFROM Calc;

CTE的价值：将需要多次引用的复杂窗口计算（running_total, dept_max_salary）提前物化，后续像普通列一样随意组合使用，避免重复定义一样窗口逻辑。

四、注意事项

1、性能考量：非性能优化工具
CTE主要解决语法依赖问题，而非性能问题。如果CTE中的窗口函数本身效率低（如：无索引的全表排序），CTE并不会提升速度。
CTE在多数数据库中是“优化屏障”（即数据库可能不会将其与主查询合并优化），因此对于超大数据集，需避免过度拆分CTE，必要时可通过EXPLAIN分析执行计划。
窗口函数的partition by字段提议建立索引，减少排序和分区的计算成本。

2、物化取决于数据库
部分数据库（如：PostgreSQL）可能将CTE优化为子查询内联，而非强制物化；而SQL Server默认会物化CTE结果。可通过EXPLAIN查看执行计划验证。

3、替代方案：子查询
我们知道，虽然子查询也能实现分步计算，但CTE的优势在于：
（1）可读性更高：多个CTE按顺序排列，逻辑层次清晰；
（2）可复用性：同一CTE可在主查询中多次引用，无需重复编写子查询。

-- CTE写法 (清晰)WITH A AS (...), B AS (...) SELECT ... FROM B;-- 等价嵌套子查询 (可读性差)SELECT ... FROM (  SELECT ... FROM (    SELECT ... FROM employees
  ) AS A
) AS B;

4、特殊场景：递归CTE与窗口函数结合
若处理树形结构（如：组织架构），递归CTE生成层级关系后，可嵌套窗口函数计算每层的统计值。例如：

-- 递归CTE生成员工层级，再计算每层的平均薪资排名with recursive emp_hierarchy as (    -- 锚点：顶级员工（无上级）    select id, name, manager_id, salary, 1 as level    from employees    where manager_id is null    union all    -- 递归：关联下属员工    select e.id, e.name, e.manager_id, e.salary, eh.level + 1 as level    from employees e    join emp_hierarchy eh on e.manager_id = eh.id
),-- 基于层级计算每层薪资排名level_ranking as (    select 
        *,        rank() over (partition by level order by salary desc) as level_salary_rank    from emp_hierarchy
)select * from level_ranking where level_salary_rank <= 2;

五、总结：CTE解决依赖的底层逻辑

核心价值：将SQL的声明式语法转化为符合人类思维的分步骤过程，让原本受限于执行顺序的窗口计算结果成为可复用的中间数据块。

CTE通过“分步拆解→顺序引用→结果整合”的模式，将窗口函数的依赖关系转化为清晰的步骤化计算，既解决了语法层面的引用限制，又提升了复杂查询的可读性和可维护性。在实际使用中，我们需根据业务逻辑合理拆分CTE，并关注性能优化，以发挥其最大价值。