SQL语言中的With子句是什么？MySQL-With...as子句如何使用？_mysql 5.7 with

目录

With子句是什么？

MySQL-With...as子句如何使用？

单个WITH AS 子句（创建一张临时视图）

多个WITH AS 子句（创建多张临时视图）

总结

---

With子句是什么？

With子句是在SQL 99中引入的，目前只有部分数据库支持这一子句。如果我们将一个复杂查询分解成一些小视图，然后将它们组合起来，就像将一个程序按其任务分解，简化复杂的查询为一个个子查询。

 如果一个查询中多次使用到了同一个子查询的结果，那么我们就可以使用WITH子句将结果暂存下来。

注意：CREATE VIEW创建视图子句会在数据库中建立视图定义，该视图定义会一直保存，直到执行DROP VIEW命令删除视图。

但是，With子句提供了定义一个临时视图的方法，该定义只对With子句出现的那条查询有效。

MySQL-With...as子句如何使用？

首先，MySQL5.7是不支持 WITH AS 子句的，只有MySQL 8.0 以上版本才支持。

SELECT VERSION() from dual;
-- Navicat中查看MySQL版本

我的版本是8.0.21，列举几个with as子句的例子：

 

单个WITH AS 子句（创建一张临时视图）

例1：关系Teachers（Tname,Eno,Tdept,SEX,Salary,Address），利用With子句查询工资最高的教师姓名。此时，如果具有同样工资最高的教师有多个，他们都会被选择。

with max-Salary(value) AS
    (SELECT max(Salary) FROM Teachers)    //with子句中SELECT查询语句要加括号！！
SElECT Tname
FROM Teachers,max-Salary
WHERE Teachers.Salary=max-Salary.value

---

例2：关系模式学生（学生号，姓名，性别，年龄）

S(Sno,Sname,Sex,Sage)

  

关系模式课程（课程号，课程名）

C(Cno,Cname)

关系模式选课（学生号，课程号，成绩）

SC(Sno,Cno,Grade)

 利用With子句查询C1课程最高分的学生的姓名以及分数：

WITH Max_score (VALUE) AS (SELECT MAX(Grade) FROM SC WHERE Cno='C1')
SELECT Sname,Max_score.value as 最高分 from SC,S,Max_score
where SC.Sno=S.Sno and Cno='C1' AND Grade=Max_score.VALUE;

运行结果如下：

---

多个WITH AS 子句（创建多张临时视图）

例3：银行账户关系模式Account（Account-no，branch-name，balance），其中属性Account-no表示账号，branch-name表示支行名称，balance表示余额。利用With子句查询所有存款总额少于所有支行平均存款总额的支行。

WITH
branch-total(branch-name,value) AS
    (SELECT branch-name,SUM(balance) FROM Account GROUP BY branch-name),
// with as创建多张临时视图时，可以只写一个WITH，子查询之间用逗号隔开
branch-total-avg(value) AS
    (SELECT AVG(value) FROM branch-name)
SELECT branch-name
FROM branch-total,branch-total-avg
WHERE branch-total.value<branch-total-avg.value;

总结

使用With...as子句时需注意：

1. with子句相当于一个临时视图，不会被储存起来，with子句执行结束后就会消失。
2. with子句中SELECT查询语句一定要加括号！！
3. with as创建多张临时视图时，可以只写一个WITH，子查询之间用逗号隔开。