关于Mysql 父子树

表结构

 查询所有的下级和本身

-- f_distcode 为本节点 f_parentcode 为本节点的父亲节点
--此功能为查询所有的下级目录 包括本身  这个是值查询到 f_distcode 本身节点
with recursive dict as 
( SELECT f_distcode from tc_district where f_distcode = '156430100'
 union all   
SELECT a.f_distcode from tc_district a, dict b where a.f_parentcode = b.f_distcode )
 SELECT f_distcode  from dict )


--查询全部数据
with recursive dict as
 ( SELECT * from tc_district where f_distcode = '156430100'
 union all 
 SELECT a.* from tc_district a, dict b where a.f_parentcode = b.f_distcode ) 
SELECT *  from dict 

把他弄为 省   市 县  区

select 
(case t_.f_type when 'province' then f_name 

--select t0.f_name from tc_district t0,tc_district t_  where t0.f_distcode = t_.f_parentcode and t_.f_type='city'
when 'city' then (select t0.f_name from tc_district t0 where t0.f_distcode = t_.f_parentcode) 

--select * from tc_district t1,tc_district t_ where t1.f_distcode = (select t0.f_parentcode from tc_district t0 where t0.f_distcode t_.f_parentcode= )  and t_.f_type='county'
when 'county' then (select t1.f_name from tc_district t1 where t1.f_distcode = (select t0.f_parentcode from tc_district t0 where t0.f_distcode = t_.f_parentcode)) end) as province, 

(case t_.f_type when 'province' then '-' 
when 'city' then f_name 
when 'county' then (select t0.f_name from tc_district t0 where t0.f_distcode = t_.f_parentcode) end) as city,

(case t_.f_type when 'province' then '-' when 'city' then '-' when 'county' then f_name end) as county
,
f_distcode, f_parentcode from tc_district t_

with  dict as 
(
select 
(case t_.f_type when 'province' then f_name 

--select t0.f_name from tc_district t0,tc_district t_  where t0.f_distcode = t_.f_parentcode and t_.f_type='city'
when 'city' then (select t0.f_name from tc_district t0 where t0.f_distcode = t_.f_parentcode) 
--select * from tc_district t1,tc_district t_ where t1.f_distcode = (select t0.f_parentcode from tc_district t0 where t0.f_distcode t_.f_parentcode= )  and t_.f_type='county'
when 'county' then (select t1.f_name from tc_district t1 where t1.f_distcode = (select t0.f_parentcode from tc_district t0 where t0.f_distcode = t_.f_parentcode)) end) as province, 

(case t_.f_type when 'province' then '-' 
when 'city' then f_name 
when 'county' then (select t0.f_name from tc_district t0 where t0.f_distcode = t_.f_parentcode) end) as city,
(case t_.f_type when 'province' then '-' when 'city' then '-' when 'county' then f_name end) as county
,
f_distcode, f_parentcode from tc_district t_
-------------------------
where f_distcode in ( 
with recursive dict as ( SELECT f_distcode from tc_district where f_distcode = '156430100' union all   SELECT a.f_distcode from tc_district a, dict b where a.f_parentcode = b.f_distcode ) SELECT f_distcode  from dict )
)
, 
---------------------------------
--slzy 是一个虚表
slzy as (select f_xzqdm, f_slfgl from dm_ayt_zygl_slzy_yf where f_n = '2021') 
--dict 是一个虚表
select province, city, county ,f_slfgl  from dict
---虚表链接
left join slzy on dict.f_distcode = slzy.f_xzqdm
where slzy.f_slfgl is not null
order by f_distcode

用递归给层级排序

with recursive dict as(
SELECT f_distcode,
cast(f_name  as varchar(100)) FROM tc_district WHERE f_distcode='156430000'
union all
	SELECT a.f_distcode,
	cast
		(a.f_name || '>' || b.f_name as varchar(100)) as name
	from tc_district a, dict b where a.f_parentcode = b.f_distcode
)SELECT * FROM dict

这个自己查自己的

select t_.area_name as xzqName,
(select sum(total_time) from patrol_analysis_statistics where area_code in 

( with recursive dict as ( SELECT area_code from sys_area where area_code = t_.area_code
 union all SELECT a.area_code from sys_area a, dict b where a.parent_area_code = b.area_code )
	SELECT area_code from dict ) and patrol_time between '2022-12-01' and '2022-12-31') as duration, 
	
	(select sum(total_length) from patrol_analysis_statistics where area_code in 
	( with recursive dict as ( SELECT area_code from sys_area where area_code = t_.area_code union all SELECT a.area_code from sys_area a, dict b where a.parent_area_code = b.area_code ) SELECT area_code from dict ) and patrol_time between '2022-12-01' and '2022-12-04')
	
	 as mileage from sys_area t_ where t_.parent_area_code = '43'

with fscd as 
(select f_qd, f_zd, f_zhd, f_xzqdm from dm_ayt_yhsw_fscd_f where f_nf= 2022 and f_type='总计')
select t_.f_distcode distCode, t_.f_name, t_.f_lat as lat, t_.f_lnt as lnt,
 (SELECT sum(coalesce(f_qd,0)) as qd FROM fscd WHERE f_xzqdm in ( with recursive dict as ( SELECT f_distcode from tc_district where f_distcode = t_.f_distcode union all SELECT a.f_distcode from tc_district a, dict b where a.f_parentcode = b.f_distcode ) SELECT f_distcode from dict )) as qd,
	
	 (SELECT sum(coalesce(f_zd,0)) FROM fscd WHERE f_xzqdm in ( with recursive dict as ( SELECT f_distcode from tc_district where f_distcode = t_.f_distcode union all SELECT a.f_distcode from tc_district a, dict b where a.f_parentcode = b.f_distcode ) SELECT f_distcode from dict )) as zd,
	
		(SELECT sum(coalesce(f_zhd,0)) as zzd FROM fscd WHERE f_xzqdm in ( with recursive dict as ( SELECT f_distcode from tc_district where f_distcode = t_.f_distcode union all SELECT a.f_distcode from tc_district a, dict b where a.f_parentcode = b.f_distcode ) SELECT f_distcode from dict )) as zzd 
		
		from tc_district t_ where t_.f_parentcode = '156430000'