select t2.bomname upitemclassname,t1.plm_bomid,t1.bomname,t1.bomfullname,ifnull(t1.parentid,0) as parentid,t1.num,t1.isdeep,t1.createby,t1.createdate,t1.changeby,t1.changedate from plm_bom t1
left join plm_bom t2 on t1.parentid=t2.plm_bomid and t1.siteid=t2.siteid
where $where$ and  ifnull(t1.isdeep,0)=1  and t1.siteid=$siteid$ order by t1.parentid,ifnull(ISNULL(t1.num)=0 and LENGTH(trim(t1.num))=0,99) asc