mysql 嵌套排序_mysql - MySql-嵌套注释的层次结构排序 - 堆栈内存溢出

我有一个包含用户注释的表，定义如下：

CREATE TABLE UserComments (

`ID` INT NOT NULL,

`Content` TEXT,

`Rank` TEXT,

`Number`INT NOT NULL,

`Parent` INT,

PRIMARY KEY (`ID`),

FOREIGN KEY (`Parent`) REFERENCES UserComments(`ID`)

);

INSERT INTO `UserComments` (`ID`, `Content`, `Rank`, `Number`) VALUES ('1', '

First root comment

', '1', '1');

INSERT INTO `UserComments` (`ID`, `Content`, `Rank`, `Number`) VALUES ('2', '

Second root comment

', '2', '2');

INSERT INTO `UserComments` (`ID`, `Content`, `Rank`, `Number`) VALUES ('3', '

Third root comment

', '3', '3');

INSERT INTO `UserComments` (`ID`, `Content`, `Rank`, `Number`, `Parent`) VALUES ('5', '

First reply to 3rd comment

', '3-5', '5', '3');

INSERT INTO `UserComments` (`ID`, `Content`, `Rank`, `Number`, `Parent`) VALUES ('6', '

First reply to first reply of 3rd comment

', '3-5-6', '6', '5');

INSERT INTO `UserComments` (`ID`, `Content`, `Rank`, `Number`, `Parent`) VALUES ('7', '

Second reply to 3rd comment

', '3-7', '7', '3');

INSERT INTO `UserComments` (`ID`, `Content`, `Rank`, `Number`, `Parent`) VALUES ('8', '

Third reply to 3rd comment

', '3-8', '8', '3');

INSERT INTO `UserComments` (`ID`, `Content`, `Rank`, `Number`) VALUES ('4', '

Fourth root comment

', '4', '4');

我显示表格层次结构的方法是通过用路径对我的注释进行排名，如本例所示

ID Content Rank Parent

1 'First root comment', '1' NULL

2 'Second root comment', '2' NULL

3 'Third root comment', '3' NULL

5 'First reply to 3rd comment', '3-5' '3'

6 'First reply to first reply of 3rd comment', '3-5-6' '5'

7 'Second reply to 3rd comment', '3-7' '3'

8 'Third reply to 3rd comment', '3-8' '3'

4 'Fourth root comment', '4' NULL

SELECT * FROM UserComments ORDER BY `Rank`

如您所见，这对于显示从最早到最新的注释非常有用 。 最新的根注释将始终显示在表的末尾。 当我想做相反的事情并将我的基本注释从“ 最新”到“最旧”排序时，就会出现问题。 尝试反向订购时，显示以下树。 评论的顺序当然是不正确的。 孩子们被堆放在父母之上

ID Content Rank Parent

4 'Fourth root comment', '4' NULL

8 'Third reply to 3rd comment', '3-8' '3'

7 'Second reply to 3rd comment', '3-7' '3'

6 'First reply to first reply of 3rd comment', '3-5-6' '5'

5 'First reply to 3rd comment', '3-5' '3'

3 'Third root comment', '3' NULL

2 'Second root comment', '2' NULL

1 'First root comment', '1' NULL

ID Content Rank Parent

4 'Fourth root comment', '4' NULL

3 'Third root comment', '3' NULL // ORDERED CORRECTLY

5 'First reply to 3rd comment', '3-5' '3'

6 'First reply to first reply of 3rd comment', '3-5-6' '5'

7 'Second reply to 3rd comment', '3-7' '3'

8 'Third reply to 3rd comment', '3-8' '3'

2 'Second root comment', '2' NULL

1 'First root comment', '1' NULL

我的问题是，我该如何解决这个问题？ 有什么方法可以构造一个查询，让我以这种方式进行排序？ 我想以这种方式订购它的原因是，这样我就可以通过LIMIT实现分页。

在应用程序级别上这样做会更容易吗？