select * from ba_accounts
--insert into ba_accounts_level(ac_no,ac_no_level,ac_no_other,ac_no_other_level,no_top,cp_no)
with a as
(select level as ll, ac_no,cp_no --level 為所在級别123 本級為1 每上一級加1
from ba_accounts
where cp_no=v_cp_no
start with ac_no = v_data_no
connect by prior ac_top = ac_no )----查詢v_data_no 及所有v_data_no的上級記錄
select v_data_no as data_no,
max(ll) over() as no_level, ----非分組取最大 max(ll) over()
ac_no as name,
rank() over(order by ll desc) as id, --按 ll 反排序後 生成 id 排名列 重複排名占用名次位 （如兩個第一下就個為第三,dense_rank()不占用）
case
when ac_no = v_data_no then
else
1
end,
cp_no
--,ll --3 2 1 (上面有order by ll desc)
from a;
----------Oracle 查詢上下級：下級需要增加 distinct 去重
select distinct * from ba_accounts
--where cp_no=v_cp_no
start with ac_no = v_data_no
connect by prior ac_top = ac_no----查詢v_data_no 及所有v_data_no的上級記錄
select * from ba_accounts
--where cp_no=v_cp_no
start with ac_no = v_data_no
connect by prior ac_no = ac_top ----查詢v_data_no 及所有v_data_no的下級記錄
----------SQL SERVER 查詢上下級：
select cp_no,cp_name,cp_top from sy_company
---------------------
select cp_no,cp_name,cp_top from sy_company
--查找條件節點及其所有上級節點帶級别
with cte_parent(cp_no,cp_name,cp_top,level,gro)
as
(
select cp_no,cp_name,cp_top,0 as level,cp_no as gro from sy_company --where cp_no = '02'--列出子節點查詢條件
union all
select a.cp_no,a.cp_name,a.cp_top,b.level+1,b.gro from sy_company a inner join cte_parent b--執行遞歸，這裡就要了解下了
on a.cp_no=b.cp_top
)
select * from cte_parent order by gro,level;
--查找條件節點及其所有下級節點帶級别
with cte_child(cp_no,cp_name,cp_top,level,gro)
as
(
select cp_no,cp_name,cp_top,0 as level,cp_no as gro from sy_company --where cp_no = '01'--列出父節點查詢條件
union all
select a.cp_no,a.cp_name,a.cp_top,b.level+1,b.gro from sy_company a
inner join cte_child b on ( a.cp_top=b.cp_no)
)
select * from cte_child order by gro,level