今天去面试,遇到一个问题。
题目是这样的:
表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>