让我膜拜的SQL句子,师傅写的。
select m.id m_id,m.name m_name,r.id r_id,r.type_name r_type_name,d.id dr_id,d.dr_type_name from material m
left join (
select dr.id, ltrim(sys_connect_by_path(dr.type_name,'->'),'->') type_name
from resource_type dr start with dr.type_code='43A00' or dr.type_code='43B00'
or dr.type_code='43C00' or dr.type_code='44C00' or dr.type_code='45B00'
or dr.type_code='46E00' connect by dr.top_type_id=prior dr.id
) r on r.id =m.resource_type_id
left join (
select dd.id,ltrim(sys_connect_by_path(dd.type_name,'->'),'->') dr_type_name from dr_type dd
start with dd.top_type_id is null and dd.type_resource='装备'
connect by dd.top_type_id= prior dd.id
) d on d.id=m.dr_type_id
where m.material_type='装备' and m.name like '%灭火器%'