如何按点分隔数字字符串排序？CTE树形查询排序异常求助

这问题我之前做树形结构遍历的时候也踩过坑！字符串排序的逻辑和数字排序完全不一样，所以才会出现0.0.10排在0.0.2前面的情况。下面给你几个实用的解决方案：

方案1：排序时转换为整数数组（推荐PostgreSQL使用） #

如果你的数据库支持数组类型（比如PostgreSQL），可以直接把camino按.拆分成数组，再转成整数数组来排序。这样数据库会按每个分段的数值大小来比较，完全符合你的需求：

ORDER BY string_to_array(camino, '.')::int[]

比如0.0.1会转成[0,0,1]，0.0.10转成[0,0,10]，0.0.9转成[0,0,9]，数组排序时会逐元素比较，自然[0,0,9] < [0,0,10]，完美解决问题。

方案2：生成路径时补前导零（通用高性能方案） #

如果数据量比较大，或者你不想在排序时做额外转换，可以在生成camino字段的时候，给每个posicion补前导零，固定每个分段的长度（比如3位）：

camino || '.' || lpad(CAST(rel.posicion AS text), 3, '0')

这样生成的路径会变成0.0.001、0.0.009、0.0.010、0.0.002，此时直接按字符串排序就能得到正确的顺序：0.0.001 < 0.0.002 < 0.0.009 < 0.0.010。这个方案的优势是排序时不需要额外计算，性能更好，适合大规模数据场景。

方案3：逐段拆分转换排序（适合MySQL等不支持数组的数据库） #

如果你的数据库不支持数组操作（比如MySQL），可以把camino的每个分段拆出来，转成整数后逐个排序。假设你的路径最多有5层，可以这么写：

ORDER BY
  CAST(SUBSTRING_INDEX(camino, '.', 1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(camino, '.', 2), '.', -1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(camino, '.', 3), '.', -1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(camino, '.', 4), '.', -1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(camino, '.', -1) AS UNSIGNED)

这种方式虽然繁琐，但能确保每一个分段都按数值大小排序，也能达到你要的效果。如果路径层数不确定，可能需要动态处理，但大部分树形结构的层数都是可控的。

为什么原来的排序会出问题？ #

简单来说，字符串排序是逐字符比较：比如比较0.0.1和0.0.10时，前三个字符都是0.0.1，但0.0.1到这里就结束了，而0.0.10还有后续字符，所以字符串排序会认为0.0.1 < 0.0.10；再比较0.0.10和0.0.2时，第三个分段的第一个字符1比2小，所以0.0.10 < 0.0.2，这就不符合我们的数字排序预期了。

内容的提问来源于stack exchange，提问作者exodehm