MySQL：with...as的根本使用

---

一、概述

with as 语句是SQL中的一种常用语法，它可以为一个查询结果或子查询结果创建一个临时表，并且可以在后续的查询中使用这个临时表，在查询竣事后该临时表就被扫除了。这种语法的使用可以使得复杂的查询变得简单，同时也可以提高查询效率。
版本支持：

• mysql版本：8以及8以上的
  
• sql server：sql server 2005以后的版本
  
• oracle：Oracle 9i的第二版本数据库
  

二、语法

1. WITH cte_name1[(column_name_list)] AS (inner_query_definition_1)
2.    [,cte_name2[(column_name_list)] AS (inner_query_definition_2)]
3. [,...]
4. outer_query_definition

复制代码

三、案例1：重命名

1. CREATE TABLE user(
2.     id INT NOT NULL PRIMARY KEY,
3.     sex CHAR(3),NAME CHAR(20)
4. );
6. INSERT INTO user VALUES
7. (1,'nan','陈一'),
8. (2,'nv','珠二'),
9. (3,'nv','张三'),
10. (4,'nan','李四'),
11. (5,'nv','王五'),
12. (6,'nan','赵六');
13.   
14. # 定义CTE，顺便为每列重新命名，且使用ORDER BY子句
15. WITH nv_user(myid,mysex,myname) AS (
16.     SELECT * FROM user WHERE sex='nv' ORDER BY id DESC
17. )
18. # 使用CTE
19. SELECT * FROM nv_user;
20. +------+-------+-------------+
21. | myid | mysex | myname      |
22. +------+-------+-------------+
23. |    5 | nv    | 王五        |
24. |    3 | nv    | 张三        |
25. |    2 | nv    | 珠二        |
26. +------+-------+-------------+

复制代码

三、案例1：简化查询写法

1. -- 分类表
2. CREATE TABLE category ( cid VARCHAR ( 32 ) PRIMARY KEY, cname VARCHAR ( 50 ) );
4. -- 商品表
5. CREATE TABLE products (
6.         pid VARCHAR ( 32 ) PRIMARY KEY,
7.         pname VARCHAR ( 50 ),
8.         price INT,
9.         category_id VARCHAR ( 32 ),
10.         FOREIGN KEY ( category_id ) REFERENCES category ( cid )
11. );
12. -- 分类数据
13. INSERT INTO category(cid,cname) VALUES('c001','家电');
14. INSERT INTO category(cid,cname) VALUES('c002','鞋服');
15. INSERT INTO category(cid,cname) VALUES('c003','化妆品');
16. INSERT INTO category(cid,cname) VALUES('c004','汽车');
18. -- 商品数据
19. INSERT INTO products(pid, pname,price,category_id) VALUES('p001','小米电视机',5000,'c001');
20. INSERT INTO products(pid, pname,price,category_id) VALUES('p002','格力空调',3000,'c001');
21. INSERT INTO products(pid, pname,price,category_id) VALUES('p003','美的冰箱',4500,'c001');
22. INSERT INTO products (pid, pname,price,category_id) VALUES('p004','篮球鞋',800,'c002');
23. INSERT INTO products (pid, pname,price,category_id) VALUES('p005','运动裤',200,'c002');
24. INSERT INTO products (pid, pname,price,category_id) VALUES('p006','T恤',300,'c002');
25. INSERT INTO products (pid, pname,price,category_id) VALUES('p007','冲锋衣',2000,'c002');
26. INSERT INTO products (pid, pname,price,category_id) VALUES('p008','神仙水',800,'c003');
27. INSERT INTO products (pid, pname,price,category_id) VALUES('p009','大宝',200,'c003');

复制代码

如上图，如果我想查询“家电”中“格力空调”与“美的冰箱”的信息，不用with as写法如下：

1. select * from category c
2. left join products p on c.cid = p.category_id
3. where c.cname = '家电' and p.pname in ('格力空调','美的冰箱');

复制代码

使用with as写法如下：

1. with c as (select * from category where cname = '家电'),
2.      p as (select * from products where pname in ('格力空调','美的冰箱'))
3.      
4. select * from c,p where c.cid = p.category_id;

复制代码

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