postgresql - Postgresql 按列和时间戳排序
问题描述
我有一张桌子，上面有评论和回复。我正在使用以下查询来获取 parentid，并从那里获取应该与它们相关的回复 (replyid)。
我面临的问题是，当我获取数据时，我可以按列（replyid）组织它，但我不能按时间戳组织它们。或者似乎 postgresql 完全忽略了二阶。
parentid | replyid | commentowner | username | commentbody | postcreation
------------------+------------------+---------------+----------+-------------------------------------------------------------------------------------------------------+----------------------------
gLKysUwsL5CVHRIR | 3X77KW5t9X43Psfp | bob@none.com | Bobert | Some Lame Comment.... | 2018-05-08 13:21:43.049563
yt1SLuFzRz1Nlri2 | BG7mm4cj7Q3Ujffs | bob@none.com | Bobert | another parent comment | 2018-05-08 13:22:05.677257
| 3X77KW5t9X43Psfp | bob@none.com | Bobert | latest reply | 2018-05-08 13:22:37.535039
| 3X77KW5t9X43Psfp | bob@none.com | Bobert | no I am the latest | 2018-05-08 13:22:52.868424
| BG7mm4cj7Q3Ujffs | bob@none.com | Bobert | I am a response to to another parent | 2018-05-08 13:23:42.167327
| 3X77KW5t9X43Psfp | mike@none.com | Mikey | I am a response to to another parent | 2018-05-09 08:27:22.733211
| 3X77KW5t9X43Psfp | mike@none.com | Mikey | Mike sent this!! | 2018-05-09 08:27:50.1
和用户表：
username | socialkey | emailaddress | bannerpic | avatarpic
----------+------------------------------------------------------------------+---------------+------------------------------------------------------------------------------+------------------------------------------------------------------------------------
Bobert | 9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a | bob@none.com | https://craftedin.co/static/images/user/headers/59d9bce7c33561.24964635.jpg | https://craftedin.co/static/images/user/avatars/thumbs/59159be40a8c76.26091804.jpg
Mikey | 3bcd6c5f811ba06aa49c2df9504fe5416f35702b78bcdc15ecbd5137fabdca59 | mike@none.com | https://i.pinimg.com/originals/29/85/16/298516524d3ed9eb35ea9aae0f111ab3.jpg | https://images.pexels.com/photos/617278/pexels-photo-617278.jpeg
如果它有助于制作某种代码笔或其他东西，请告诉我，我会尽力做到这一点。
这是我正在运行的查询：
with parents as (select distinct replyid from comments where commentowner = (select emailaddress from users where SocialKey = ('9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a')) and parentid IS NOT NULL LIMIT 5 ), socialkeys as (select distinct socialkey,emailaddress,avatarpic from users) select distinct comments.parentid, comments.replyid,comments.username, comments.commentbody,comments.postcreation from comments left join parents on parents.replyid = comments.replyid left join socialkeys on comments.commentowner = socialkeys.emailaddress where parents.replyid = comments.replyid order by replyid, postcreation;
这是我收到的数据：
parentid | replyid | username | commentbody | postcreation
------------------+------------------+----------+-------------------------------------------------------------------------------------+----------------------------
gLKysUwsL5CVHRIR | 3X77KW5t9X43Psfp | Bobert | Some Lame Comment.... | 2018-05-08 13:21:43.049563
| 3X77KW5t9X43Psfp | Bobert | latest reply | 2018-05-08 13:22:37.535039
| 3X77KW5t9X43Psfp | Bobert | no I am the latest | 2018-05-08 13:22:52.868424
| 3X77KW5t9X43Psfp | Mikey | I am a response to to another parent | 2018-05-09 08:27:22.733211
| 3X77KW5t9X43Psfp | Mikey | Mike sent this!! | 2018-05-09 08:27:50.140229
| 3X77KW5t9X43Psfp | Bobert | bob | 2018-06-28 14:22:07.131133
| 3X77KW5t9X43Psfp | Bobert | Oh so this really does work? | 2018-06-28 14:27:02.956613
| 3X77KW5t9X43Psfp | Bobert | this is a reply | 2018-06-28 17:41:49.071165
wpdcBFUft0r597IK | 4WMFSMSTSomjdoD9 | Bobert | this is parent comment number 5 I think the database is set to limit 5... | 2018-06-28 18:15:47.534429
yt1SLuFzRz1Nlri2 | BG7mm4cj7Q3Ujffs | Bobert | another parent comment | 2018-05-08 13:22:05.677257
| BG7mm4cj7Q3Ujffs | Bobert | I am a response to to another parent | 2018-05-08 13:23:42.167327
| BG7mm4cj7Q3Ujffs | Bobert | Another lame coment reply | 2018-05-19 12:55:56.090902
pw22kSb7MdA9Nx5G | MD3jA9znknV9c3p7 | Bobert | This is a new comment from bobbert on the new interfce | 2018-06-28 18:03:30.05851
bffV59nEQRg5YtP3 | tIZYNjkyzoHhKfza | Bobert | This is comment # 6... This should show up becasue it is the newest! of the newsts! | 2018-06-28 18:16:12.823547
这是我想收到的。所有的评论都由replyid收集和排序，但我希望这些评论也与他们进入的时间保持一致。我希望这是有道理的。我试图把所有的东西都画出来。
parentid | replyid | username | commentbody | postcreation
------------------+------------------+----------+-------------------------------------------------------------------------------------+----------------------------
gLKysUwsL5CVHRIR | 3X77KW5t9X43Psfp | Bobert | Some Lame Comment.... | 2018-05-08 13:21:43.049563
| 3X77KW5t9X43Psfp | Bobert | latest reply | 2018-05-08 13:22:37.535039
| 3X77KW5t9X43Psfp | Bobert | no I am the latest | 2018-05-08 13:22:52.868424
| 3X77KW5t9X43Psfp | Mikey | I am a response to to another parent | 2018-05-09 08:27:22.733211
| 3X77KW5t9X43Psfp | Mikey | Mike sent this!! | 2018-05-09 08:27:50.140229
| 3X77KW5t9X43Psfp | Bobert | bob | 2018-06-28 14:22:07.131133
| 3X77KW5t9X43Psfp | Bobert | Oh so this really does work? | 2018-06-28 14:27:02.956613
| 3X77KW5t9X43Psfp | Bobert | this is a reply | 2018-06-28 17:41:49.071165
yt1SLuFzRz1Nlri2 | BG7mm4cj7Q3Ujffs | Bobert | another parent comment | 2018-05-08 13:22:05.677257
| BG7mm4cj7Q3Ujffs | Bobert | I am a response to to another parent | 2018-05-08 13:23:42.167327
| BG7mm4cj7Q3Ujffs | Bobert | Another lame coment reply | 2018-05-19 12:55:56.090902
pw22kSb7MdA9Nx5G | MD3jA9znknV9c3p7 | Bobert | This is a new comment from bobbert on the new interfce | 2018-06-28 18:03:30.05851
wpdcBFUft0r597IK | 4WMFSMSTSomjdoD9 | Bobert | this is parent comment number 5 I think the database is set to limit 5... | 2018-06-28 18:15:47.534429
bffV59nEQRg5YtP3 | tIZYNjkyzoHhKfza | Bobert | This is comment # 6... This should show up becasue it is the newest! of the newsts! | 2018-06-28 18:16:12.823547
我这样做完全错了吗？还是我错过了一些简单的东西？
请告诉我，谢谢。
解决方案
根据您的说法，从 2 个输出看来， parentid = wpdcBFUft0r597IK 的条目顺序错误。只有一个具有该 parentid 的条目，因此您的查询不关心您的 parentid 出现的顺序。如果您确实希望按 parentid 的 postcreation 排序，请尝试将您的查询更改为以下内容。
with parents
as (select replyid,
min(postcreation) postcreation
from comments
where commentowner = (select emailaddress
from users
where socialkey = ('9784e946c53d44c975ee91625486d758fe630f176d44863080ec689ae3cd536a'))
and parentid is not null
group by replyid limit 5),
socialkeys
as (select distinct socialkey,
emailaddress,
avatarpic
from users)
select distinct parents.postcreation,
comments.parentid,
comments.replyid,
comments.username,
comments.commentbody,
comments.postcreation
from comments
left join parents on parents.replyid = comments.replyid
left join socialkeys on comments.commentowner = socialkeys.emailaddress
where parents.replyid = comments.replyid
order by parents.postcreation,
comments.replyid,
comments.postcreation;
我将您父母的 CTE 更改为 group by，以便您可以在 order by 中使用 MIN POSTCREATION。我还用表名限定了 ORDER BY 字段，以防对 REPLYID 进行排序不明确。
-HTH