with as的常见用法

with as的常见用法

什么是 with as？

是SQL中的一种[常见语法]，是[公共表达式(CTE)]，也是一个[临时的查询结果集]。我们可以将它作为一个"查询结果"，在下一次的查询当中，直接使用它，这样不仅能使代码变得简洁，易懂，而且还能提高查询效率。

为什么要使用它？

两个主要原因：

1. 降低对表的扫描次数，提高查询效率；
2. 减少代码重写，提高代码可读性；

怎么用？

实例：

1、创建1张用户表

CREATE TABLE [user]
(
    [id] INTEGER IDENTITY, -- 自增ID
    [username] VARCHAR(20), -- 用户名
    [sex] CHAR(2), -- 性别
    [city] VARCHAR(20) -- 城市
)

2、写入测试数据

insert into [user]([username], [sex], [city])
values ('阿波', '男', '宜宾')
insert into [user]([username], [sex], [city])
values ('阿浪', '女', '拉萨')
insert into [user]([username], [sex], [city])
values ('阿贤', '男', '成都')

3、需求：找出和用户阿波性别相同，并且城市不同的其他用户

01、with as解法：

-- 使用 with as 提取用户阿波的性别和城市信息
WITH UserDAO AS (
    SELECT [sex], [city]
    FROM [user]
    WHERE [username] = '阿波'
)

-- 连表查询
SELECT u.id
       , u.username
       , u.sex
	   , u.city
FROM [user] u
JOIN UserDAO a ON u.[sex] = a.[sex] AND u.[city] <> a.[city]

执行计划：

02、子查询解法：

SELECT *
FROM [user]
WHERE [sex] = (SELECT [sex] FROM [user] WHERE [username] = '阿波') -- 子查询1：获取用户阿波的性别
AND [city] <> (SELECT [city] FROM [user] WHERE [username] = '阿波') -- 子查询2：获取用户阿波的城市

执行计划：

通过阅读执行计划，我们可以很明显的看到，【子查询】，Table Scan(全表扫描)一共执行了3次，而【with as】只执行了2次，这还不包括 Stream Aggregate、Assert、Nested Loops，所以综合对比下来，可以得出：with as的执行效率是要高于 “多次使用子查询” 的。当然，我也不是想表达 with as 的执行效率就一定高于子查询，而是想借此来说明一个业务场景：当遇到，需要多次访问同一个子查询结果集的时候，可以考虑使用 with as；

补充

1、需要同时定义多个子查询时，只可用1个with关键字，而不可用多个；

with 
	t1 as (select [sex],[username],[city] from [user]),
	t2 as (select [sex],[username],[city] from [user])
	
	select distinct t1.* from t1 inner join t2 on t1.[sex] = t2.[sex]
	where t1.[username] = '阿波'

2、使用 with as 定义的子查询，只存在于 “下一次的查询当中”，不可多次使用；

结语：没有秋风寒霜，枫叶永远是普通的绿色。