mysql - 从 SQL 查询中排除记录

Question

给定一个或多个owner_ids (例如，2、4和6)，我想返回resources.id的列表链接到owners通过owners_has_resources 。没问题，我可以这样做 SELECT DISTINCT ohr.resources_id FROM owners_has_resources ohr WHERE ohr.owners_id IN (2,4,6);

现在是我陷入困境的部分。我想返回 resources.id 的列表与上面相同，但排除任何也链接到未删除所有者(由 owners.deleted!=true 确定)的链接，其中链接未删除(由 owners_has_resources.deleted!=true 确定)

可以假设 owners.deleted是 true对于所有最初提供的owners_ids (即 2、4、6)

例如，给定 owners_ids 2和4，我应该返回resources_id 2 和 3。请注意，我的意思是 deleted=TRUE表明它已被删除，但由于已经发布了使用前一个的答案，因此我不会编辑该问题。相反，下面的真值表显示 owner_not_deleted和resource_not_deleted .

+-----------+-------------------+--------------+----------------------+
| owners_id | owner_not_deleted | resources_id | resource_not_deleted |
+-----------+-------------------+--------------+----------------------+
|         2 | FALSE             |            1 | TRUE                 |
|         2 | FALSE             |            2 | TRUE                 |
|         4 | FALSE             |            2 | TRUE                 |
|         4 | FALSE             |            3 | TRUE                 |
|         5 | TRUE              |            1 | FALSE                |
|         5 | TRUE              |            2 | TRUE                 |
|         7 | TRUE              |            2 | FALSE                |
+-----------+-------------------+--------------+----------------------+

owners
- id (INT PK)
- name, etc
- deleted (true/false)

resources
- id (INT PK)
- name, etc

owners_has_resources
- owners_id (INT PK REFERENCES owners.id)
- resources_id (INT PK REFERENCES resources.id)
- deleted (true/false)

Answer 1

首先选择所需的数据，然后仅选择 NOT EXISTS block 中未删除的所有者未引用的数据

SELECT DISTINCT ohr.resources_id 
FROM owners_has_resources ohr 
JOIN owners o ON ohr.owners_id = o.id
WHERE ohr.owners_id IN (2,4,6)
AND NOT EXISTS (
    SELECT NULL FROM owners_has_resources ohr2 
    JOIN owners o2 ON ohr2.owners_id = o2.id
    WHERE ohr2.deleted=FALSE 
    AND o2.deleted=FALSE
    AND ohr.resources_id=ohr2.resources_id 
)