oracle 多个with as

今天去面试，遇到问题。

题目是这样的:

表1：

SQL> select * from tb_data;        ID      CODE1      CODE2---------- ---------- ----------         1          1          2         2          2          1         3          2          3         4          3          1         5          4          2         6          5          3已选择6行。SQL>

表2：

SQL> select * from tb_code;      CODE DESCRIPTION---------- --------------------         1 a         2 b         3 c         4 d         5 eSQL>

要求显示为：

ID DESCRIPTION          DESCRIPTION---------- -------------------- --------------------         1 a                    b         2 b                    a         3 b                    c         4 c                    a         5 d                    b         6 e                    c

也就是说，将表1中的code翻译成表2中的description。

因为面试官坐在旁边，以前没写过，也没怎么用脑子写。

现在回来后，我想了想。可以使用多个with。 as来解决。

WITHT3 AS(SELECT T1.ID, T1.CODE1, T2.DESCRIPTIONFROM TB_DATA T1, TB_CODE T2WHERE T1.CODE1 = T2.CODE),T4 AS(SELECT T1.ID, T1.CODE2, T2.DESCRIPTIONFROM TB_DATA T1, TB_CODE T2WHERE T1.CODE2 = T2.CODE)SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTIONFROM T3, T4WHERE T3.ID = T4.IDORDER BY ID;

执行后：

SQL> WITH  2  T3 AS  3  (  4  SELECT T1.ID, T1.CODE1, T2.DESCRIPTION  5  FROM TB_DATA T1, TB_CODE T2  6  WHERE T1.CODE1 = T2.CODE  7  ),  8  T4 AS  9  ( 10  SELECT T1.ID, T1.CODE2, T2.DESCRIPTION 11  FROM TB_DATA T1, TB_CODE T2 12  WHERE T1.CODE2 = T2.CODE 13  ) 14  SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTION 15  FROM T3, T4 16  WHERE T3.ID = T4.ID 17  ORDER BY ID;        ID DESCRIPTION          DESCRIPTION---------- -------------------- --------------------         1 a                    b         2 b                    a         3 b                    c         4 c                    a         5 d                    b         6 e                    c已经选择了6行。SQL>