数据库有用到反范式,字段CONTACT_IDS上有多个值,这个时候无法直接做关联查询,这个时候需要用instr截取一下,根据多个值中间的逗号位置决定截取的值。
tac.id = to_number ( substr( tapc.CONTACT_IDS, 1, instr( tapc.CONTACT_IDS, ‘,’ ) - 1 ) )
LEFT JOIN fas_user.T_ACCT_CONTACT sec ON sec.id = to_number (
substr(
tapc.CONTACT_IDS,
instr( tapc.CONTACT_IDS, ‘,’, 1, 1 ) + 1,
instr( tapc.CONTACT_IDS, ‘,’, 1, 2 ) - 1-instr ( tapc.CONTACT_IDS, ‘,’, 1, 1 )
)
)
LEFT JOIN fas_user.T_ACCT_CONTACT third ON third.id = substr(
tapc.CONTACT_IDS,
instr( tapc.CONTACT_IDS, ‘,’, 2, 2 ) + 1,
instr( tapc.CONTACT_IDS, ‘,’, 2, 3 ) - 1-instr ( tapc.CONTACT_IDS, ‘,’, 2, 2 )
)