两个表 ,一个表存id,另一个表存明细,测试表如下:
tb
P_id number (15)
partmentname varchar(15)
userid varchar(15)
测试数据
1 部门1 1
2 部门2 2,3
3 部门3 4
user
id number(15)
name varchar(15)
age number(3)测试数据
1 zhang 25
2 lisi 29
3 wangwu 48
4 zhaoliu 27select name from user where id in( select userid from tb where P_id=2 ) 就不行报错,应该是类型转换的问题,大家帮看一下。
tb
P_id number (15)
partmentname varchar(15)
userid varchar(15)
测试数据
1 部门1 1
2 部门2 2,3
3 部门3 4
user
id number(15)
name varchar(15)
age number(3)测试数据
1 zhang 25
2 lisi 29
3 wangwu 48
4 zhaoliu 27select name from user where id in( select userid from tb where P_id=2 ) 就不行报错,应该是类型转换的问题,大家帮看一下。
where id in( select to_number(userid) from tb where P_id=2 )
---------------- --------------- ---------------
1 部门1 1
2 部门2 2,3
3 部门3 4SQL> select * from usert; ID NAME AGE
---------------- --------------- ----
1 zhangsan 25
2 lisi 49
3 wangwu 48
4 zhaoliu 27SQL>
SQL> select name
2 from usert
3 where exists (select 1
4 from tb
5 where P_id = 2
6 and instr(tb.userid, usert.id) > 0)
7 ;NAME
---------------
lisi
wangwu
SELECT "NAME" FROM "USER" WHERE ID IN (SELECT USERID FROM TB WHERE P_ID = 2);