我用这种方式编写了一个查询，但我想用下面显示的方式对它进行排序。但是，注释没有排序，您无法获得所需的结果。
create table tree_table
(
id int not null,
parent_id int not null,
name nvarchar(30) not null
);
insert into tree_table (id, parent_id, name)
values
(1, 0, '1Title'),
(2, 0, '2Title'),
(3, 0, '3Title'),
(4, 1, ' ㄴRE 1Title 1-1'),
(5, 1, ' ㄴRE 1Title 1-2'),
(6, 1, ' ㄴRE 1Title 1-3'),
(7, 2, ' ㄴRE 2Title 1-1'),
(8, 2, ' ㄴRE 2Title 1-2'),
(9, 2, ' ㄴRE 2Title 1-3'),
(10, 4, ' ㄴRE 1Title 1-1-1'),
(11, 4, ' ㄴRE 1Title 1-1-2'),
(12, 4, ' ㄴRE 1Title 1-1-3'),
(13, 3, ' ㄴRE 3Title 1-1'),
(14, 1, ' ㄴRE 1Title 1-4'),
(15, 6, ' ㄴRE 3Title 1-3-1'),
(16, 0, '4Title'),
(17, 16, ' ㄴRE 4Title 1-1'),
(18, 15, ' ㄴRE 3Title 1-3-1-1'),
(19, 16, ' ㄴRE 4Title 1-2'),
(20, 17, ' ㄴRE 4Title 1-1-1');
选择查询：
WITH rcte AS
(
SELECT
t.id AS 'thread',
t.id,
t.parent_id,
t.name
FROM
tree_table t
WHERE
t.parent_id = 0
UNION ALL
SELECT
r.thread,
t.id,
t.parent_id,
t.name
FROM
tree_table t
JOIN
rcte r ON r.id = t.parent_id
),
sorted_threads AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY MAX(r.id) DESC) AS sort_number,
r.thread
FROM
rcte r
GROUP BY
r.thread
)
SELECT
st.sort_number,
r.id,
r.parent_id,
r.name
FROM
sorted_threads st
JOIN
rcte r ON r.thread = st.thread
ORDER BY
st.sort_number
结果-按以上查询排序：
sort_number id parent_id name
----------- ---- ---------- ----------------
1 16 0 4Title
1 17 16 ㄴRE 4Title 1-1
1 19 16 ㄴRE 4Title 1-2
1 20 17 ㄴRE 4Title 1-1-1
2 1 0 1Title
2 4 1 ㄴRE 1Title 1-1
2 5 1 ㄴRE 1Title 1-2
2 6 1 ㄴRE 1Title 1-3
2 10 4 ㄴRE 1Title 1-1-1
2 11 4 ㄴRE 1Title 1-1-2
2 12 4 ㄴRE 1Title 1-1-3
2 14 1 ㄴRE 1Title 1-4
2 15 6 ㄴRE 3Title 1-3-1
2 18 15 ㄴRE 3Title 1-3-1-1
3 3 0 3Title
3 13 3 ㄴRE 3Title 1-1
4 2 0 2Title
4 7 2 ㄴRE 2Title 1-1
4 8 2 ㄴRE 2Title 1-2
4 9 2 ㄴRE 2Title 1-3
我想这样排序——当评论时，那篇帖子应该放在顶部：
sort_number id parent_id name
----------- ---- ---------- ----------------
1 16 0 4Title
1 17 16 ㄴRE 4Title 1-1
1 20 17 ㄴRE 4Title 1-1-1
1 19 16 ㄴRE 4Title 1-2
2 1 0 1Title
2 14 1 ㄴRE 1Title 1-4
2 15 6 ㄴRE 3Title 1-3-1
2 18 15 ㄴRE 3Title 1-3-1-1
2 6 1 ㄴRE 1Title 1-3
2 10 4 ㄴRE 1Title 1-1-1
2 11 4 ㄴRE 1Title 1-1-2
2 12 4 ㄴRE 1Title 1-1-3
2 5 1 ㄴRE 1Title 1-2
2 4 1 ㄴRE 1Title 1-1
3 3 0 3Title
3 13 3 ㄴRE 3Title 1-1
4 2 0 2Title
4 9 2 ㄴRE 2Title 1-3
4 8 2 ㄴRE 2Title 1-2
4 7 2 ㄴRE 2Title 1-1
如何更改查询以反映上述结果？
谢谢你的回答
递归地构建varchar排序键，并使用技巧降序排序，以便将较短的值视为较大的值。
WITH sorted AS
(
SELECT
right(cast (1000000 + (select coalesce(max(t2.id),t.id) from tree_table t2 where t2.parent_id = t.id )as varchar(max)),6) AS [sort_key],
t.id,
t.parent_id,
t.name
FROM
tree_table t
),
rcte AS (
SELECT *
FROM sorted t
WHERE
t.parent_id = 0
UNION ALL
SELECT
r.sort_key + t.sort_key,
t.id,
t.parent_id,
t.name
FROM
sorted t
JOIN
rcte r ON r.id = t.parent_id
)
SELECT *
FROM rcte
ORDER BY
stuff(replicate(cast ('9' as varchar(max)), (select max(len(r2.sort_key)) from rcte r2)),1,len(sort_key), sort_key)
DESC;
我假设id<1000000为了简单起见，您可以使用bigint算法轻松地将其调整为任何大值。
输出：
sort_key id parent_id name
000019 16 0 4Title
000019000020 17 16 ㄴRE 4Title 1-1
000019000020000020 20 17 ㄴRE 4Title 1-1-1
000019000019 19 16 ㄴRE 4Title 1-2
000014 1 0 1Title
000014000015 6 1 ㄴRE 1Title 1-3
000014000015000018 15 6 ㄴRE 3Title 1-3-1
000014000015000018000018 18 15 ㄴRE 3Title 1-3-1-1
000014000014 14 1 ㄴRE 1Title 1-4
000014000012 4 1 ㄴRE 1Title 1-1
000014000012000012 12 4 ㄴRE 1Title 1-1-3
000014000012000011 11 4 ㄴRE 1Title 1-1-2
000014000012000010 10 4 ㄴRE 1Title 1-1-1
000014000005 5 1 ㄴRE 1Title 1-2
000013 3 0 3Title
000013000013 13 3 ㄴRE 3Title 1-1
000009 2 0 2Title
000009000009 9 2 ㄴRE 2Title 1-3
000009000008 8 2 ㄴRE 2Title 1-2
000009000007 7 2 ㄴRE 2Title 1-1