在SQL连接中需要帮助

好奇男孩

我有两个表，如下所示：

表：员工

employeeid  code_one    code_two
101          17112       17112
102          17113       17112
103          17114       17112
104          17115       16800
106          17116       17112
107          17117       18000
108          17118       17112

表：代码

codeid  codename
17112   200TS
17113   400TS
17114   100TS
17115   500TS
17116   620TS
17117   899TS
17118   900TS
16800   888TS
18000   912TS

我需要像输出：输出

employeeid  code_one    code_two
101         200TS       200TS
102         400TS       200TS
103         100TS       200TS
104         500TS       888TS
106         620TS       200TS
107         899TS       912TS
108         900TS       200TS

我需要将雇员表中的代码ID与代码表中的相应代码名称进行映射。请帮我。

Lalit Kumar B

您只需要正确地连接表。

表数据

SQL> SELECT * FROM employee;

EMPLOYEEID   CODE_ONE   CODE_TWO
---------- ---------- ----------
       101      17112      17112
       102      17113      17112
       103      17114      17112
       104      17115      16800
       106      17116      17112
       107      17117      18000
       108      17118      17112

7 rows selected.

SQL> SELECT * FROM code;

    CODEID CODENAME
---------- --------
     17112 200TS
     17113 400TS
     17114 100TS
     17115 500TS
     17116 620TS
     17117 899TS
     17118 900TS
     16800 888TS
     18000 912TS

9 rows selected.

询问

使用Oracle连接语法：

SQL> column code_one format a8
SQL> column code_two format a8
SQL> SELECT E.employeeid,
  2    C1.codename AS code_one,
  3    C2.codename AS code_two
  4  FROM Employee e,
  5    code c1,
  6    code c2
  7  WHERE E.code_one = c1.codeid
  8  AND E.code_two   = c2.codeid
  9  /

EMPLOYEEID CODE_ONE CODE_TWO
---------- -------- --------
       108 900TS    200TS
       106 620TS    200TS
       103 100TS    200TS
       102 400TS    200TS
       101 200TS    200TS
       104 500TS    888TS
       107 899TS    912TS

7 rows selected.

SQL>

使用ANSI连接语法：

SQL> SELECT E.employeeid,
  2    C1.codename AS code_one,
  3    C2.codename AS code_two
  4  FROM Employee e
  5  INNER JOIN code c1
  6  ON E.code_one = c1.codeid
  7  INNER JOIN code c2
  8  ON E.code_two = c2.codeid
  9  /

EMPLOYEEID CODE_ONE CODE_TWO
---------- -------- --------
       108 900TS    200TS
       106 620TS    200TS
       103 100TS    200TS
       102 400TS    200TS
       101 200TS    200TS
       104 500TS    888TS
       107 899TS    912TS

7 rows selected.

SQL>

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。