MySQL中用with as 解决临时表的问题
1. MySQL不能创建临时表
写存储过程中,经常需要做过渡的临时表,主要是字段多,但是MySQL不支持,或者调整参数。
create table ts_tmp as
select * from test_grade f where f.math>60 and f.english>70;
报错:
1786 - Statement violates GTID consistency: CREATE TABLE … SELECT.
时间: 0.002s
2. With As
WITH 子句,称为 Common Table Expressions(CTE),是一种在 SQL 查询中创建临时结果集的方法,存在于单个语句的范围内,以便在查询中多次引用。它可以使 SQL 查询更加模块化和可读。
实现临时表的效果和目的
语法:
WITH temporary_table_name (column1, column2, ...) AS (
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
SELECT * FROM temporary_table_name;
3. 用例
(1)简单查询
filter_class 实际上可以理解为是一个临时表名,后面对这个临时表进行查询。
mysql> with filter_class as
-> (select * from test_grade t where t.classno='Class1')
-> select * from filter_class f where f.math>60 and f.english>70;
+----------+------------+---------+------+---------+---------+-----------+
| stu_id | test_date | classno | math | english | physics | chemistry |
+----------+------------+---------+------+---------+---------+-----------+
| jxBeehzP | 2024-07-28 | Class1 | 65 | 84 | 27 | 81 |
| ZRb81xrr | 2024-06-20 | Class1 | 92 | 93 | 91 | 74 |
| 7Mot1vl3 | 2024-05-23 | Class1 | 68 | 79 | 91 | 16 |
+----------+------------+---------+------+---------+---------+-----------+
3 rows in set (0.01 sec)
mysql>
(2)组合查询
有两个CTE,filter_class1 和 filter_class2 ,视为两个表,使用标准的where语句即可。
mysql> with filter_class1 as
-> (select * from test_grade t where t.classno='Class1'),
-> filter_class2 as
-> (select * from test_grade t where t.physics>90 and t.math>80)
-> select a.* from filter_class1 a,filter_class2 b where a.stu_id=b.stu_id;
+----------+------------+---------+------+---------+---------+-----------+
| stu_id | test_date | classno | math | english | physics | chemistry |
+----------+------------+---------+------+---------+---------+-----------+
| nRZdY132 | 2024-06-06 | Class1 | 94 | 56 | 95 | 10 |
| ZRb81xrr | 2024-06-20 | Class1 | 92 | 93 | 91 | 74 |
| bzaVZYKH | 2024-05-18 | Class1 | 99 | 18 | 94 | 17 |
+----------+------------+---------+------+---------+---------+-----------+
3 rows in set (0.01 sec)
4.小结
- 使用with子句可以让子查询重用相同的with查询块,通过select调用(with子句只能被select查询块引用),一般在with查询用到多次情况下。在引用的select语句之前定义,同级只能定义with关键字只能使用一次,多个用逗号分割。
- 最后一个with 子句与下面的查询之间不能有逗号,只通过右括号分割,with 子句的查询必须用括号括起来。
- 前面with子句定义的查询在后面的with子句中可以使用。但同一with子句内部不能嵌套with子句。
- 当一个查询块名字和一个表名或其他的对象相同时,解析器从内向外搜索,优先使用子查询块名字。