WITH 语法, 可在 MySQL 和 Oracle 中使用

1 WITH AS 短语

也叫做子查询部分 (subquery factoring), 是用来定义一个 SQL 片断

with t as (
select * from consumer
), t1 as (
select * from consumer
)
select * from t, t1

该语句的作用是在, 大量的报表查询时, 使用 WITH AS 可以提取出大量的子查询, 更加简洁

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 语句的补充: 公共表表达式（CTEs)

公共表表达式（CTEs）是一个命名的临时结果集。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.