如何通过SQL Server查询按父ID和子ID顺序排列表数据

使用递归CTE。

假设每层最多有9个。使用单个数字作为序列级别。如果你有9个以上,你将需要使用2个数字,如01,02等

; with
rcte as
(
    -- Anchor member, seq = 1
    select  *, seq = convert(varchar(100), '1')
    from    Category_Level
    where   Child_Id    = 0

    union all

    -- recursive member, concatenate to the seq
    select  c.*, seq = convert(varchar(100), 
                    r.seq 
                +   convert(varchar(10), row_number() over (partition by r.seq 
                                                                order by c.Child_Id)))
    from    Category_Level c
        inner join rcte r   on  c.Child_Id  = r.Parent_Id
)
select  *
from    rcte 
order by seq

/* RESULT
CLevel_Id   Category_Name        Parent_Id   Child_Id    Level_Id    seq
----------- -------------------- ----------- ----------- ----------- -------
12          Jewelry              1           0           1           1
14          Rings                2           1           2           11
15          Men-Rings            3           2           3           111
1013        Metal-Fashion        7           3           4           1111
17          Women-Rings          4           2           3           112
1015        Diamond-Fashion      8           4           4           1121
18          Earrings             5           1           2           12
20          Women-Earings        6           5           3           121
1016        Semi-Set             9           6           4           1211

(9 rows affected)
*/