什么是公共表表达式(CTE)

SQL开发过程中，时常会遇到使用一段逻辑作为子查询使用的情况。

子查询通常出现在FROM后代替数据表，或者出现在WHERE后作为条件的一部分来使用。

当我们使用子查询的时候就会面临一些问题，比如子查询的嵌套使用和重复使用的问题。

• 子查询嵌套使用时，一旦嵌套层数过多或逻辑复杂时，就会影响到SQL的可读性。

• 子查询重复引用时，如果需要修改则需同步修改多段逻辑，就会影响到SQL的可维护性。

使用WITH子句，也就是公共表表达式(CTE)则可以一定程度上解决这些问题。

可以认为CTE是将一段逻辑的结果集定义为一个公共表，在当前SQL中可以通过这个公共表名来使用结果集的数据，通过CTE可以将复杂的子查询封装起来，可以通过代码主体的可读性，并使逻辑整体层次清晰。

下面使CTE的使用语法：

 -- CTE语法
 -- 当指定(CTE_字段列表)时，子查询的字段数必须与CTE_字段列表字段数相等
 -- 不指定(CTE_字段列表)时，子查询的字段必须指定别名
 WITH CTE名 (CTE_字段列表) AS (
   子查询
 )
 SELECT CTE_字段列表
   FROM CTE名
 ;
 ​
 -- 一个简单的CTE语句
 WITH TAB1 (COL1, COL2, COL3) AS (
   SELECT C1, C2, C3
     FROM CUSTEM_TABLE
 ),
 TAB2 (COL1, COL2, COL3) AS (
   SELECT C1, C2, C3
     FROM CUSTEM_TABLE
 )
 SELECT TAB1.COL1, TAB1.COL2, TAB1.COL3
      , TAB2.COL2, TAB2.COL3
   FROM TAB1, TAB2
  WHERE TAB1.COL1 = TAB2.COL1
 ;

实例演示

假如有population表存放世界人口统计信息

数据集下载：population.csv _{数据来自世界银行统计}

使用CTE处理嵌套使用

一个SQL中可以同时声明多个CTE，后声明的CTE可以引用先声明的CTE，所以将嵌套引用的子查询按照从内到外的顺序声明为CTE，即可解决嵌套问题，使单个CTE的逻辑更加简介和清晰。

 -- 使用嵌套子查询
 -- 查询中国逐年人口及占世界人口比例
 SELECT YEAR, VALUE, PERCENTAGE
   FROM (
     -- 查询各国家和地区逐年人口占总人口比例
     SELECT T.NAME, T.YEAR, T.VALUE, T.VALUE*1.00/WORLD.VALUE PERCENTAGE
       FROM POPULATION T
       LEFT JOIN (
         -- 查询世界逐年总人口
         SELECT YEAR, VALUE
           FROM POPULATION
          WHERE NAME = 'World'
       ) WORLD ON T.YEAR = WORLD.YEAR
   ) 
   WHERE NAME = 'China'
 ;
 ​
 -- 使用CTE拆分嵌套
 WITH WORLD AS (
     -- 查询世界逐年总人口
     SELECT YEAR, VALUE
       FROM POPULATION
      WHERE NAME = 'World'
 ),
 PERCENT AS (
     -- 查询各国家和地区逐年人口占总人口比例
     SELECT T.NAME, T.YEAR, T.VALUE, T.VALUE*1.00/WORLD.VALUE PERCENTAGE
       FROM POPULATION T
       LEFT JOIN WORLD ON T.YEAR = WORLD.YEAR
 )
 -- 查询中国逐年人口及占世界人口比例
 SELECT * FROM PERCENT WHERE NAME = 'China';

比如上述SQL通过CTE拆分嵌套之后结构会被展开，各部分功能更清晰，方便逐段读取理解功能。

使用CTE处理重复引用

声明过的CTE可以在其他CTE或者查询体中重复使用，效果同临时表的使用方式。在多次引用子查询的时候可以减少代码量，保证结构清晰的同时，还避免了逻辑同步调整的问题。

 -- 计算中国人口每年的同比增长率
 SELECT CURRENT.NAME, CURRENT.YEAR
      , CURRENT.VALUE*100.00/LAST.VALUE-100 GROWTH
   FROM (
     SELECT * 
       FROM POPULATION
      WHERE NAME = 'China'
   ) CURRENT 
   JOIN (
     SELECT * 
       FROM POPULATION
      WHERE NAME = 'China'
   ) LAST
   ON CURRENT.YEAR = LAST.YEAR+1 
 ;
 ​
 -- 使用CTE处理重复引用
 WITH CHINA AS (
     SELECT * 
       FROM POPULATION
      WHERE NAME = 'China'
 )
 SELECT CURRENT.NAME, CURRENT.YEAR
      , CURRENT.VALUE*100.00/LAST.VALUE-100 GROWTH
   FROM CHINA CURRENT 
   JOIN CHINA LAST
   ON CURRENT.YEAR = LAST.YEAR+1 
 ;

使用CTE可以方便的在多处重复使用子查询，并避免了子查询需要同步修改的问题。在子查询逻辑复杂的时候带来的帮助尤为明显。

本文中所有案例都有更简单的写法，这里只是展示CTE对SQL优化的能力，并不是针对案例的最优解。