求教一个查询SQL
各位好，现在有一个用户-角色关系数据表记录用户ID以及用户角色，示例如下：

USERID ROLE
-----------------
000001 Admin
000001 Client
000002 Operator
000003 Admin
000003 Operator

现在编写SQL语句查询出所有不具有Admin角色(ROLE)的用户ID(USERID),非常感谢！

[解决办法]
tb 你的那张表

SQL code

select USERID  from tb where USERID not in(select distinct USERID  from tb where ROLE='Admin')
[解决办法]
create table T99
(
   USERID varchar2(20),
   ROLE varchar2(20)
   
)

INSERT INTO T99 VALUES('000001','ADMIN');
INSERT INTO T99 VALUES('000001','CLIENT');
INSERT INTO T99 VALUES('000002','OPERATOR');
INSERT INTO T99 VALUES('000003','ADMIN');
INSERT INTO T99 VALUES('000003','OPERATOR')

SELECT USERID FROM T99 WHERE USERID NOT IN (SELECT USERID FROM T99 WHERE ROLE='ADMIN')
SELECT USERID FROM (SELECT USERID,WM_CONCAT(ROLE) AS RL ,COUNT(ROLE) AS CT FROM T99 GROUP BY USERID) WHERE RL NOT LIKE '%ADMIN%'


实测语句 两种都有 楼主可以试试
[解决办法]
这个速度快，tb 你的那张表  
SQL code
select userid from tb a where not exists (select 1 from tb b where b.userid=a.userid  and b.role='Admin' )
[解决办法]
这个速度快，tb 你的那张表  
SQL code
select userid  from tb a where not exists (select 1          from tb b         where b.userid = a.userid           and b.role = 'Admin')
[解决办法]
select userid  from tab1 where  
role not in ('ADMIN')
and userid not in (select userid from tab1 where role = 'ADMIN' group by userid)
group by userid

前在的错了，当我没说。请用上面这语句
[解决办法]
select userid from tab1 where 
role not in ('ADMIN')
and userid not in (select userid from tab1 where role = 'ADMIN' group by userid)
group by userid

[解决办法]
select * from tablename t where t.role!=admini
[解决办法]
select userid from tab1 where 
role not in ('Admin')
group by userid
[解决办法]
SQL code
create table U_TAB(  USERID varchar2(20),  ROLE varchar2(20)    )INSERT INTO U_TAB VALUES('000001','ADMIN');INSERT INTO U_TAB VALUES('000001','CLIENT');INSERT INTO U_TAB VALUES('000002','OPERATOR');INSERT INTO U_TAB VALUES('000003','ADMIN');INSERT INTO U_TAB VALUES('000003','OPERATOR')SELECT DISTINCT USERID FROM U_TAB WHERE USERID NOT IN (SELECT USERID FROM U_TAB WHERE ROLE='ADMIN')