我有一个包含用户注释的表,定义如下:
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实现分页。
在应用程序级别上这样做会更容易吗?