MySQL 8.0 窗口函数与 CTE 使用场景

MySQL 8.0 窗口函数与 CTE 使用场景

窗口函数和 CTE 是 SQL 高级特性，能简化复杂查询。

1. 公共表表达式 (CTE)

可提高查询可读性，支持递归。

-- 普通 CTE

WITH dept_avg AS (

  SELECT dept_id, AVG(salary) as avg_sal

  FROM employee

  GROUP BY dept_id

)

SELECT e.*, d.avg_sal

FROM employee e JOIN dept_avg d ON e.dept_id = d.dept_id;



-- 递归 CTE（查询组织树）

WITH RECURSIVE org_tree AS (

  SELECT id, name, manager_id, 1 as level

  FROM employee

  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, ot.level + 1

  FROM employee e JOIN org_tree ot ON e.manager_id = ot.id

)

SELECT * FROM org_tree;

2. 窗口函数

• ROW_NUMBER()：行号


• RANK() / DENSE_RANK()：排名


• LAG() / LEAD()：前后行值


• 配合 PARTITION BY 分组计算

-- 每个部门员工按薪水排名

SELECT 

  name,

  dept_id,

  salary,

  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept

FROM employee;



-- 移动平均（最近3天）

SELECT 

  date,

  sales,

  AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg

FROM daily_sales;

窗口函数可替代复杂的自连接和子查询，代码更清晰。