MYSQL 使用动态条件 CASE 连接表

Question

我希望能够将“图标”表和“产品”表连接起来，使其看起来像“已连接”表

Table: icon

|  image_name   |     file_location     | 
|----------     |-----------------------|
|   12x12       |  Location_12x12       |
|   12_ifu      |  Location_12_ifu      |
|   12_keep_dry |  Location_12_keep_dry |
|   12_warning  |  Location_12_warning  |
|   12_no_box   |  Location_12_no_box   |
|   12_no_2     |  Location_12_no_2     |


Table: products

|  top_box_01  |  top_box_02  |  top_box_03   |  top_box_04  |
|--------------|--------------|---------------|--------------|  
|   12x12      |  12_ifu      |  12_warning   |  12_no_box   |
|   null       |  12_keep_dry |  12_warning   |  null        |
|   12x12      |  12_keep_dry |  null         |  12_no_2     |
|   12x12      |  12_ifu      |  12_warning   |  12_no_box   |
|   null       |  12_keep_dry |  12_warning   |  null        |
|   12x12      |  12_ifu      |  null         |  12_no_2     |


Table: Joined

|  top_box_01       |      top_box_02       |       top_box_03       |      top_box_04       |
|-------------------|-----------------------|------------------------|-----------------------|  
|   Location_12x12  |  Location_12_ifu      |  Location_12_warning   |  Location_12_no_box   |
|   null            |  Location_12_keep_dry |  Location_12_warning   |  null                 |
|   Location_12x12  |  Location_12_keep_dry |  null                  |  Location_12_no_2     |
|   Location_12x12  |  Location_12_ifu      |  Location_12_warning   |  Location_12_no_box   |
|   null            |  Location_12_keep_dry |  Location_12_warning   |  null                 |
|   Location_12x12  |  Location_12_ifu      |  null                  |  Location_12_no_2     |

我的尝试是这样的，但显然不起作用。

SELECT 
  CASE products.top_box_01 
      WHEN icon.image_name = products.top_box_01 
      THEN icon.file_location ELSE 'null'  
   END AS location_1
FROM products 
   Right JOIN icon 
      ON products.top_box_01 = icon.image_name;

有谁知道如何比较这样的两个表？

Answer 1

您需要为每个 top_box_XX 列加入一次 icon 表:

select
  i1.file_location as top_box_01,
  i2.file_location as top_box_02,
  i3.file_location as top_box_03,
  i4.file_location as top_box_04
from products p
left join icon i1 on i1.image_name = p.top_box_01
left join icon i2 on i2.image_name = p.top_box_02
left join icon i3 on i3.image_name = p.top_box_03
left join icon i4 on i4.image_name = p.top_box_04

结果:

| top_box_01     | top_box_02           | top_box_03          | top_box_04         |
| -------------- | -------------------- | ------------------- | ------------------ |
| Location_12x12 | Location_12_ifu      | Location_12_warning | Location_12_no_box |
| Location_12x12 | Location_12_ifu      | Location_12_warning | Location_12_no_box |
| Location_12x12 | Location_12_ifu      |                     | Location_12_no_2   |
| Location_12x12 | Location_12_keep_dry |                     | Location_12_no_2   |
|                | Location_12_keep_dry | Location_12_warning |                    |
|                | Location_12_keep_dry | Location_12_warning |                    |

View on DB Fiddle

另一种方法是在 SELECT 子句中编写子查询:

select
  (select i.file_location from icon i where image_name = p.top_box_01) as top_box_01,
  (select i.file_location from icon i where image_name = p.top_box_02) as top_box_02,
  (select i.file_location from icon i where image_name = p.top_box_03) as top_box_03,
  (select i.file_location from icon i where image_name = p.top_box_04) as top_box_04
from products p

View on DB Fiddle

如果您有更多类似的查询，编写查找函数可能会更方便:

create function get_image_location(in_image_name text)
  returns text
  return (
    select i.file_location
    from icon i
    where image_name = in_image_name
  );

那么你的查询就会看起来很简单:

select
  get_image_location(top_box_01) as top_box_01,
  get_image_location(top_box_02) as top_box_02,
  get_image_location(top_box_03) as top_box_03,
  get_image_location(top_box_04) as top_box_04
from products p

View on DB Fiddle