WITH common_table_expression

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

1
2
3
4
5
6
[ 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

用到的表

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

简单的例子

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

使用多个CTE

1
2
3
4
5
6
7
8
9
10
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计算的程序有一部分并不处理(比如还款,由操作员手动录入),现在想查每个人最后的余额在两个表是否一致,以及剩余的支付宝,微信余额等,再决定数据处理方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

评论