SQL:按(可选)关系对结果进行排序
我有两个表 Post (id, ...,creationdate)
和 Post_Has_Post(parent_id, child_id)
。
某些帖子可以是父子关系的一部分,并且在Post_Has_Post
表中具有相应的条目。
我想做的是获取按创建日期排序的帖子列表。然而,其他帖子的任何子帖子都应插入到结果中其父帖子之后。 当然,现在很容易按创建日期排序,但我对第二个排序条件没有想法。有办法做到这一点吗?
Post Post_Has_Post
+------+-------+--------------+ +-----------+----------+
| id | ... | creationdate | | parent_id | child_id |
+------+-------+--------------+ +-----------+----------+
| 1 | ... | 2010-11-01 | | 1 | 3 |
| 2 | ... | 2010-11-02 | +-----------+----------+
| 3 | ... | 2010-11-03 |
+------+-------+--------------+
我需要这样排序的结果:
Post
+------+-------+--------------+
| id | ... | creationdate |
+------+-------+--------------+
| 1 | ... | 2010-11-01 |
| 3 | ... | 2010-11-03 |
| 2 | ... | 2010-11-02 |
+------+-------+--------------+
有没有办法通过排序解决这个问题?
I have two tables Post (id, ..., creationdate)
and Post_Has_Post(parent_id, child_id)
.
Some Posts can be part of a parent<->child relation and have a corresponding entry in the Post_Has_Post
-Table.
What I'm trying to do is getting a list of posts ordered by their creationdate. Any children of other posts however should be inserted after their parent in the result.
Now of course it's easy to order by creationdate, but i'm out of ideas for the second sorting condition. Is there a way to do that at all?
Post Post_Has_Post
+------+-------+--------------+ +-----------+----------+
| id | ... | creationdate | | parent_id | child_id |
+------+-------+--------------+ +-----------+----------+
| 1 | ... | 2010-11-01 | | 1 | 3 |
| 2 | ... | 2010-11-02 | +-----------+----------+
| 3 | ... | 2010-11-03 |
+------+-------+--------------+
I need a result sorted like this:
Post
+------+-------+--------------+
| id | ... | creationdate |
+------+-------+--------------+
| 1 | ... | 2010-11-01 |
| 3 | ... | 2010-11-03 |
| 2 | ... | 2010-11-02 |
+------+-------+--------------+
Is there a way to solve this through sorting?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
假设父母总是在孩子之前,这应该可行。
Assuming the parent always comes before the child, this should work.
您不应该拥有表 Post_Has_Parent。 而是看看
将列添加到“parent_id”的 Post 表并使用
post_id 和parent_id 之间的关系以及自连接。 何时
有人回复帖子,只需将父母的帖子 ID 作为
新帖子的parent_id。 这将允许您存储
一张表中的关系。
根据您可以考虑使用的 DBMS:
Oracle:Connect_be
SQL Server:公用表表达式
MYSQL:您将不得不考虑使用某种树
遍历算法并进行深度优先搜索。 都很漂亮
复杂,通常涉及存储一些东西(路径,左和
正确的值等)在数据库中。 原因是MYSQL没有
允许任何递归选择语句(如果我错了请纠正我,所以我
可以重做我写的一些代码!! )
You should not have the table Post_Has_Parent. Instead look into
adding a column to the Post table of "parent_id" and using a
relationship between post_id and parent_id and a self join. When
someone posts a reply to a post, just put the parents post id as the
parent_id of the new post. This will allow you to store the
relationship in one table.
Depending on the DBMS you can look into using:
Oracle: Connect_be
SQL Server: Common Table Expressions
MYSQL: You are going to have to look into using some sort of tree
traversal algorithm and do a depth first search. They are all pretty
complex and usually involve storing something ( the path, a left and
right value, etc ) in the database. The reason is that MYSQL does not
allow any recursive select statements ( correct me if I am wrong so I
can redo some code that I have written!! )
您不会将日期放在一列中,但恕我直言,无论如何,这有点令人困惑。您可以选择在显示数据的任何 UI 中显示子线程日期或父线程日期:
You won't have the dates in a single column, but IMHO that's a little confusing anyway. You could choose to show the child thread date or parent thread date in whatever UI is presenting the data: