SQL中的WITH AS

在Oracle和TDH中可以使用with as语法，将子查询结果进行抽取，达到提高可读性和复用的效果。mysql中没有这个语法。

 WITH A AS (SELECT * FROM USER WHERE AGE > 20),
  B AS (SELECT * FROM USER_SCORE )
 SELECT A.ID,A.NAME,B.SCORE FROM A LEFT JOIN B ON A.UID = B.UID

这是一个很简单的用法。

但是在TDH环境下需要注意，Oracle没有下面的限制：

1、在一条SQL中的这种虚表的名字不能重复

2、在join的右侧表，不能直接使用，需要select * from (T)这种方式包裹

SELECT T1.* FROM (
                   WITH A AS (SELECT * FROM USER),
                       B AS (SELECT * FROM USER_SCORE)
                   SELECT A.ID,A.NAME,B.SCORE FROM A LEFT JOIN B ON A.UID = B.UID
                 ) T1
  LEFT JOIN
  (
    SELECT * FROM ( WITH A AS (SELECT * FROM USER),
      B AS (SELECT * FROM USER_SCORE)
  SELECT A.ID,A.NAME,B.SCORE FROM A LEFT JOIN B ON A.UID = B.UID)T
  ) T2
    ON T1.ID = T2.ID