MySQL二十二：MySQL8.0新特性

MySQL8.0 新特性

一、窗口函数

1. 函数列表

2. 语法结构

   # 方式一
   SELECT 窗口函数 OVER (PARTITION BY 字段名 ORDER BY 字段名 ASC/DESC) AS 新字段名, 字段名1,...
   FROM 剩余查询语句;
   # 方式二
   SELECT 窗口函数 OVER 窗口名 
   FROM 剩余查询语句
   WINDOW 窗口名 AS (PARTITION BY 字段名 ORDER BY 字段名 ASC/DESC);
   

二、公用表表达式(CTE)

1. 普通公用表语法结构

   WITH 公用表名称
   AS (子查询)
   SQL语句;
   

   WITH emp_dept_id
   AS (SELECT DISTINCT department_id FROM employees)
   SELECT *
   FROM departments d JOIN emp_dept_id e
   ON d.department_id = e.department_id;
   

2. 递归公用表语法结构

   WITH RECURSIVE 公用表名称
   AS (子查询)
   SQL语句;
   

   WITH RECURSIVE cte
   AS (
   	SELECT employee_id, last_name, 1 AS n FROM employees WHERE employee_id = 100
       UNION ALL
       SELECT a.employee_id,a.last_name, n+1 FROM employees AS a 
       JOIN cte
       ON a.manager_id = cte.employee_id
   )
   SELECT employee_id, last_name, FROM cte WHERE n >= 3;