如下的SQL语句查询不到任何记录,尽管数据库中有符合条件的记录。这是Oracle三层查询的bug。
select cb.* from mw_app.t_cb cb where cb.obj_id in
(select cba.channel_id from mw_app.t_current_business_alarm cba where cba.business_id in
(select b.business_id from mw_app.t_rt_business b where b.alarm_state <> '0' and b.business_code = '1') )
要想查询到结果,就需要更改sql语句,可以改成
select cb.* from mw_app.t_cb cb Left Join (select alarm.channel_id from mw_app.t_current_business_alarm alarm
where alarm.business_id in ( select business_id from mw_app.t_rt_business where alarm_state <> '0' and business_code = '1' )) b
On cb.obj_id=b.channel_id
或者
select cb.* from mw_app.t_cb cb Left Join (
select alarm.channel_id from mw_app.t_current_business_alarm alarm left join mw_app.t_rt_business biz
on alarm.business_id = biz.business_id
where (biz.alarm_state <> '0' and biz.business_code = '1') ) b
On cb.obj_id=b.channel_id
Oracle三层嵌套查询的bug
最新推荐文章于 2022-06-14 18:40:00 发布