MySQL：with...as的根本使用
一、概述with as 语句是SQL中的一种常用语法，它可以为一个查询结果或子查询结果创建一个临时表，并且可以在后续的查询中使用这个临时表，在查询竣事后该临时表就被扫除了。这种语法的使用可以使得复杂的查询变得简单，同时也可以提高查询效率。
版本支持：
[*]mysql版本：8以及8以上的
[*]sql server：sql server 2005以后的版本
[*]oracle：Oracle 9i的第二版本数据库
二、语法
WITH cte_name1[(column_name_list)] AS (inner_query_definition_1)
[,cte_name2[(column_name_list)] AS (inner_query_definition_2)]
[,...]
outer_query_definition
三、案例1：重命名
CREATE TABLE user(
id INT NOT NULL PRIMARY KEY,
sex CHAR(3),NAME CHAR(20)
);
INSERT INTO user VALUES
(1,'nan','陈一'),
(2,'nv','珠二'),
(3,'nv','张三'),
(4,'nan','李四'),
(5,'nv','王五'),
(6,'nan','赵六');
# 定义CTE，顺便为每列重新命名，且使用ORDER BY子句
WITH nv_user(myid,mysex,myname) AS (
SELECT * FROM user WHERE sex='nv' ORDER BY id DESC
)
# 使用CTE
SELECT * FROM nv_user;
+------+-------+-------------+
| myid | mysex | myname |
+------+-------+-------------+
| 5 | nv | 王五 |
| 3 | nv | 张三 |
| 2 | nv | 珠二 |
+------+-------+-------------+
三、案例1：简化查询写法
-- 分类表
CREATE TABLE category ( cid VARCHAR ( 32 ) PRIMARY KEY, cname VARCHAR ( 50 ) );
-- 商品表
CREATE TABLE products (
pid VARCHAR ( 32 ) PRIMARY KEY,
pname VARCHAR ( 50 ),
price INT,
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,category_id) VALUES('p001','小米电视机',5000,'c001');
INSERT INTO products(pid, pname,price,category_id) VALUES('p002','格力空调',3000,'c001');
INSERT INTO products(pid, pname,price,category_id) VALUES('p003','美的冰箱',4500,'c001');
INSERT INTO products (pid, pname,price,category_id) VALUES('p004','篮球鞋',800,'c002');
INSERT INTO products (pid, pname,price,category_id) VALUES('p005','运动裤',200,'c002');
INSERT INTO products (pid, pname,price,category_id) VALUES('p006','T恤',300,'c002');
INSERT INTO products (pid, pname,price,category_id) VALUES('p007','冲锋衣',2000,'c002');
INSERT INTO products (pid, pname,price,category_id) VALUES('p008','神仙水',800,'c003');
INSERT INTO products (pid, pname,price,category_id) VALUES('p009','大宝',200,'c003');
如上图，如果我想查询“家电”中“格力空调”与“美的冰箱”的信息，不用with as写法如下：
select * from category c
left join products p on c.cid = p.category_id
where c.cname = '家电' and p.pname in ('格力空调','美的冰箱');
使用with as写法如下：
with c as (select * from category where cname = '家电'),
p as (select * from products where pname in ('格力空调','美的冰箱'))
select * from c,p where c.cid = p.category_id;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]