mysql - 有没有办法获取变量并将它们添加为 SQL 中左连接后的列？

Question

我有 2 张 table : 表A:

| ID |  class1     |  class2 | class3 |  class4 |
+----+-------------+---------+--------+---------+
|  1 |  ABC123     |  23     | C123   | BC123   | 
|  2 |  DEF465     |  65     | F465   | EF465   | 
|  3 |  GHI789     |  89     | I789   | HI789   |
|  4 |  JKL132     |  32     | L132   | KL132   |
|  5 |  MNO456     |  56     | O456   | NO456   |

和表 B:

| ID |  class_desc |     text   | 
+----+-------------+------------+
|  1 |  ABC123     |  "foo"     |  
|  2 |  23         |  "foo b"   |
|  3 |  C123       |  "foo bar" | 
|  4 |  BC123      |  "foo-bar" | 
|  5 |  DEF465     |  "bar"     | 
|  6 |  65         |  "bar f"   | 
|  7 |  F465       |  "bar foo" | 
|  7 |  EF465      |  "bar-foo" | 
etc...

我想做一个左连接，得到一个像表 C 这样的表:

| ID |  class1  |  class2 | class3 |  class4 | class_1_desc | class_2_desc | class_3_desc| class_4_desc|
+----+----------+---------+--------+---------+--------------+--------------+-------------+-------------+
|  1 |  ABC123  |  23     | C123   | BC123   | "foo"        |"foo b"       |"foo bar"    |"foo-bar"    |
|  2 |  DEF465  |  65     | F465   | EF465   | "bar"        |"bar f"       |"bar foo"    |  "bar-foo"  |
|  3 |  GHI789  |  89     | I789   | HI789   | etc...
|  4 |  JKL132  |  32     | L132   | KL132   |
|  5 |  MNO456  |  56     | O456   | NO456   |

提前致谢

Answer 1

您可以加入 4 次:

select 
    ta.*,
    tb1.text class_1_desc,
    tb2.text class_2_desc,
    tb3.text class_3_desc,
    tb4.text class_4_desc
from tableA ta
inner join tableB tb1 on tb1.class_desc = ta.class1
inner join tableB tb2 on tb2.class_desc = ta.class2
inner join tableB tb3 on tb3.class_desc = ta.class3
inner join tableB tb4 on tb4.class_desc = ta.class4

如果tableA中声明的class之一可能在tableB中不存在，那么您需要左连接而不是内连接。

如果您确实想使用查询结果创建一个新表(例如 tableC)，那么您可以使用 create table ... as select 语法:

create table tableC as
select 
    ta.*,
    tb1.text class_1_desc,
    tb2.text class_2_desc,
    tb3.text class_3_desc,
    tb4.text class_4_desc
from tableA ta
inner join tableB tb1 on tb1.class_desc = ta.class1
inner join tableB tb2 on tb2.class_desc = ta.class2
inner join tableB tb3 on tb3.class_desc = ta.class3
inner join tableB tb4 on tb4.class_desc = ta.class4

Demo on DB Fiddle (使用左连接，因为您的示例数据不完整):

ID | class1 | class2 | class3 | class4 | class_1_desc | class_2_desc | class_3_desc | class_4_desc-: | :----- | -----: | :----- | :----- | :----------- | :----------- | :----------- | :----------- 1 | ABC123 |     23 | C123   | BC123  | foo          | foo b        | foo bar      | foo-bar      2 | DEF465 |     65 | F465   | EF465  | bar          | bar f        | bar foo      | null         3 | GHI789 |     89 | I789   | HI789  | null         | null         | null         | null         4 | JKL132 |     32 | L132   | KL132  | null         | null         | null         | null         5 | MNO456 |     56 | O456   | NO456  | null         | null         | null         | null