本文介绍了如何保持孩子出现在父母面前的秩序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
replyid的预期顺序：55、57、58、59、60、56-这样，整个第一个父级答复及其所有子项就会出现在第二个父级答复之前
以下SQL查询返回错误的顺序结果
具有递归t（replyid，replypid，深度，路径，reply，replied，reply_userid）AS（
（选择replyid，replypid，0，array [replyid]，回复，已答复，replies.userid，u.displayname，u.email_address，
（从reply_revs中选择COUNT（*），其中replyid = replies.replyid）AS Reply_revs
FROM答复
左外加入用户u ON（replies.userid = u.userid）
其中的replypid为NULL AND postid = 31 ORDER BY已答复）
UNION ALL
（SELECT r.replyid，r.replypid，t.depth + 1，t.path || r.replypid，r.reply，r.replied，r.userid，u.displayname，u.email_address，
（从reply_revs中的SELECT COUNT（*），其中replyid = r.replyid）
从回复r
JOIN t ON（r.replypid = t.replyid ）
左外加入用户u ON（r.userid = u.userid）
ORDER BY答复）
）SELECT * FROM t
Replyid深度路径回复回复
55 NULL 0 {55}第一父答复2011-02-13 11：40：48.072148-05
56 NULL 0 {56}第二父答复2011-02-13 11：41：00.610033- 05
57 55 1 {55,55}第一个孩子对第一个父母的答复2011-02-13 11：41：26.541024-05
58 55 1 {55,55}第二个孩子对第一个父母的答复2011 -02-13 11：41：39.485405-05
59 55 1 {55,55}第3个孩子对第1个父母的答复2011-02-13 11：41：51.35482-05
60 59 2 {55 ，55,59}第一胎的第一胎到第三胎2011-02-13 11：42：14.866852-05
但是，仅将 ORDER BY path添加到最终fixe s，但这仅适用于升序
与递归t（回复，回复pid，深度，路径，回复，回复，reply_userid） AS（
（SELECT replyid，replypid，0，array [replyid]，reply，reply，replies.userid，u.displayname，u.email_address，
（SELECT COUNT（*）从reply_revs在哪里replyid = replies.replyid）AS Reply_revs
FROM回复
左外加入用户u ON（replies.userid = u.userid）
replypid为NULL并且postid = 31 ORDER BY已答复）
UNION ALL
（选择r.replyid，r.replypid，t.depth + 1，t.path || r.replypid，r.reply，r.replied，r.userid，u.displayname，u.email_address，
（从reply_revs中选择COUNT（*），其中Replyid = r.replyid）
FROM答复r
JOIN t ON（r.replypid = t.replyid）
左外加入用户u ON（r.userid = u.userid）
ORDER BY答复）
）SELECT * FROM t ORDER BY路径
Replyid Replypid深度路径答复已答复
55空0 {55}第一亲答复2011-02-13 11：40：48.072148-05
57 55 1 {55,55}第一个孩子对第一个父母的答复2011-02-13 11：41：26.541024-05
58 55 1 {55,55}第二个孩子对第一个父母的答复2011-02-13 11:41 ：39.485405-05
59 55 1 {55,55}第3个孩子对第1个父母的答复2011-02-13 11：41：51.35482-05
60 59 2 {55,55,59}第一个孩子对第一父母的第三个孩子的答复2011-02-13 11：42：14.866 852-05
56空0 {56}第二个家长答复2011-02-13 11：41：00.610033-05
所以让我们现在尝试通过添加 ORDER BY path DESC来降低顺序，结果是：
replyid Replypid深度路径回复已答复
56 NULL 0 {56}第二亲答复2011-02-13 11：41：00.610033-05
60 59 2 {55,55,59}第一个孩子到第一个孩子的第三个孩子父级回复2011-02-13 11：42：14.866852-05
57 55 1 {55,55}第一个孩子对第一个父级回复2011-02-13 11：41：26.541024-05
58 55 1 {55,55}第2个孩子对第1个父母的答复2011-02-13 11：41：39.485405-05
59 55 1 {55,55}第3个孩子对第1个父母的答复2011-02-13 11:41 ：51.35482-05
55 NULL 0 {55}第一位家长回复2011-02-13 11:40:48。 072148-05
现在看来，第一亲答复的孩子是第二亲的孩子
我的问题是：如何排序结果，以便子级或深度大于0的结果始终出现在其相应的父项之后而不是在其他父项之后？
我想要查看的结果：
replyid Replypid深度路径回复已答复
56 NULL 0 {56}第二个父级回复2011-02-13 11：41：00.610033-05
55 NULL 0 {55}第一个父级回复2011-02-13 11:40 ：48.072148-05
57 55 1 {55,55}第一个孩子对第一父母的答复2011-02-13 11：41：26.541024-05
58 55 1 {55,55}第二个孩子对第一家长回复2011-02-13 11：41：39.485405-05
59 55 1 {55,55}第三个孩子对第一个父母的答复2011-02-13 11：41：51.35482-05
60 59 2 {55,55,59}第一胎的第一个孩子到第三个孩子的答复2011-02-13 11：42：14.866852-05
感谢Freenode上#postgresql中的RhodiumToad我能够提出以下PHP和SQL查询，该查询非常有效！
if（isset（$ _ SESSION [ userid]））{
$ s_col1 =，（选择COUNT（*）个来自投票的地方，回复ID = replies.replyid和用户ID =％d）AS Reply_voted;
$ s_col2 =，（从投票中选择COUNT（*）条回复ID = r.replyid和用户ID =％d）;
} else {$ s_col1 =; $ s_col2 =; }
if（$ sort == newest）{$ s_arr1 = -extract（已回复的纪元）:: integer; $ s_arr2 = || -extract（来自r.epeped的时期）:: integer; }
else if（$ sort == oldest）{$ s_arr1 = extract（从回复的纪元）:: integer; $ s_arr2 = ||提取（来自r.replied的时期）:: integer; }
else if（$ sort == topvotes）{$ s_arr1 = -votes; $ s_arr2 = || -r.votes; }
else {$ s_arr1 =; $ s_arr2 =; }
$ sql =使用递归t（replyid，replypid，深度，路径，reply，reply，reply_userid）AS（
（SELECT replyid，replypid，0，array [$ s_arr1， Replyid]，reply.reply.userid，u.displayname，u.email_address，
（从reply_revs选择COUNT（*），其中replyid = replies.replyid）作为Reply_revs，
（选择COUNT（* ）FROM投票Where回复ID = replies.replyid）AS Reply_votes
$ s_col1
FROM回复
左外加入用户u ON（replies.userid = u.userid）
WHERE Replypid是NULL AND postid =％d）
联盟所有
（选择r.replyid，r.replypid，t.depth + 1，t.path $ s_arr2 || r.replyid，r.reply，r。回复，r.userid，u.displayname，u.email_address，
（从reply_revs WHERE Replyid = r.replyid中选择COUNT（*））作为reply_revs，
（从投票WHEREreplyid中选择SELECT（（*））个） = r.replyid）AS Reply_votes
$ s_col2
FROM回复r
JOIN t ON（r.replypid = t.replyid）
左外加入用户u ON（r.userid = u.userid））
）SELECT * FROM t ORDER BY path;
解决方案
在最后一个查询中，您确实有两种。
在看到了这个之后，我相信您可以用这样的解决方案。
p>
当深度= 0
时按情况
排序，然后按路径
/ *
秘密该函数始终返回
个正确数字，而不管排序是否递增。
* /
else XXX_function（’DESC’，path）
end desc;
我相信逻辑是合理的，但是您必须弄清楚如何用降序替换数字事情将倒置。 （也许可以反转数组的位置）
Expected order by replyid: 55, 57, 58, 59, 60, 56 -- So that the entire 1st parent reply and all its children appear BEFORE the 2nd parent replyThe following SQL query returns the wrong order of results
WITH RECURSIVE t(replyid, replypid, depth, path, reply, replied, reply_userid) AS (
(SELECT replyid, replypid, 0, array[replyid], reply, replied, replies.userid, u.displayname, u.email_address,
(SELECT COUNT(*) FROM reply_revs WHERE replyid = replies.replyid) AS reply_revs
FROM replies
LEFT OUTER JOIN users u ON (replies.userid = u.userid)
WHERE replypid is NULL AND postid = 31 ORDER BY replied)
UNION ALL
(SELECT r.replyid, r.replypid, t.depth+1, t.path || r.replypid, r.reply, r.replied, r.userid, u.displayname, u.email_address,
(SELECT COUNT(*) FROM reply_revs WHERE replyid = r.replyid)
FROM replies r
JOIN t ON (r.replypid = t.replyid)
LEFT OUTER JOIN users u ON (r.userid = u.userid)
ORDER BY replied)
) SELECT * FROM t
replyid replypid depth path reply replied
55 NULL 0 {55} 1st parent reply 2011-02-13 11:40:48.072148-05
56 NULL 0 {56} 2nd parent reply 2011-02-13 11:41:00.610033-05
57 55 1 {55,55} 1st child to 1st parent reply 2011-02-13 11:41:26.541024-05
58 55 1 {55,55} 2nd child to 1st parent reply 2011-02-13 11:41:39.485405-05
59 55 1 {55,55} 3rd child to 1st parent reply 2011-02-13 11:41:51.35482-05
60 59 2 {55,55,59} 1st child to 3rd child of 1st parent reply 2011-02-13 11:42:14.866852-05
However, merely tacking on " ORDER BY path" to the end fixes this, but ONLY for ASCENDING order
WITH RECURSIVE t(replyid, replypid, depth, path, reply, replied, reply_userid) AS (
(SELECT replyid, replypid, 0, array[replyid], reply, replied, replies.userid, u.displayname, u.email_address,
(SELECT COUNT(*) FROM reply_revs WHERE replyid = replies.replyid) AS reply_revs
FROM replies
LEFT OUTER JOIN users u ON (replies.userid = u.userid)
WHERE replypid is NULL AND postid = 31 ORDER BY replied)
UNION ALL
(SELECT r.replyid, r.replypid, t.depth+1, t.path || r.replypid, r.reply, r.replied, r.userid, u.displayname, u.email_address,
(SELECT COUNT(*) FROM reply_revs WHERE replyid = r.replyid)
FROM replies r
JOIN t ON (r.replypid = t.replyid)
LEFT OUTER JOIN users u ON (r.userid = u.userid)
ORDER BY replied)
) SELECT * FROM t ORDER BY path
replyid replypid depth path reply replied
55 NULL 0 {55} 1st parent reply 2011-02-13 11:40:48.072148-05
57 55 1 {55,55} 1st child to 1st parent reply 2011-02-13 11:41:26.541024-05
58 55 1 {55,55} 2nd child to 1st parent reply 2011-02-13 11:41:39.485405-05
59 55 1 {55,55} 3rd child to 1st parent reply 2011-02-13 11:41:51.35482-05
60 59 2 {55,55,59} 1st child to 3rd child of 1st parent reply 2011-02-13 11:42:14.866852-05
56 NULL 0 {56} 2nd parent reply 2011-02-13 11:41:00.610033-05
So let's try DESCENDING now by instead appending " ORDER BY path DESC" Results are:
replyid replypid depth path reply replied
56 NULL 0 {56} 2nd parent reply 2011-02-13 11:41:00.610033-05
60 59 2 {55,55,59} 1st child to 3rd child of 1st parent reply 2011-02-13 11:42:14.866852-05
57 55 1 {55,55} 1st child to 1st parent reply 2011-02-13 11:41:26.541024-05
58 55 1 {55,55} 2nd child to 1st parent reply 2011-02-13 11:41:39.485405-05
59 55 1 {55,55} 3rd child to 1st parent reply 2011-02-13 11:41:51.35482-05
55 NULL 0 {55} 1st parent reply 2011-02-13 11:40:48.072148-05
Now it appears as if the children to the 1st parent reply are children of the 2nd parent reply.
My question is: How can I order the results so that the children or results with depth > 0 ALWAYS appear after their corresponding parents and not after other parent items?
The results I'd like to see:
replyid replypid depth path reply replied
56 NULL 0 {56} 2nd parent reply 2011-02-13 11:41:00.610033-05
55 NULL 0 {55} 1st parent reply 2011-02-13 11:40:48.072148-05
57 55 1 {55,55} 1st child to 1st parent reply 2011-02-13 11:41:26.541024-05
58 55 1 {55,55} 2nd child to 1st parent reply 2011-02-13 11:41:39.485405-05
59 55 1 {55,55} 3rd child to 1st parent reply 2011-02-13 11:41:51.35482-05
60 59 2 {55,55,59} 1st child to 3rd child of 1st parent reply 2011-02-13 11:42:14.866852-05
Thanks to RhodiumToad in #postgresql on Freenode I was able to come up with the following PHP and SQL query which works AMAZINGLY!
if (isset($_SESSION["userid"])) {
$s_col1 = ", (SELECT COUNT(*) FROM votes WHERE replyid = replies.replyid AND userid = %d) AS reply_voted";
$s_col2 = ", (SELECT COUNT(*) FROM votes WHERE replyid = r.replyid AND userid = %d)";
} else { $s_col1 = ""; $s_col2 = ""; }
if ($sort == "newest") { $s_arr1 = "-extract(epoch from replied)::integer"; $s_arr2 = " || -extract(epoch from r.replied)::integer"; }
else if ($sort == "oldest") { $s_arr1 = "extract(epoch from replied)::integer"; $s_arr2 = " || extract(epoch from r.replied)::integer"; }
else if ($sort == "topvotes") { $s_arr1 = "-votes"; $s_arr2 = " || -r.votes"; }
else { $s_arr1 = ""; $s_arr2 = ""; }
$sql = "WITH RECURSIVE t(replyid, replypid, depth, path, reply, replied, reply_userid) AS (
(SELECT replyid, replypid, 0, array[$s_arr1,replyid], reply, replied, replies.userid, u.displayname, u.email_address,
(SELECT COUNT(*) FROM reply_revs WHERE replyid = replies.replyid) AS reply_revs,
(SELECT COUNT(*) FROM votes WHERE replyid = replies.replyid) AS reply_votes
$s_col1
FROM replies
LEFT OUTER JOIN users u ON (replies.userid = u.userid)
WHERE replypid is NULL AND postid = %d)
UNION ALL
(SELECT r.replyid, r.replypid, t.depth+1, t.path$s_arr2 || r.replyid, r.reply, r.replied, r.userid, u.displayname, u.email_address,
(SELECT COUNT(*) FROM reply_revs WHERE replyid = r.replyid) AS reply_revs,
(SELECT COUNT(*) FROM votes WHERE replyid = r.replyid) AS reply_votes
$s_col2
FROM replies r
JOIN t ON (r.replypid = t.replyid)
LEFT OUTER JOIN users u ON (r.userid = u.userid))
) SELECT * FROM t ORDER BY path";
解决方案
You really have two sorts in one on your last query. The parents are able to sort ascending or descending but the children can only sort ascending.
After looking at this I believe you can get a solution with something like this.
order by case
when depth = 0
then path
/*
secret function that always returns the
right numbers regardless of whether or not the sort is ascending.
*/
else XXX_function('DESC', path)
end desc;
I believe the logic is sound but you have to figure out how to replace the numbers on descending sorts since things will be 'upside down'. (Maybe reverse the array positions)
这篇关于如何保持孩子出现在父母面前的秩序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！