WITH common_table_expression

先上个官方说明
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

简单理解，就是把一个查询结果临时存到指定的表达式上，这个表达式就叫公用表表达式 CTE(Common Table Expression)。 可以简单理解为一个不会实际创建的视图。

WHY

为什么要用这个呢？

经常遇到多表需要关联查询，虽然所有的都可以通过视图, 临时表，或者多写几个SELECT来实现，但是多数为一次性查询，没必要创建视图，而SELECT的可读性太低（特别是业务复杂的时候）。使用WITH就可以避免这些问题，也不需要多余的开支。

Syntax

[ WITH <common_table_expression> [ ,...n ] ]  

<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )

• expression_name [^xxxka] 公用表表达式的有效标识符（必须不同与其他CTE名称,可以与基视图或基表名称相同）
• column_name 公用表达式列名
• CTE_query_definition 查询语句（与创建视图要求相同）

Examples

用到的表

卡值流水表 CardHistoryTable(只有总的卡值变化)
卡值流水详情表 CardHistoryDetailTable(包括支付宝，微信卡值变动)

简单的例子

WITH CardPerson AS
(
SELECT PersonId,MAX(id) AS maxId FROM CardHistoryTable GROUP BY PersonId)
SELECT * FROM CardPerson 

使用多个CTE

WITH MiddlePersonDetail AS
(
SELECT m.mainId, m.fee, m.is_wechat, m.wechat_balance, m.id, m.alipay_balance,f.PersonId 
FROM CardHistoryDetailTable m LEFT JOIN CardHistoryTable f ON f.id = m.mainId
),
MiddlePerson AS
(
SELECT PersonId,MAX(id) AS maxid FROM MiddlePersonDetail GROUP BY PersonId
)
SELECT * FROM MiddlePerson

实际查询场景

CardHistoryTable包含多种类型的卡值变化（收入，消费支出，转账，还款等），detail计算的程序有一部分并不处理（比如还款，由操作员手动录入），现在想查每个人最后的余额在两个表是否一致，以及剩余的支付宝，微信余额等,再决定数据处理方式

WITH CardPerson AS
(
SELECT PersonId,MAX(id) AS maxId FROM CardHistoryTable GROUP BY PersonId),
MiddlePersonDetail AS
(
SELECT m.mainId, m.fee, m.is_wechat, m.wechat_balance, m.id, m.alipay_balance,f.PersonId 
FROM CardHistoryDetailTable m LEFT JOIN CardHistoryTable f ON f.id = m.mainId
),
MiddlePerson AS
(
SELECT PersonId,MAX(id) AS maxid FROM MiddlePersonDetail GROUP BY PersonId
)

SELECT mg.PersonId,mg.maxid,m.wechat_balance,m.is_wechat,m.alipay_balance,
m.mainId, f.id,f.this_balance 
FROM MiddlePerson mg 
LEFT JOIN CardHistoryDetailTable m ON m.id = mg.maxid 
LEFT JOIN MiddlePerson fg ON fg.PersonId = mg.PersonId 
LEFT JOIN CardPerson f ON fg.maxId = f.id
--WHERE ...

一些需要注意的

• 如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：UNION ALL、UNION、EXCEPT 或 INTERSECT。
• CTE 之后必须跟随引用部分或全部 CTE 列的单条 SELECT、INSERT、UPDATE 或 DELETE 语句。 也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。

MORE

本文只讨论非递归公用表达式，对于递归的参见 docs.microsoft.com

REF

MICROSOFT DOCS