在线等，一个小的查询问题。谢谢

[shermantian]

在一张用户权限表中user_role_t，有user_id（用户id）,role_id（权限id）。
一个用户的id对应着多个权限id。如：
user_id role_id
001 01
001 02
001 03
001 04
001 05
001 06
001 07
002 01
002 02
002 03
002 04
003 01
003 02
003 03
要求是查询出来所有的只有权限04的用户，和只有04，11权限的用户。
谢谢拉

[哀]

[shermantian]

大哥不要晕啊 帮帮忙啊 我想了好久了 没想出来

[dushouxian]

只有权限04的用户
select user_id from user_role_t where role_id='04' and user_id in (
select user_id from user_role_t group by user_id having count(role_id) = 1);

[dr_lou]

只有04

select userid from ruletest
where userid in (
  select userid from ruletest where ruleid = '04'
)
group by userid
having count(distinct ruleid) = 1;

04和11

select userid from ruletest
where userid in (
  select userid from ruletest where ruleid in ('04','11')
)
group by userid
having count(distinct ruleid) = 2;

觉得写得很丑陋，肯定有更好的方法。

[dr_lou]

[Quote=引用 3 楼 dushouxian 的回复:]
只有权限04的用户
select user_id from user_role_t where role_id='04' and user_id in (
select user_id from user_role_t group by user_id having count(role_id) = 1);
[/Quote]

方法差不多

[dushouxian]

受教啦

[风过无痕1110]

我比较喜欢用连接查询，用in子查询效率不高。。。
只有04的：

select a.user_id
  from user_role_t a,
       (select user_id
          from user_role_t
         group by user_id
        having count(role_id) = 1) b
 where a.user_id = b.user_id
   and a.role_id = '04'

04和11的：

select a.user_id
  from user_role_t a,
       (select user_id
          from user_role_t
         group by user_id
        having count(role_id) = 1) b
 where a.user_id = b.user_id
   and a.role_id in ('04','11')

[风过无痕1110]

下面的=2
04和11：

select a.user_id
  from user_role_t a,
       (select user_id
          from user_role_t
         group by user_id
        having count(role_id) = 2) b
 where a.user_id = b.user_id
   and a.role_id in ('04','11')

[zheng192004]

支持三楼和四楼的，O(∩_∩)O哈哈~

[songminghong]

lz这都不会么?你是两颗星啊