从SQL查询中排除记录

问题描述：

给定一个或多个owner_ids（例如2,4和6），我想返回链接到owners的resources.id的列表owners_has_resources。没问题，我可以做SELECT DISTINCT ohr.resources_id FROM owners_has_resources ohr WHERE ohr.owners_id IN (2,4,6);从SQL查询中排除记录

现在我坚持的部分。我想回的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)

你的用户表是什么？这张桌子是和其他人联系在一起的吗？ – Wistar

对不起，错字。这应该是'所有者'。我会更新。 – user1032531

这似乎很明显，你需要什么。但我会给予怀疑的好处。你可以显示一个小数据样本。这样我可以确定你在问什么？ –

答

什么

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 (o.deleted = FALSE OR ohr.deleted = FALSE);

我认为它需要'o.deleted = FALSE 或ohr.deleted = FALSE'，但这似乎会导致其他问题。 – user1032531

@ user1032531我做了更改。该查询面临什么问题？ – Wistar

鉴于您的第一个查询和我的编辑帖子中的虚构数据，它将仅返回resources_id＃3，并且您编辑的查询将返回＃1，＃2和＃3。它应该只返回＃2和＃3。 – user1032531

答

首先，你选择你想要，那么你只能选择那些不被非删除的业主引用在NOT EXISTS块

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 
)

谢谢StephaneM。需要稍微调整一下。 – user1032531

答

SELECT DISTINCT ohr1.resources_id 
FROM owners_has_resources ohr1 
LEFT OUTER JOIN owners_has_resources ohr2 ON ohr2.resources_id=ohr1.resources_id AND ohr2.deleted = TRUE 
LEFT OUTER JOIN owners o ON o.id=ohr2.owners_id AND ohr2.deleted = TRUE 
WHERE ohr1.owners_id IN (2,4,6) AND o.id IS NULL;

数据或

SELECT DISTINCT ohr1.resources_id 
FROM owners_has_resources ohr1 
LEFT OUTER JOIN owners_has_resources ohr2 ON ohr2.resources_id=ohr1.resources_id 
LEFT OUTER JOIN owners o ON o.id=ohr2.owners_id 
WHERE ohr1.owners_id IN (2,4,6) AND o.id IS NULL AND ohr2.deleted = TRUE AND ohr2.deleted = TRUE;

我希望对一种方法是否比其他方法更好的评论。

从SQL查询中排除记录

相关推荐