具有空值的多个字段的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

"Original Answer"翻译成中文为“最初的回答”。

+----------------+----------+--------+--------+--------+---------+
| 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”、dev_id为“3”且建筑物为“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，但返回了ids 3和4。如果使用AND，则不返回任何行，因为没有列具有所有值。

是否可能以这种方式加入表以实现此目的，或者还有其他方法来构造数据？

翻译成简单易懂的语言：

使用OR分隔多个连接可能导致无法考虑到每一行的AND条件。比如说，maint_id为2的应该只对equip_id为3的适用，但是返回了id为3和4的数据。如果使用AND，则没有返回数据，因为没有一行数据同时拥有所有的列。

是否有可能以其他方式连接表格或者再构造一些数据结构呢？

最初的回答：

- user10981012

---

可能是SQL Server查询：使用JOIN包含NULL值的重复问题。 - noraj

Answer 1

如果我理解正确的话，当maint_equip中与设备相关的ID为空时，应该将其视为匹配。只有当它不为空时，才必须与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;

- sticky bit

---

这正是我一直在寻找的。谢谢！ - user10981012

Answer 2

试试这个：

   ( equip.mfg_id  = maint_equip.mfg_id  OR maint_equip.mfg_id  is null )
AND( equip.mod_id  = maint_equip.mod_id  OR maint_equip.mod_id  is null )
AND( equip.dev_id  = maint_equip.dev_id  OR maint_equip.dev_id  is null )
AND( equip.bldg_id = maint_equip.bldg_id OR maint_equip.bldg_id is null )

- David Dubois

Answer 3

请注意，您的mod_id始终为null。否则，下面的查询将遍历所有情况。

  SELECT  maint_equip.maint_id, equip.equip_id, maint.freq
FROM    equip INNER JOIN
    maint_equip ON (
    (equip.mfg_id = maint_equip.mfg_id AND 
        equip.dev_id = maint_equip.dev_id AND
        equip.bldg_id = maint_equip.bldg_id
     ) OR
     (equip.mfg_id = maint_equip.mfg_id AND 
        maint_equip.dev_id is NULL AND
        equip.bldg_id = maint_equip.bldg_id
     ) OR
     (maint_equip.mfg_id is NULL AND 
        equip.dev_id = maint_equip.dev_id AND
        maint_equip.bldg_id is NULL
     ) OR 
     (maint_equip.mfg_id is NULL AND
        maint_equip.dev_id is NULL AND
        equip.bldg_id = maint_equip.bldg_id
     ) )


      INNER JOIN
    maint ON maint_equip.maint_id = maint.maint_id
   ; 

- Oly

Answer 4

在我看来，你实际上要找的是具有最高匹配次数的维护计划表。你可以通过使用一系列CASE表达式和SUM函数来获取匹配列的计数。

然后，你需要考虑到当有多个maint_id值匹配相同次数时的情况。对于下面的示例，我选择使用维护频率作为决定因素，更青睐频率更高的维护而不是频率较低的维护。

设置了数据集的Rextester链接：https://rextester.com/VISR88105

ORDER BY子句中的ROW_NUMBER按照列匹配次数（通过疯狂的SUM/CASE组合）的降序排序，以便首先获取最多的匹配项，然后按照维护频率的升序排序，以更青睐频率更高的维护。如果你喜欢，可以通过使用DESC来反转排序顺序。然后，TOP (1) WITH TIES将结果集限制为所有ROW_NUMBER评估为1的行。

代码如下：

SELECT TOP (1) WITH TIES
  e.equip_id, 
  m.maint_id, 
  m.freq
FROM 
  #maint as m
JOIN
  #maint_equip as me
    ON
      m.maint_id = me.maint_id
JOIN
  #equip as e
    ON 
      e.mfg_id = COALESCE(me.mfg_id, e.mfg_id)
      AND
      e.mod_id = COALESCE(me.mod_id, e.mod_id)
      AND
      e.dev_id = COALESCE(me.dev_id, e.dev_id)
      AND
      e.bldg_id = COALESCE(me.bldg_id, e.bldg_id)
GROUP BY 
  e.equip_id, 
  m.maint_id, 
  m.freq
ORDER BY 
  ROW_NUMBER() OVER (PARTITION BY e.equip_id ORDER BY (
    SUM( 
    (CASE WHEN e.mfg_id = me.mfg_id THEN 1 ELSE 0 END) +
    (CASE WHEN e.mod_id = me.mod_id THEN 1 ELSE 0 END) +
    (CASE WHEN e.dev_id = me.dev_id THEN 1 ELSE 0 END) +
    (CASE WHEN e.bldg_id = me.bldg_id THEN 1 ELSE 0 END)) ) DESC, m.freq )

结果：

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

- Eric Brandt