oracle表关联字段,oracle一张表的id关联另一张表中的字段

假设现在有两个表

表一：

idnum

sname

score

140643851

小A

87

140643856

小B

88

140643931

小C

92

140643611

小D

77

140643427

小E

81

140643155

小F

95

140643815

小G

78

140643492

小H

90

140643653

小I

71

表二：

id

oname

event

1001

小a

小a在2017-10-19通知了学号为140643851，140643856的学生

1003

小b

小b在2017-10-03通知了学号为140643931的学生

1005

小c

小c在2017-10-08通知了学号为140643611,140643427,140643155的学生

表一的id是学号，表二的event中的数字就是学号，现在要查出这样的表

id

oname

idnum

sname

那怎么做呢？

第一步，要把表二中的学号匹配出来吧？用正则表达式就好啦

‘[0-9]{9}(,[0-9]{9})*’

注意这里的数字是9位，后面可能有若干个逗号加数字，所以用括号括起来，用了‘*’

这个’*’就是零个或者多个的意思啦，{9}表示这样的数字有9位。有关正则可以看这一篇博客总结http://blog.csdn.net/yolanda_nuonuo/article/details/50524758

然后用oracle里的正则函数

regexp_substr(t.event, ‘[0-9]{9}(,[0-9]{9})*’)，这个就可以把数字取出来了。

第二步，第一步的做法拿到的是这样的数字，这个表是test_tb，一个辅助中间表

value

140643851，140643856

140643931

140643611,140643427,140643155

但是要跟表一关联的话，需要的是这样的数字

学号

140643851

140643856

140643931

140643611

140643427

140643155

这个要怎么做呢？用connect by.

SELECT distinct regexp_substr(t.value, '[^,]+',1,level)

FROM test_tb t

CONNECT BY t.value = PRIOR t.value

AND LEVEL <= (length(t.value)-length(regexp_replace(t.value, ',', '')))+1

AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

说明：

这里的level就是一个层级的概念，比如吧

学号

140643851，140643856

140643931

140643611,140643427,140643155

这个第三列，有三个数，就有三层，level就要小于3

所以就要先算出这列有几个数据，可以用下面这句sql试一下

select t.value, (length(t.value)-length(regexp_replace(t.value, ',', '')))+1 from test_tb t

其实就是算出逗号的个数再加一就是有几个数。

最后的sql就是这样啦

select op.id, op.oname, tu.idnum, tu.sname from

(

select distinct to_number(regexp_substr(tb.value, '[^,]+',1,level)) as tid, tb.oid as oid from

(

select regexp_substr(o.event, '[0-9]{9}(,[0-9]{9})*') as value, o.id as oid from test_oper o

where regexp_substr(o.event, '[0-9]{9}(,[0-9]{9})*') is not null) tb

CONNECT BY TB.value = PRIOR TB.value

AND LEVEL <= (length(tb.value)-length(regexp_replace(tb.value, ',', '')))+1

AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

)

mm, test_stu tu, test_oper op

where mm.tid = tu.idnum

and mm.oid = op.id

结果就是

这里临时自定义了几个表，为了对应关系