построение запроса для формирования таблицы, отсортированной по уровням вложения в дереве
исходная таблица: spr_dep (dep, parent_dep,name)
need_level=60 level_sel=""" SELECT spr_dep.dep, spr_dep.name, concat( %(select_state)s case when spr_dep.parent_dep=0 then "" else concat(lpad(spr_dep.parent_dep,10,"0"),"-") end, lpad(spr_dep.dep,10,"0") ) tree FROM spr_dep left join (select * from spr_dep) z1 on spr_dep.parent_dep=z1.dep %(join_state)s where spr_dep.dep<>0 order by tree """ select_state="" join_state="" for x in range(need_level,0,-1): select_state+=""" case when z%s.parent_dep=0 then "" else concat(lpad(z%s.parent_dep,10,"0"),"-") end,""" % (x,x) for x in range(2,need_level+1): join_state+=""" left join (select * from spr_dep) z%s on z%s.parent_dep=z%s.dep """ % (x,x-1,x) sql=level_sel % locals()
Результат
SELECT spr_dep.dep, spr_dep.name, concat( case when z10.parent_dep=0 then "" else concat(lpad(z10.parent_dep,10,"0"),"-") end, case when z9.parent_dep=0 then "" else concat(lpad(z9.parent_dep,10,"0"),"-") end, case when z8.parent_dep=0 then "" else concat(lpad(z8.parent_dep,10,"0"),"-") end, case when z7.parent_dep=0 then "" else concat(lpad(z7.parent_dep,10,"0"),"-") end, case when z6.parent_dep=0 then "" else concat(lpad(z6.parent_dep,10,"0"),"-") end, case when z5.parent_dep=0 then "" else concat(lpad(z5.parent_dep,10,"0"),"-") end, case when z4.parent_dep=0 then "" else concat(lpad(z4.parent_dep,10,"0"),"-") end, case when z3.parent_dep=0 then "" else concat(lpad(z3.parent_dep,10,"0"),"-") end, case when z2.parent_dep=0 then "" else concat(lpad(z2.parent_dep,10,"0"),"-") end, case when z1.parent_dep=0 then "" else concat(lpad(z1.parent_dep,10,"0"),"-") end, case when spr_dep.parent_dep=0 then "" else concat(lpad(spr_dep.parent_dep,10,"0"),"-") end, lpad(spr_dep.dep,10,"0") ) tree FROM spr_dep left join (select * from spr_dep) z1 on spr_dep.parent_dep=z1.dep left join (select * from spr_dep) z2 on z1.parent_dep=z2.dep left join (select * from spr_dep) z3 on z2.parent_dep=z3.dep left join (select * from spr_dep) z4 on z3.parent_dep=z4.dep left join (select * from spr_dep) z5 on z4.parent_dep=z5.dep left join (select * from spr_dep) z6 on z5.parent_dep=z6.dep left join (select * from spr_dep) z7 on z6.parent_dep=z7.dep left join (select * from spr_dep) z8 on z7.parent_dep=z8.dep left join (select * from spr_dep) z9 on z8.parent_dep=z9.dep left join (select * from spr_dep) z10 on z9.parent_dep=z10.dep where spr_dep.dep<>0 order by tree