各位好，现在有一个用户-角色关系数据表记录用户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')