MySQL 命令之 WITH AS 公共表达式

1、WITH AS

在 MySQL 中，WITH AS 用于创建一个临时的命名查询（也称为Common Table Expressions,CTE），这些子查询可以在后续的查询中引用，从而简化查询语句的编写。语法如下

WITH cte_name_1 [(column_name1, column_name2, ...)] AS (
    -- CTE查询定义
    SELECT column1, column2, ... FROM table_name WHERE condition
)[, cte_name_2 (column_name1, column_name2, ...) AS (
    -- CTE查询定义
    SELECT column1, column2, ... FROM table_name_1 WHERE condition
)]

- - 主查询引用CTE
SELECT column_name1, column_name2, ... FROM cte_name WHERE condition;

其中

• cte_name 是CTE的名称，
• (column_name1, column_name2, ...) 是可选的，用于为CTE定义列名，提供可选的列名可以提高可读性。
• SELECT column1, column2, ... FROM table_name WHERE condition 是CTE查询的定义部分。

2、WITH 特性

• WITH 其实就是一个子查询抽取出来，换了一个别名
• 和视图的区别：WITH AS 等同于一次性视图，只会持续到下一个查询。在之后就不能再被引用
• 主要用于简化复杂的 数据集 和 递归

注：WITH 语法，MySQL 8.0 版本之前不能用

3、旧版本替代方案 temporary table

5.6 / 5.7 版本, WITH 的替代品

CREATE TEMPORARY TABLE detail 
  SELECT id, end_time, status
  FROM t_wl_record_repairs_detail
  WHERE end_time IS NULL;

SELECT COUNT(1) as sum, 'today' as name FROM detail...
UNION
SELECT COUNT(1) as sum, 'd1' as name....
UNION...

• 作用：临时表用来保存一些 ‘临时数据’

• 特点:

  1. 临时表 仅在 ‘当前连接’ 可见，当关闭连接时，MySQL 会 ‘自动删除表数据及表结构’
  2. 临时表 和 普通表 用法一样，用关键字 temporary 予以区别

-- 可以手动 insert
insert into temporary_test(tid, tname) values(1, 'a');
insert into temporary_test(tid, tname) values(2, 'b');
-- 也可手动 delete 和 drop
drop temporary table if exists temporary_test;

4、准备语句（Prepared Statement）

准备语句是一种数据库查询的执行机制, 最早由IBM的数据库管理系统DB2引入. 随后, 准备语句得到了广泛的支持，被包括MySQL、Oracle、Microsoft SQL Server等在内的众多数据库管理系统所采用.

-- Prepared Statement
SET @tableName = 't_user';
# select * from @tableName; -- error

-- 构建动态SQL语句
SET @sql = CONCAT('select * from ', @tableName);

-- 执行动态SQL语句
PREPARE stmt FROM @sql;
EXECUTE stmt;

-- 释放准备的语句
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT('DROP TABLE ', @tableName);
....

5、WITH 语句的补充: 公共表表达式（CTE)

公共表表达式（CTE）是一个命名的临时结果集。CTE不作为对象存储，仅在查询执行期间持续

CTE是 WITH 语句中定义的命名查询块. 而 WITH 语句用于定义和使用CTE的语法结构。

Common table expressions (CTEs) in SQL Server provide us with a tool that allows us to design and organize queries in ways that may allow faster development, troubleshooting, and improve performance. In the first part of this series, we’ll look at querying against these with a practice data set. From examples of wrapped query checks to organization of data to multiple structured queries, we’ll see how many options we have with this tool and where it may be useful when we query data.

WITH cte_name AS (
    query
);

• Oracle 中定义了 WITH 语句, 但在接下来的语句中未使用, 将会报错.

[42000][1762] ORA-01762: vopdrv: view query block not in FROM

ps: 在 WHERE 语句中 AND 优先级高于 OR.

Reference

• https://gitcode.csdn.net/65e83e1b1a836825ed78b396.html
• https://www.cnblogs.com/f-ck-need-u/p/8875863.html