[SQL进阶] CTE (Common Table Expression/公用表表达式) 与Subquery的区别

简介

A Common Table Expression (aka CTE, aka WITH statement) is a temporary data set to be used as part of a query. It only exists during the execution of that query; it cannot be used in other queries even within the same session.
A subquery is a nested query; it’s a query within a query.

参考文章： https://towardsdatascience.com/sql-for-data-analysis-subquery-vs-cte-699ef629d9eb

CTE和Subquery的相似点

CTE和Subquery都可以用来写相对复杂的SQL，在很多情况下它们可以互为替代。

CTE 语法：

WITH CTE_NAME(column_1, column_2, column_3)
AS
    (--normal SQl query
       SELECT *
       FROM table
       WHERE 1 = 1)

Subquery语法：

SELECT sub.*
  FROM (
        SELECT *
          FROM table
         WHERE 1 = 1
       ) sub
 WHERE sub.resolution = 'NONE'

实战案例及思路分析

接下来，我们将分别用CTE和Subquery来解决一个SQL问题。

SQL Problem：