CTE和子查询

CTE 是一个临时的结果集，相比于subquery子查询来说，CTE可读性更好，能实现递归查询，而且不浪费储存空间，不需要维护。缺点是只能在当前的query使用。

接下来看一个简单例子来了解CTE（目的是得到2019年邮件的发送数量和退订数量）:

1 得到emaildelivered这个表格中的数据，存放在 delivered 这个CTE中

2 得到emailunsubscribe 这个表格中的数据，存放在unsubs这个CTE中

3 引用CTE,将CTE作为临时结果集（可以理解为类似表）输出需要的数据

WITH delivered as

(SELECT emailaddress, emailid, senddate

FROM emaildelivered

WHERE senddate between '2019-01' and '2019-12' ),

unsubs as

(SELECT emailaddress, emailid, senddate

FROM emailunsubscribe

WHERE senddate between '2019-01' and '2019-12' )

SELECT delivered.emailid,

count(delivered.emailaddress) as countofdelivered,

count(unsubs.emailaddress) as countofunsubd

FROM delivered

Inner JOIN unsubs on delivered.emailid = unsubs.emailid

GROUP BY delivered.emailid,

ORDER BY delivered.emailid

接下来，子查询一般是包含了select嵌套语句，在理解子查询的时候，从内向外去理解。

看一个子查询的例子（目的是得到订单金额大于30的城市名字，以及相应的订单信息）：

1 从orderhistory表格中得到所有ordervalue 大于30的订单临时结果集，并命名为Ord

2 连接account表,并限制国家是China

3 返回Ord表格中所有的列，以及account 表格中的city列

select ord.*, acc.city from

( select accountid, orderid, ordervalue from orderhistory where ordervalue > '30’) ord

join account acc on ord.accountid = acc.accountid where acc.country = 'China'

如果是简单子查询还是可读的，但如果嵌套很多子句，费神又丑陋，不如CTE清晰明了。

为什么强烈建议你不要做联表查询？ - 知乎

大数据基础：ORM框架入门简介 - 知乎

CTE和子查询示例 - 知乎

SQL 复杂子查询 - 知乎