岗位代码表:dm_gwgwdm gwmc
001 系统岗
002 网络岗
003 应用岗用户岗位表: yhgwxx
userdm gwlist
111 001,002
如何查询出用户111的岗位数据:
gwdm gwmc
001 系统岗
002 网络岗我写的sql执行不了,请问下是什么问题?如何实现?
select gwdm,gwmc from dm_gw where gwdm in(select gwlist from yhgwxx where userdm='111')
001 系统岗
002 网络岗
003 应用岗用户岗位表: yhgwxx
userdm gwlist
111 001,002
如何查询出用户111的岗位数据:
gwdm gwmc
001 系统岗
002 网络岗我写的sql执行不了,请问下是什么问题?如何实现?
select gwdm,gwmc from dm_gw where gwdm in(select gwlist from yhgwxx where userdm='111')
解决方案 »
- 帮忙看看这个sql语句有没有更好的写法(选择执行where条件后的语句),期待各位的回帖
- ORA-02049: 超时: 分布式事务处理等待锁(急)
- 用VC++使用ADO连接Oracle 10g 在XP系统下运行连接不上,在Vista下完全正常!!!
- oracle 11最新版本是多少?
- 数据查询语句优化
- 初学者请教一个从三个相关表中查询的SQL语句
- 如何让ORACLE开机不启动库不吃那么多内存?
- execute immediate 问题??
- 备份数据库出错,大家请帮下忙!急
- java.sql.SQLException: Io 异常: The Network Adapter could not establish the connec
- 急求指点:EXCEL更新ORACEL
- Oracle系统报ora-12500错误,该如何处理?
-----把你的修改一下
with dm_gw as(
select '001' gwdm, '系统岗' gwmc from dual union all
select '002' gwdm, '网络岗' gwmc from dual union all
select '003' gwdm, '应用岗' gwmc from dual
),yhgwxx as
(
select '111' userdm,'001,002' gwlist from dual
)
select gwdm, gwmc
from dm_gw
where gwdm in (select regexp_substr(gwlist, '[^,]+', 1, rownum)
from yhgwxx
where userdm = '111'
connect by rownum <= length(gwlist) -
length(replace(gwlist, ',', '') + 1))
------执行结果如下。。
SQL> with dm_gw as(
2 select '001' gwdm, '系统岗' gwmc from dual union all
3 select '002' gwdm, '网络岗' gwmc from dual union all
4 select '003' gwdm, '应用岗' gwmc from dual
5 ),yhgwxx as
6 (
7 select '111' userdm,'001,002' gwlist from dual
8 )
9 select gwdm, gwmc
10 from dm_gw
11 where gwdm in (select regexp_substr(gwlist, '[^,]+', 1, rownum)
12 from yhgwxx
13 where userdm = '111'
14 connect by rownum <= length(gwlist) -
15 length(replace(gwlist, ',', '') + 1))
16 ;GWDM GWMC
---- ---------
001 系统岗
002 网络岗SQL>
where instr((SELECT gwlist from yhgwxx where userdm='111'),gwdm)>0
select gwdm,gwmc from dm_gw
where instr((select gwlist from yhgwxx where userdm='111'),gwdm)>0
where instr((select gwlist from yhgwxx where userdm='111'),gwdm)>0
这个可以得出结果:
GWD GWMC
--- ------
001 系统岗
002 网络岗
select '001' gwdm,'系统岗' gwmc from dual union all
select '002', '网络岗' from dual union all
select '003', '应用岗' from dual),
yhgwxx as(
select 111 userdm,'001,002' gwlist from dual)
select gwdm,gwmc
from dm_gw,yhgwxx
where instr(gwlist,gwdm)>0
and userdm=111GWD GWMC
--- ------
001 系统岗
002 网络岗