MySQL 8.0 新特性：窗口函数与 CTE

公共表表达式（CTE）

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;

窗口函数

在不分组的情况下对每行进行计算。

-- 排名函数

SELECT 

  name,

  salary,

  RANK() OVER (ORDER BY salary DESC) AS rank,

  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,

  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

FROM employee;



-- 分区排名（按部门）

SELECT 

  name,

  dept_id,

  salary,

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

FROM employee;



-- 移动平均

SELECT 

  date,

  sales,

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

FROM daily_sales;

窗口函数让复杂的统计分析变得优雅高效。