高性能Sql模板
1. 实现业务的查询
优化方案：对业务的理解，界面录入的数据结构和查询的数据机构不一样。
select a.Id from UserRule_Config a ,BusinessRule_Config d
where ((a.Office='test_office' and a.Channel_ID='test_Channel_ID' ) or (a.Office is null and a.Channel_ID is null) ) and a.BusinessRule_id=d.Id
and exists (select 1 from ODRange_Config b,Place_Config c ,Place_Config e
where a.BusinessRule_id=b.BusinessRule_id and c.Place_Code='Place_code1' and e.Place_Code='Place_code1'
and
( (b.Ori_Type='P' and b.Ori=c.Place_Code ) or
(b.Ori_Type='C' and b.Ori=c.City_Code ) or
(b.Ori_Type='N' and b.Ori=c.ATPCO_Country) or
(b.Ori_Type='Z' and b.Ori=c.ZONE) or
(b.Ori_Type='A' and b.Ori=c.AREA) or
(b.Ori_Type='*' )
)
and
( (b.Des_Type='P' and b.Ori=e.Place_Code ) or
(b.Des_Type='C' and b.Ori=e.City_Code ) or
(b.Des_Type='N' and b.Ori=e.ATPCO_Country) or
(b.Des_Type='Z' and b.Ori=e.ZONE) or
(b.Des_Type='A' and b.Ori=e.AREA) or
(b.Des_Type='*' )
)
)
order by Office desc,Sequence_No