各位好,现在有一个用户-角色关系数据表记录用户ID以及用户角色,示例如下:USERID ROLE
-----------------
000001 Admin
000001 Client
000002 Operator
000003 Admin
000003 Operator现在编写SQL语句查询出所有不具有Admin角色(ROLE)的用户ID(USERID),非常感谢!
-----------------
000001 Admin
000001 Client
000002 Operator
000003 Admin
000003 Operator现在编写SQL语句查询出所有不具有Admin角色(ROLE)的用户ID(USERID),非常感谢!
解决方案 »
- Oracle ERP 在Report Completed 后是否可以自動的 View Output
- mac lion桌面版下oracle10.2.0 安装问题
- 简单SQL语句
- 关于oracle全文检索实现精确搜索
- 新人提问,PLSQL为什么调试跳不到程序内部
- 可否在视图上用start with 和 connect by 进行树结构查询?
- 安装10G时tnslistener的问题
- 怎样测试用自己写的sql后者pl/sql 测试pl/sql??????
- 请问:怎样在oracle中导入access表格?
- [在线等]一个关于ORACLE触发器的问题,需要查询多个表才能实现,请高手指点一下,谢谢,
- 菜鸟求助:users表空间已满,想添加数据文件。
- 高手看一下
select USERID
from tb
where USERID not in(select distinct USERID from tb where ROLE='Admin')
(
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%'
实测语句 两种都有 楼主可以试试
from tb a
where not exists (select 1
from tb b
where b.userid = a.userid
and b.role = 'Admin')
role not in ('Admin')
group by userid
role not in ('ADMIN')
and userid not in (select userid from tab1 where role = 'ADMIN' group by userid)
group by userid前在的错了,当我没说。请用上面这语句
role not in ('ADMIN')
and userid not in (select userid from tab1 where role = 'ADMIN' group by userid)
group by userid
role not in ('Admin')
group by userid
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')
from tb a
where not exists (select 1
from tb b
where b.userid = a.userid
and b.role = 'Admin')
谢谢您的回复,怎么忘记给您加分了~您的回答非常详细,不仅有示例而且还有截图。 这次是我的疏忽,真是不好意思,下次我会记得把分数给您补上的。同时也要感谢:888888888888,tanshi,hebeishimeng,ccp20022000虽然无法一一给分,但在这里一并致谢了,春节快乐!