MySQL-WITH AS用法

 

在刷leetcode 2072时发现了其他人使用了更简洁的写法，关键字：mysql with as 

2072. 赢得比赛的大学

原写法：

SELECT if((

SELECT count(1)

FROM NewYork

WHERE score >= 90

) = (

SELECT count(1)

FROM California

WHERE score >= 90

), 'No Winner', if((

SELECT count(1)

FROM NewYork

WHERE score >= 90

) > (

SELECT count(1)

FROM California

WHERE score >= 90

), 'New York University', 'California University')) AS winner

 

使用 with as 改良后的写法

# 方法二

WITH temp1 AS (

SELECT count(1) AS c1

FROM NewYork

WHERE score >= 90

),

temp2 AS (

SELECT count(1) AS c2

FROM California

WHERE score >= 90

)

SELECT if(c1 = c2, 'No Winner', if(c1 > c2, 'New York University', 'California University')) AS winner

FROM temp1, temp2

是不是简洁了许多

 

 

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

运行结果为：

 

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