遇到个业务场景,分层级显示数据库的表,该表主键为bas_wrapper_uuid,并且有层级关系字段pre_bas_wrapper_uuid,该字段与某条数据的主键关联。标识上一层级的数据,用level来标识级数,
select level as label_Num,
bw.bas_wrapper_uuid,
bw.wrapper_code,
bw.wrapper_name,
bw.pre_bas_wrapper_uuid,
bw.wrapper_type
from bas_wrapper bw
where 0 = 0
start with bw.pre_bas_wrapper_uuid is null and bw.status='Active' and bw.wrapper_type in('PACKAGE_MATERIAL','PACKAGE_GROUP')
connect by prior bw.bas_wrapper_uuid = bw.pre_bas_wrapper_uuid
order by label_Num
其中level为层级显示的排序,
start whit 后接条件
connect by prior 自关联字段
order by 排序显示,也可以在where 下面加上显示分组之后的查询条件,进行对应的查询