MySQL-WITH AS用法

麻甩君子

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用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;运行结果为：



一定要逗号隔开呀，今天用的时候忘记逗号就报错啦

种豆南山下

秀起来~