人数登记率的sql优化(TBL_ZBXT_013_00):
思路:开始的思路是想把每一个符合条件的人员所在区县,转换成地市来进行统计,但是上万条数据逐条进行函数转换极度影响查询效率
因此,先将各区县的人数进行个数统计,展示的效果是显示各个区县的人数,一共将上千上万条数据浓缩成一百条左右。
在对一百条各区县的数据转换成地市数量,在对地市机构id进行分组,并配合使用聚合函数sum来求得各地市总人数。
select fun_getinstiname( f_get_cityinstiid(b.instiid ) )as instiname
,sum(count) sum
from ( select count(0) count,a.instiid instiid
from tbl_zbxt_002 a
WHERE nd = 2018
and ( to_char(sysdate, 'yyyy')-substr(cardid,7,4))=18
and
a.instiid
in (SELECT instiid
FROM mlos_sm_instiinfo
CONNECT BY PRIOR instiid=upinstiid
START WITH instiid = '23000000' --机构号
)group by a.instiid) b group by f_get_cityinstiid(b.instiid)
----------------------------------------------------------------------------------------
递归查询:
SELECT instiid
FROM mlos_sm_instiinfo
CONNECT BY PRIOR instiid=upinstiid
START WITH upinstiid = '23010000' --查询本级以下节点,不包含本身节点
SELECT instiid
FROM mlos_sm_instiinfo
CONNECT BY PRIOR instiid=upinstiid
START WITH instiid = '23010000' --查询本级以下节点,包含本身节点