Oracle 查询--避免使用Union All的情况

记一次Oracel的查询调整
 
业务表定义-- 经济特区定义表
 
 
CityConfig (province varchar2(4), city varchar2(4), flag varchar2(1));
 
-- 市级别的每日收支记录
 
BusiStatRpt(province varchar2(4), city varchar2(4), income number(20,2), Expenses number(20,2), recordDate varchar2(8));
 
 
 
业务场景定义：
按省统计一段时间内的数据，其中经济特区的数据按省级进行统计，经济特区的城市配置在CityConfig表中flag = 1。
 
 
写法一：简单直接，不用思考，但在大数据量的情况下查询效率比较低 
 

-- 普通的写法直接Union all 
Select province, '1' kind, sum(income), sum(Expenses) 
  from BusiStatRpt rpt 
where not exists (select * 
          from CityConfig cfg 
         where cfg.province = rpt.province 
           and cfg.city = rpt.city 
           and cfg.flag = '1') 
group by province 
union all 
Select City, '2', kind, sum(income), sum(Expenses) 
  from BusiStatRpt rpt 
where exists (select * 
          from CityConfig cfg 
         where cfg.province = rpt.province 
           and cfg.city = rpt.city 
           and cfg.flag = '1') 
group by province, city; 
 
  

 
 
写法二：采用子查询，对大表只进行了一次扫描，查询效率不错 
  

-- 优点，采用子查询，对大表只进行了一次扫描 
Select dept, sum(income), sum(Expenses) 
  from (select case 
                 when (select flag 
                         from CityConfig cfg 
                        where cfg.province = province 
                          and cfg.city = city) = '1' then 
                  province || '2' || city 
                 else 
                  province || '10000' 
               end dept, 
               sum(income) income, 
               sum(Expenses) Expenses 
          from BusiStatRpt rpt 
         group by province, city) 
group by province; 
  

 
  
哈哈，同事写出来的，我调整了下，看来自己真的老了，只想着能完成任务就行了，以后还得多学习学习。