oracle with as 弊端,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 e

SQL>

要求显示成：

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来解决。

WITH

T3 AS

(

SELECT T1.ID, T1.CODE1, T2.DESCRIPTION

FROM TB_DATA T1, TB_CODE T2

WHERE T1.CODE1 = T2.CODE

),

T4 AS

(

SELECT T1.ID, T1.CODE2, T2.DESCRIPTION

FROM TB_DATA T1, TB_CODE T2

WHERE T1.CODE2 = T2.CODE

)

SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTION

FROM T3, T4

WHERE T3.ID = T4.ID

ORDER 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>