时间戳

首页 > 解决方案 > MYSQL 使用动态条件连接表 CASE

mysql - MYSQL 使用动态条件连接表 CASE

问题描述

我希望能够加入“图标”表和“产品”表看起来像“加入”表

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;

有谁知道我怎么能像这样比较两个表？

标签： mysqlsql

解决方案

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

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

在 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

在 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

在 DB Fiddle 上查看

推荐阅读