lotus

贵有恒何必三更眠五更起 最无益只怕一日曝十日寒

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

 

在刷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;

运行结果为:

 

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

posted on 2022-08-12 17:37  白露~  阅读(1686)  评论(0编辑  收藏  举报