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;
哈哈，同事写出来的，我调整了下，看来自己真的老了，只想着能完成任务就行了，以后还得多学习学习。
页:
[1]