公用表表达式（CTE）详解：针对 MySQL 和 SQL Server 数据库 ...

公用表表达式（CTE，Common Table Expressions）是一种在 SQL 中界说暂时结果集的方法，该结果集在单个查询的执行过程中可以被引用。CTE 进步了查询的可读性和结构化，特别实用于复杂的子查询和递归查询。本文将详细介绍 CTE 的概念和用法，并分别针对 MySQL 和 SQL Server 数据库进行说明。
什么是公用表表达式（CTE）

CTE 是一个命名的暂时结果集，它在查询的执行范围内有用。CTE 的界说在 WITH 子句中，紧随其后的是查询主体。CTE 分为两种类型：

• 非递归 CTE：用于界说一次性盘算结果的暂时表。
  
• 递归 CTE：用于处理递归查询，如处理条理结构数据（例如组织结构图）。
  

CTE 的语法

根本语法

1. WITH cte_name AS (
2.     SELECT column1, column2, ...
3.     FROM table_name
4.     WHERE condition
5. )
6. SELECT column1, column2, ...
7. FROM cte_name
8. WHERE condition;

复制代码

MySQL 中的 CTE

MySQL 从 8.0 版本开始支持 CTE。下面是一些常见的使用场景。
示例 1：非递归 CTE

假设我们有一个名为 bs_bill_day 的表，并且盼望使用 CTE 从该表中选择数据，然后限制结果为前 10 行。

1. WITH bill_cte AS (
2.     SELECT * FROM bs_bill_day
3. )
4. SELECT *
5. FROM bill_cte
6. LIMIT 10;

复制代码

示例 2：递归 CTE

假设我们有一个表现员工和司理关系的表 employees，其中包含 employee_id 和 manager_id 列，我们盼望找到某个司理及其所有下属。

1. WITH RECURSIVE employee_cte AS (
2.     -- 初始查询，选择顶层经理
3.     SELECT employee_id, manager_id, 1 AS level
4.     FROM employees
5.     WHERE manager_id IS NULL
7.     UNION ALL
9.     -- 递归部分，选择下一级员工
10.     SELECT e.employee_id, e.manager_id, ec.level + 1
11.     FROM employees e
12.     INNER JOIN employee_cte ec ON e.manager_id = ec.employee_id
13. )
14. SELECT *
15. FROM employee_cte;

复制代码

SQL Server 中的 CTE

SQL Server 从 2005 版本开始支持 CTE。它的语法和 MySQL 类似。
示例 1：非递归 CTE

假设我们有一个名为 bs_bill_day 的表，并且盼望使用 CTE 从该表中选择数据，然后限制结果为前 10 行。

1. WITH bill_cte AS (
2.     SELECT * FROM bs_bill_day
3. )
4. SELECT TOP 10 *
5. FROM bill_cte;

复制代码

示例 2：递归 CTE

假设我们有一个表现员工和司理关系的表 employees，其中包含 employee_id 和 manager_id 列，我们盼望找到某个司理及其所有下属。

1. WITH employee_cte AS (
2.     -- 初始查询，选择顶层经理
3.     SELECT employee_id, manager_id, 1 AS level
4.     FROM employees
5.     WHERE manager_id IS NULL
7.     UNION ALL
9.     -- 递归部分，选择下一级员工
10.     SELECT e.employee_id, e.manager_id, ec.level + 1
11.     FROM employees e
12.     INNER JOIN employee_cte ec ON e.manager_id = ec.employee_id
13. )
14. SELECT *
15. FROM employee_cte;

复制代码

使用 CTE 进行分页

在大数据集的分页查询中，CTE 也非常有用。以下是在 MySQL 和 SQL Server 中使用 CTE 进行分页的示例。
MySQL 分页

1. WITH bill_cte AS (
2.     SELECT * FROM bs_bill_day
3. )
4. SELECT *
5. FROM bill_cte
6. LIMIT 10 OFFSET 0; -- 获取第一页的10条记录

复制代码

SQL Server 分页

1. WITH bill_cte AS (
2.     SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
3.     FROM bs_bill_day
4. )
5. SELECT *
6. FROM bill_cte
7. WHERE row_num BETWEEN 1 AND 10; -- 获取第一页的10条记录

复制代码

mysql实现返回最近三十天的列

2. WITH RECURSIVE recent_day_cte AS (
3.     -- 初始查询，获取当前日期的订单记录
4.     SELECT CURDATE() AS dayKey
6.     UNION ALL
8.     -- 递归查询，获取前一天的订单记录
9.     SELECT DATE_SUB(dayKey, INTERVAL 1 DAY)
10.     FROM recent_day_cte
11.     WHERE dayKey >= CURDATE() - INTERVAL 29 DAY
12. )
13. SELECT *
14. FROM recent_day_cte;

复制代码

sql server 实现返回最近三十天的列

1. WITH recent_dates_cte AS (
2.     -- 初始查询，获取当前日期的订单记录
3.     SELECT CAST(GETDATE() AS DATE) AS dayKey
5.     UNION ALL
7.     -- 递归查询，获取前一天的日期
8.     SELECT DATEADD(DAY, -1, dayKey)
9.     FROM recent_dates_cte
10.     WHERE dayKey >= DATEADD(DAY, -29, CAST(GETDATE() AS DATE))
11. )
12. SELECT *
13. FROM recent_dates_cte;

复制代码

sql server 获取最近七个月的月份信息

1. WITH recent_dates_cte AS (
2.     -- 初始查询，获取当前日期的订单记录
3.     SELECT CAST(GETDATE() AS DATE) AS dayKey
5.     UNION ALL
7.     -- 递归查询，获取前一天的日期
8.     SELECT DATEADD(MONTH, -1, dayKey)
9.     FROM recent_dates_cte
10.     WHERE dayKey >= DATEADD(MONTH, -5, CAST(GETDATE() AS DATE))
11. ),
12. month_key_cte as (
13. SELECT CONVERT(varchar(7),dayKey,120)  as monKey
14. FROM recent_dates_cte
15. )
17. select * from month_key_cte

复制代码

sql server 获取最近七周的周信息

1. WITH recent_dates_cte AS (
2.     -- 初始查询，获取当前日期的订单记录
3.     SELECT CAST(GETDATE() AS DATE) AS dayKey
5.     UNION ALL
7.     -- 递归查询，获取前一天的日期
8.     SELECT DATEADD(WEEK, -1, dayKey)
9.     FROM recent_dates_cte
10.     WHERE dayKey >= DATEADD(WEEK, -5, CAST(GETDATE() AS DATE))
11. ),
12. week_key_cte as (
13.     SELECT
14.     CONCAT(DATEPART(YEAR, dayKey), '-', FORMAT(DATEPART(WEEK, dayKey), '00')) AS weekKey,
15.     CASE
16.         WHEN DATEDIFF(WEEK, dayKey, GETDATE()) = 0 THEN '本周'
17.         WHEN DATEDIFF(WEEK, dayKey, GETDATE()) = 1 THEN '前1周'
18.         WHEN DATEDIFF(WEEK, dayKey, GETDATE()) = 2 THEN '前2周'
19.         WHEN DATEDIFF(WEEK, dayKey, GETDATE()) = 3 THEN '前3周'
20.         WHEN DATEDIFF(WEEK, dayKey, GETDATE()) = 4 THEN '前4周'
21.         WHEN DATEDIFF(WEEK, dayKey, GETDATE()) = 5 THEN '前5周'
22.         WHEN DATEDIFF(WEEK, dayKey, GETDATE()) = 6 THEN '前6周'
23.     END AS label
24.         FROM recent_dates_cte
25. )
27. select * from week_key_cte

复制代码

有了这样的基础，实现最近6个月，最近5年，最近6周是不是特别简单了，方便统计，比如统计最近六周，半年，最近六年，有了with以后是不是好明白多了。
公用表表达式（CTE）是处理复杂查询的强大工具，提供了更好的代码可读性和结构化。在 MySQL 和 SQL Server 中使用 CTE，能够简化复杂的子查询，并且方便处理递归查询和分页查询。通过本文的介绍，盼望读者能对 CTE 的概念和用法有更清晰的明白，并能在实际工作中应用这些技巧。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。