Oracle SQL統計各單位及其子級單位用戶總數
業務場景：最近幫同事寫一個sql，業務是統計各個單位及其子單位用戶總數，聽起來是挺容易的，所以拿起鍵盤就是敲：
select sum(t.ucount)
from (select count(1) as ucount, uinfo.unit_code, uinfo.unit_name
from t_user bs
inner join (select unit_code, unit_name
from t_unit_info
start with unit_code = #{unitCode}
connect by prior unit_code = para_unit_code) uinfo
on bs.unit_code = uinfo.unit_code
group by uinfo.unit_code, uinfo.unit_name) t;
不過這個只能傳個頂級單位編碼，一個一個查，這樣肯定很耗時，所以還是自己想太簡單了，摸索了好一陣子，想到方法：
①、新建一個函式，傳一個頂級單位編碼，通過函式統計本單位及其子單位的用戶總數
create or replace function FU_UNIT_USER_COUNT(unitCode in varchar2)
RETURN NUMBER IS
v_Value number(9,1);
BEGIN
select sum(t.ucount) into v_Value
from (select count(1) as ucount, uinfo.unit_code, uinfo.unit_name
from t_user bs
inner join (select unit_code, unit_name
from lzcity_approve_unit_info
start with unit_code = unitCode
connect by prior unit_code = para_unit_code) uinfo
on bs.unit_code = uinfo.unit_code
group by uinfo.unit_code, uinfo.unit_name) t;
RETURN v_Value;
END;
測驗函式是否建立成功：
select FH_UNIT_USER_COUNT('15803') from dual;
②、呼叫函式，傳頂級單位編碼進行統計
select FH_UNIT_USER_COUNT(uinfo.unit_code) as 用戶總數,
uinfo.unit_code as 單位編碼,
uinfo.unit_name as 廳單位名稱
from t_unit_info uinfo
group by uinfo.unit_code, uinfo.unit_name
order by 用戶總數 desc nulls last
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/287216.html
標籤：其他
上一篇：Creating Server TCP listening socket 127.0.0.1:6379: bind: No error。。。啟動遇到問題的解決辦法