mysql - 如果表 1 有 2 行与表 2 匹配，则 INNER JOIN

Question

我不太知道如何正确表达这个问题，所以我将从数据开始，因为我认为这样会更有意义。

从这两个表开始:

Table 1:
user_id | equipment_id
------ | ------
1      | 2
1      | 3
1      | 6
2      | 2
2      | 6

Table 2:
equipment_id | exercise_id
------ | ------
2      | 1
3      | 2
6      | 2

我想创建第三个表:

Table 3:
user_id | exercise_id
------ | ------
1      | 1
1      | 2
2      | 1

这似乎是一个简单的 INNER JOIN 来获取数据，但我在第二行遇到了麻烦。我只希望当 user_id 1 具有表 2 中列出的两件设备时显示该行。如果其中一件设备丢失，我不希望按照 user_id 2 所示插入数据。我还想确保我不要为每件设备获取两个 user_id 1 的实例。

这是我到目前为止的代码:

INSERT INTO Table3 (user_id, exercise_id) 
SELECT user_id, exercise_id 
FROM Table1
INNER JOIN Table2 
ON Table2.equipment_id = Table1.equipment_id

我得到的结果是这样的，其中指出了需要删除的行:

Table 3:
user_id | exercise_id
 ------ | ------
 1      | 1
 1      | 2
 1      | 2 <--- duplicate of row above
 2      | 1
 2      | 2 <--- user 2 doesn't have equipment 3, so shouldn't be included

任何有关限制结果的帮助将不胜感激。谢谢!

Answer 1

如果我理解正确，您希望检索用户的所有练习，其中该练习所需的所有设备对用户“可用”。我已将一些代码放在 rextester.com/AYRE74108 处。其中重要的部分如下所示:

select distinct
    t1.user_id,
    t2.exercise_id
from
    table_1 t1
JOIN table_2 t2 ON
    t1.equipment_id = t2.equipment_id
where
    not exists
   (select 1
    from
        table_2 t2b
    where
        t2b.exercise_id = t2.exercise_id
        AND not exists (select 1
                        from
                            table_1 t1b
                        where
                            t2b.equipment_id = t1b.equipment_id
                            AND t1.user_id = t1b.user_id)
    )

为了解释逻辑:我从标准连接和 distinct 开始。但随后我要求练习所需的任何设备都不存在，以便该用户/设备组合不存在。这相当于说用户必须“可用”(表 1 中列出)练习所需的所有设备。

或者示意性地表示:“所有 X 都是 Y”===“NO X 不等于 Y”。