某树状结构排序

对象Q有pk，亲pk和表示顺3个属性，它的实例组成list，现在需要按树状结构(按亲->子，同级再按表示顺小到大)排序。

逻辑部分：

function testNum(qs,orderedList){					//非逻辑部分，简单测试用
	console.log('length-->',qs.length,orderedList.length)
	if(qs.length!=orderedList.length){
		for(let i in qs){
			let pk=qs[i].pk
			var flg=false
			for(let j in orderedList){
				if(orderedList[j].pk==pk){flg=true;break}
			}
			if(!flg){console.log(pk,'not found in orderedList')}
		}
	}
}

function print(orderedList){						//非逻辑部分，简单测试用
	let split='->'
	for(let i in orderedList){
		let q=orderedList[i]
		var prefix=''
		let depth=q.depth
		delete q.depth
		for(j=0;j<depth;j++){
			prefix=prefix+split
		}
		console.log(prefix,q)
	}
}

/*
qs.sort可以在sql文里进行，目的是使同级别的对象按表示顺逆序排列。
marked是搜索时用来标识已经走过的点，防止重复走。此例不存在一个pk对应两个ppk的情况，所以不需要。
//--------------标记的代码是记录深度，如果只排序不需要。
思路是遍历list，生成有向图，所有点加入图中，没有亲的点(顶点)还要入栈，之后dfs。
时间空间复杂度都是线性，和list.length成正比。
*/
function getOrderedList(qs){
	function sortByOrderDesc(a,b){
		return b.order-a.order
	}
	qs.sort(sortByOrderDesc)
	var stack=[]
	var graph={}
	//var marked={}
	var orderedList=[]
	for(let i in qs){
		let q=qs[i]
		let pk=q.pk
		let ppk=q.ppk
		//marked[pk]=false
		if(ppk=='null'){
			q.depth=0					//--------------
			stack.push(q)
		}else{
			if(graph[ppk]==undefined){
				graph[ppk]=[]
			}
			graph[ppk].push(q)
		}
	}

	while(stack.length>0){
		let q=stack.pop()
		let pk=q.pk
		//if(marked[pk])continue
		orderedList.push(q)
		//marked[pk]=true
		if(graph[pk]!=undefined){	
			let depth=q.depth+1			//--------------
			for(let i in graph[pk]){		//--------------
				graph[pk][i].depth=depth	//--------------	
			}								//--------------
			stack=stack.concat(graph[pk])
		}
	}
	return orderedList
}



var qs=[{pk:1,ppk:"null",order:1,},{pk:3,ppk:"null",order:3,},
{pk:5,ppk:"null",order:5,},{pk:7,ppk:"null",order:7,},pk:8,ppk:"null",order:8,},
{pk:9,ppk:"null",order:9,},{pk:17,ppk:1,order:7,},{pk:37,ppk:3,order:7,},
{pk:55,ppk:5,order:5,},{pk:77,ppk:7,order:7,},{pk:80,ppk:8,order:19,},
{pk:91,ppk:9,order:1,},{pk:93,ppk:9,order:3,},{pk:171,ppk:17,order:1,},
{pk:379,ppk:37,order:9,},{pk:550,ppk:55,order:19,},{pk:774,ppk:77,order:15,},
{pk:913,ppk:91,order:3,},{pk:937,ppk:93,order:7,},]
let orderedList=getOrderedList(qs)
testNum(qs,orderedList)
print(orderedList)

sql部分：@tmp用来防止找不到@p对应的数据时@p不变，出现死循环。
思路是在@str里找分隔符’,’，找不到说明str里只有一个pk，lp:loop查询完后退出，否则找到就分割出一个pk给lp:loop查询，查询完后把该’,'后的字符串赋给@str，重复上述操作。

delimiter $
select * from q$
drop   procedure if exists p1$
create  procedure p1(in str varchar(99))
begin
	set @str=str;	
	drop  table if exists tmp;
    CREATE table tmp like q;
    lp:loop
		set @i1=instr(@str,',');
		if @i1=0 then
			set @tmp= @str;
		else
			set @tmp=substr(@str,1,@i1-1);
		end if;
   
		set @p=0+@tmp;
		ilp:loop
			set @tmp=@p;
			insert ignore  tmp  select pk,@p:=ppk,`order` from q where pk=@p;
            #select @p,sleep(3);
            
			if @p is null or @tmp=@p then
				leave ilp;
			end if;
		end loop;
        
		if @i1=0 then
			leave lp;
		else
            set @str=substr(@str,@i1+1);
		end if;
    end loop;
    select * from tmp;
end;
$

delimiter  ;
SELECT * FROM jd.q;
truncate q;
call p1('937,913,774,550,171,80,379');
call p1('11111');

vba部分：
用来生成有规律的数据方便测试，并用数据生成json字符串保存到剪切板供js代码使用

'[{pk:1,ppk:1,order:1},{pk:1,ppk:1,order:1},****]
Public Sub generate_test_json()
    json = "["
    With ActiveSheet
        col_count = .Cells(1, 222).End(1).Column        '3
        row_count = .Cells(55555, 1).End(3).Row         '??
        arrField = .Range(.Cells(1, 1), .Cells(1, col_count)).Value
        arr = .Range(.Cells(2, 1), .Cells(row_count, col_count)).Value
        For i = LBound(arr, 1) To UBound(arr, 1)
            json = json & "{"
            For j = LBound(arr, 2) To UBound(arr, 2)
                v = arr(i, j)
                If LCase(v) = "null" Then
                    v = """null"""
                End If
                json = json & arrField(1, j) & ":" & v & ","
            Next j
            json = json & "},"
        Next i
    End With
    json = json & "]"
    Debug.Print json
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText json
        .PutInClipboard
    End With
End Sub

'生成row_num个数据，每个亲下有jinzhi个子，除非数据量不够。
'表示顺pk按mod jinzhi后，奇数递增偶数递减的规则给值。
Public Sub generateData()
    row_num = CLng(InputBox("please insert your row_num", Default:=100))
    col_num = 3
    Dim arr()
    '这么设置arr并在后面转置是因为二维数组redim时只能redim最后一维。
    ReDim arr(1 To col_num, 1 To row_num)
    jinzhi = CLng(InputBox("please insert your jinzhi", Default:=10))
    For i = 1 To row_num
        arr(1, i) = i
        arr(2, i) = (i - i Mod jinzhi) / jinzhi
        arr(3, i) = getOrder(i, jinzhi)
    Next i
    For i = 1 To jinzhi
        arr(2, i) = "null"
    Next i
    
    With ActiveSheet
        .Cells.ClearContents
        .Cells(1, 1) = "pk"
        .Cells(1, 2) = "ppk"
        .Cells(1, 3) = "order"
        .Cells(2, 1).Resize(row_num, col_num) = Application.Transpose(arr)
    End With

End Sub



Function getOrder(ByVal pk As Long, Optional ByVal jinzhi As Long)
    o = pk Mod jinzhi
    If o Mod 2 = 0 Then
        o = jinzhi * 2 - 1 - o
    End If
    getOrder = o
End Function