纯sql实现树添加子节点时生成序号

// 新增时生成序号
 select CONCAT(IFNULL(MAX(a.sno),0)+1,'') sno from pms_contract_list a where a.contract_id = '%s' and parent_id is null

// 选择一条数据点击新增子项时生成序号
select 
CONCAT((case when LENGTH(SUBSTRING_INDEX(MAX(a.sno) ,'.',LENGTH( MAX(a.sno)) - LENGTH(REPLACE( MAX(a.sno),'.','' ))))=0 
then (select b.sno from pms_contract_list b where id =  '%s') 
else SUBSTRING_INDEX(MAX(a.sno),'.',LENGTH( MAX(a.sno)) - LENGTH(REPLACE( MAX(a.sno),'.','' ))) end ),
'.',
IFNULL(SUBSTRING_INDEX(MAX(REVERSE(left(REVERSE(a.sno),LOCATE('.',REVERSE(a.sno))-1))+0 ),'.',-1),0)+1) sno 
from pms_contract_list a where a.contract_id = '%s' and parent_id= '%s'

效果图如下：