我正在建立一个支持像Reddit这样的嵌套评论的论坛。

• 无限的回复嵌套级别
• 按喜欢/投票对评论进行排序，其中具有较高voteCount的评论显示在顶部，但保留树结构（父项始终直接显示在子项上方）

基本上，我需要的答案在这里：Postgres WITH RECURSIVE CTE: sorting/ordering children by popularity while retaining tree structure (parents always above children).但我需要一个与MySQL一起工作的解决方案
这是我迄今为止没有成功的尝试：

CREATE TABLE `comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent` int DEFAULT NULL,
  `content` text NOT NULL,
  `voteCount` int DEFAULT NULL
);

INSERT INTO ios_community.comment (id,parent,content,voteCount) VALUES
     (1, NULL,'Comment 1'      ,0),
     (2, NULL,'Comment 2'      ,0),
     (3, 2   ,'Comment 2.1'    ,0),
     (4, 2   ,'Comment 2.2'    ,20),
     (5, NULL,'Comment 3'      ,5),
     (6, 3   ,'Comment 2.1.1'  ,0),
     (7, 6   ,'Comment 2.1.1.1',0);

WITH RECURSIVE nested_comments(
id,
content,
voteCount,
path,
level,
sortable
) AS (
SELECT
    id,
    content,
    voteCount,
    CAST(id AS CHAR(1000)),
    0,
    CONCAT('-', CAST(COALESCE(voteCount, 0) AS CHAR(1000)), '.', CAST(id AS CHAR(1000)))
FROM
    comment
WHERE
    parent IS NULL
UNION ALL
SELECT
    c.id,
    c.content,
    c.voteCount,
    CONCAT(nc.path, '.', CAST(c.id AS CHAR(1000))),
    nc.level + 1,
    CONCAT(nc.sortable, '.-', CAST(COALESCE(c.voteCount, 0) AS CHAR(4)), '.', CAST(c.id AS CHAR(1000)))
FROM
    nested_comments nc
JOIN comment c ON
    nc.id = c.parent
)
SELECT
    *
FROM
    nested_comments
ORDER BY
    sortable;

我的结果

+----+-----------------+-----------+---------+-------+---------------------+
| id | content         | voteCount | path    | level | sortable            |
+----+-----------------+-----------+---------+-------+---------------------+
| 1  | Comment 1       | 0         | 1       | 0     | -0.1                |
+----+-----------------+-----------+---------+-------+---------------------+
| 2  | Comment 2       | 0         | 2       | 0     | -0.2                |
+----+-----------------+-----------+---------+-------+---------------------+
| 3  | Comment 2.1     | 0         | 2.3     | 1     | -0.2.-0.3           |
+----+-----------------+-----------+---------+-------+---------------------+
| 6  | Comment 2.1.1   | 0         | 2.3.6   | 2     | -0.2.-0.3.-0.6      |
+----+-----------------+-----------+---------+-------+---------------------+
| 7  | Comment 2.1.1.1 | 0         | 2.3.6.7 | 3     | -0.2.-0.3.-0.6.-0.7 |
+----+-----------------+-----------+---------+-------+---------------------+
| 4  | Comment 2.2     | 20        | 2.4     | 1     | -0.2.-20.4          |
+----+-----------------+-----------+---------+-------+---------------------+
| 5  | Comment 3       | 5         | 5       | 0     | -5.5                |
+----+-----------------+-----------+---------+-------+---------------------+

我需要的结果

+----+-----------------+-----------+---------+-------+---------------------+
| id | content         | voteCount | path    | level | sortable            |
+----+-----------------+-----------+---------+-------+---------------------+
| 5  | Comment 3       | 5         | 5       | 0     | -5.5                |
+----+-----------------+-----------+---------+-------+---------------------+
| 1  | Comment 1       | 0         | 1       | 0     | -0.1                |
+----+-----------------+-----------+---------+-------+---------------------+
| 2  | Comment 2       | 0         | 2       | 0     | -0.2                |
+----+-----------------+-----------+---------+-------+---------------------+
| 4  | Comment 2.2     | 20        | 2.4     | 1     | -0.2.-20.4          |
+----+-----------------+-----------+---------+-------+---------------------+
| 3  | Comment 2.1     | 0         | 2.3     | 1     | -0.2.-0.3           |
+----+-----------------+-----------+---------+-------+---------------------+
| 6  | Comment 2.1.1   | 0         | 2.3.6   | 2     | -0.2.-0.3.-0.6      |
+----+-----------------+-----------+---------+-------+---------------------+
| 7  | Comment 2.1.1.1 | 0         | 2.3.6.7 | 3     | -0.2.-0.3.-0.6.-0.7 |
+----+-----------------+-----------+---------+-------+---------------------+