具有IN和NOT IN条件的单个表上的内部查询 [英] Inner queries on a single table with IN and NOT IN conditions

问题描述

这是对我以前的回答的问题

我在下表中有数据:

ROLE_ID | USER_ID
------------------
 14     | USER A
 15     | USER A
 11     | USER B
 13     | USER D
 13     | USER A
 15     | USER B
 15     | USER D
 12     | USER C
 15     | USER C

我想获取仅具有13和15的用户ID.因此，根据上面的示例，我应该只返回USER D

I would like to get user ids that ONLY have 13 and 15. So based on the example above, I should only get back USER D

下面的查询是我以前的回答中提供的内容，而NOT IN部分是我添加的，但是并没有达到目标.

The query below was provided in my previous answer and the NOT IN part was added by me, however, that doesn't achieve the goal..

select user_id
  from my_table
 where role_id in (13,15) AND role_id not in (11,14)
 group by user_id.
having count(distinct role_id) = 2

推荐答案

假设user_id和role_id的组合是唯一的，则可以执行类似的操作

Assuming the combination of user_id and role_id is unique, you could do something like

select user_id
  from my_table
 where role_id in (13,15,11,14) 
 group by user_id.
having sum( case when role_id in (13,15) then 1 else 0 end) = 2
   and sum( case when role_id in (11,14) then 1 else 0 end) = 0

如果user_id和role_id的组合不是唯一的，则原始count中的distinct是必要的，并且事情会变得更具挑战性.

If the combination of user_id and role_id is not unique, then the distinct in your original count is necessary and things get a bit more challenging.

这篇关于具有IN和NOT IN条件的单个表上的内部查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！