mysql - 根据条件在 MySQL 中加入不同表的最佳方法？

Question

我有以下表格:
Licenses :

 License    |    object_type_id  |  selected_object_type_id  
  L1                   1                     300 
  L4                   2                     300
  L5                   3                     127 
  ..                   ..                    ...

object_type

id    |    type  |
1          Main
2          Sub
3          Feature

Main

id   | product  | ....
1        PMA
2        PMB
3        PMC
...       ...
300       PMZZ

Sub

id  | product | ...
1       PSA1
2       PSA2
3       ...
300     PSAZZ

Feature

id  |  Feature|  ...
1        FO1
2        FO2
3        FO3
..        ..
127       FFZ127

我想要一个名为“license_mapping”的最终表
license_mapping

 license   |   license_object | license_object_type
   L1              PMZZ              Main
   L4              PSAZZ             Sub
   L5              FFZ127            Feature

逻辑是，对于给定的许可证，找到 licenses.object_type_id .取决于 object_type.type 的值哪里 licenses.object_type_id = object_type.id从对应表中选择与 licenses.selected_object_type_id 匹配的产品或功能.

但是我怎么处理这个案子 Main和 Sub因为它们具有相同的 id 和相同的列名？

如果我要编写伪 SQL 语句，它将如下所示，最后一行是我不明白如何正确执行的地方。

 SELECT
   L.License,
   CASE WHEN OT.type = 'Main' THEN M.Product WHEN OT.type = 'Sub' THEN S.Product WHEN OT.Type = 'Feature' THEN F.Feature END AS License_Objects,
   OT.type As license_object_type
 FROM 
   Licenses L
   JOIN object_type OT on L.object_type_id = OT.id
   JOIN Feature F on L.object_type_id = F.id
   JOIN Main M when OT.type = 'Main' and Sub S when OT.type = 'Sub'

Answer 1

您只需要LEFT JOIN给每个Main , Sub和 Feature selected_object_type_id 上的表值并根据 object_type 从这些表中选择适当的值:

SELECT l.License, 
       CASE WHEN ot.type = 'Main' THEN m.product 
            WHEN ot.type = 'Sub' THEN s.product
            WHEN ot.type = 'Feature' THEN f.Feature
       END AS license_object,
       ot.type AS license_object_type
FROM licenses l
JOIN object_type ot ON ot.id = l.object_type_id
LEFT JOIN main m ON m.id = l.selected_object_type_id
LEFT JOIN sub s ON s.id = l.selected_object_type_id
LEFT JOIN feature f ON f.id = l.selected_object_type_id
ORDER BY l.License

示例数据的输出:

License     license_object  license_object_type
L1          PMZZ            Main
L4          PSAZZ           Sub
L5          FFZ127          Feature

Demo on dbfiddle