I just met a problem with the sort of a tree structure table, so I thought of a deep bug hidden. I usually designed such a table of tree-type structures: three columns, ID, ParentID, Treekey in the table and sorting
ID: The unique main key ParentID recorded by this article: The number of the parent record Treekey: Indicates the hierarchical structure, the shape is like xx.xxx.xxxx
The key is Treekey, I used to count the number of the father ID, like this
ID Parentid Treekey ----------------------------------------------- ---------------------- 1 NULL 12 1 1.23 1 1.3 ... 10 1 1.1011 1 1.11
As such, you will have a problem in ordering, because Treekey is a string, and 1.10 will be ranked 1.2, 1.3, it is obvious that this is not the result we want. To solve it is also very simple, supplement 0, for example, 101.0010, 1.2 is written into 0001.0002, so that there is no problem.
This reminds me of the software code of Microsoft's related trees that are watching very ago, I see them in this way of 000x.000x, I still feel so troublesome, now I want to come, I found it myself. No known, huh, huh.