组织表信息查询结果
1.组织表中orule字段包含104592和104607的所有组织,一般用or like即可
select * from waf_ac_organ2biz where orule like '%104592%' or orule like '%104607%';
但是问题来了,如果有好多个,那要不要一直or下去呢,当然可以,但是不好,这是就可以用到
select * from waf_ac_organ2biz where regexp_like (orule,'104592|104607');
但是,如果需要like的结果是另一个查询结果呢,怎么办呢,这就需要直接把查询结果写进去了
比如,需要查询
orule包含这一类组织的所有组织
select o.oid from WAF_AC_ORGAN o join WAF_AC_ORGAN2BIZ ob on o.oid=ob.oid
where ((length(ob.ORULE) between '15' and '16') and substr(ob.orule,9,6)<>'104218')
or ((length(ob.ORULE) between '22' and '24') and substr(ob.orule,9,6)='104218');
那么完整的sql如下
select count(*) from waf_ac_organ2biz where
regexp_like(orule,(select replace(wm_concat(o.oid),',','|') from WAF_AC_ORGAN o join WAF_AC_ORGAN2BIZ ob on o.oid=ob.oid
where ((length(ob.ORULE) between '15' and '16') and substr(ob.orule,9,6)<>'104218')
or ((length(ob.ORULE) between '22' and '24') and substr(ob.orule,9,6)='104218')));
更多关于regexp_like的知识,参考