具有空值的多个字段的 SQL 联接

Question

我有一张维护要求表和相关的每月执行频率

维护

+----------+------+
| maint_id | freq |
+----------+------+
|        1 |    6 |
|        2 |   12 |
|        3 |   24 |
|        4 |    3 |
+----------+------+

我还有一张设备表，其中包含有关其制造商、型号、设备类型和建筑物的数据。

装备

+----------+--------+--------+--------+---------+
| equip_id | mfg_id | mod_id | dev_id | bldg_id |
+----------+--------+--------+--------+---------+
|        1 |      1 |      1 |      3 |       1 |
|        2 |      1 |      2 |      3 |       1 |
|        3 |      2 |      3 |      1 |       2 |
|        4 |      2 |      3 |      1 |       3 |
+----------+--------+--------+--------+---------+

我正在尝试将每个维护要求与其相关设备相匹配。每项要求适用于其应用范围内的特定制造商、型号、设备、设施或其任意组合。我创建了一个表来管理这些关系，如下所示:

维护装备

+----------------+----------+--------+--------+--------+---------+
| maint_equip_id | maint_id | mfg_id | mod_id | dev_id | bldg_id |
+----------------+----------+--------+--------+--------+---------+
|              1 |        1 | NULL   | NULL   | 1      | NULL    |
|              2 |        2 | 2      | NULL   | NULL   | 2       |
|              3 |        3 | NULL   | NULL   | NULL   | 1       |
|              4 |        3 | NULL   | NULL   | NULL   | 3       |
|              5 |        4 | 1      | NULL   | 3      | 1       |
+----------------+----------+--------+--------+--------+---------+

根据上表，要求 1 仅适用于设备类型为“1”的任何设备。要求 2 适用于同时具有制造商“2”和建筑物“2”的所有设备。要求 3 将适用于具有建筑物“1”或建筑物“3”的所有设备要求 4 适用于所有 mfg_id“1”AND dev_id“3”AND building“1”的设备。

我正在尝试编写一个查询，根据 maint_equip 中定义的关系，为我提供所有设备 ID 和所有相关频率要求的列表。我遇到的问题是处理多个连接。我已经试过了:

SELECT  equip.equip_id, maint.freq
FROM    equip INNER JOIN
    maint_equip ON equip.mfg_id = maint_equip.mfg_id 
        OR equip.mod_id = maint_equip.mod_id 
        OR equip.dev_id = maint_equip.dev_id 
        OR equip.bldg_id = maint_equip.bldg_id INNER JOIN
    maint ON maint_equip.maint_id = maint.maint_id

但是使用 OR 分隔多个连接意味着它不考虑每行的 AND 意外事件。例如，maint_id 2 应仅适用于 equip_id 3，但会返回 id 3 和 4。如果使用 AND，则不会返回任何行，因为所有列都没有值。

是否有可能以这种方式连接表来完成此操作，或者是否有其他方式来构建数据？

Answer 1

如果我做对了，当 maint_equip 中的设备相关 ID 为空时，这应该算作匹配。只有当它不为 null 时，它才必须与 equip 中的相应 ID 相匹配。也就是说，您要检查 maint_equip 中的 ID 是否为空或等于 equip 中的 ID。

SELECT e.equip_id,
       m.freq
       FROM equip e
            INNER JOIN maint_equip me
                       ON (me.mfg_id IS NULL
                            OR me.mfg_id = e.mfg_id)
                          AND (me.mod_id IS NULL
                                OR me.mod_id = e.mod_id)
                          AND (me.dev_id IS NULL
                                OR me.dev_id = e.dev_id)
                          AND (me.bldg_id IS NULL
                                OR me.bldg_id = e.bldg_id)
            INNER JOIN maint m
                       ON m.maint_id = me.main_id;