MySQL-WITH AS用法

MySQL 8.0开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。

 

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用。

注意：

• 相当于一个临时表，但是不同于视图，不会存储起来，要与select配合使用。
• 同一个select前可以创建多个临时表，写一个with就可以，临时表之间用逗号隔开，with语句的最后不要用逗号。
• with子句要用括号括起来。

为了更直观我们先建立两个表，准备一些数据：

-- 分类表
CREATE TABLE category ( cid VARCHAR ( 32 ) PRIMARY KEY, cname VARCHAR ( 50 ) );

-- 商品表
CREATE TABLE products (
    pid VARCHAR ( 32 ) PRIMARY KEY,
    pname VARCHAR ( 50 ),
    price INT,
    flag VARCHAR ( 2 ),-- 是否上架标记为：1表示上架、0表示下架
    category_id VARCHAR ( 32 ),
    FOREIGN KEY ( category_id ) REFERENCES category ( cid ) 
);
-- 分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','汽车');

-- 商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视机',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空调',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰箱',4500,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动裤',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋衣',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大宝',200,'1','c003');

 

然后举例说明With as的用法：

-- 先查询一下小米电视机和格力空调在products中的信息
SELECT * FROM products WHERE pname IN ('小米电视机', '格力空调' );

查询结果为：

那如果我们想把这个表作为一个子查询，去查一下小米电视机和格力空调的平均价格，就可以使用with as先提取子查询，取别名xm_gl，然后在计算：

WITH xm_gl AS ( SELECT * FROM products WHERE pname IN ( '小米电视机', '格力空调' ) ) 
SELECT avg( price ) FROM xm_gl;

结果为：

使用with as还可以创建多个临时表，但是要注意同一个查询语句前写一个with就够了，另外子查询需要逗号隔开，举个例子：

WITH a AS ( SELECT * FROM category WHERE cname = '家电' ),
b AS ( SELECT * FROM products WHERE pname IN ( '小米电视机', '格力空调' ) ) 
SELECT * FROM    a  LEFT JOIN b ON a.cid = b.category_id;

运行结果为：

注意：一定要逗号隔开 

 

参考链接：https://zhuanlan.zhihu.com/p/397195826