SQL:按(可选)关系对结果进行排序

发布于 2024-11-17 15:07:33 字数 1010 浏览 2 评论 0原文

我有两个表 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

深爱不及久伴 2024-11-24 15:07:33

假设父母总是在孩子之前,这应该可行。

SELECT p.id, ..., creationdate
    FROM Post p
        LEFT JOIN Post_Has_Post php
            ON p.id = php.child_id
    ORDER BY COALESCE(php.parent_id, p.id),
             creationdate

Assuming the parent always comes before the child, this should work.

SELECT p.id, ..., creationdate
    FROM Post p
        LEFT JOIN Post_Has_Post php
            ON p.id = php.child_id
    ORDER BY COALESCE(php.parent_id, p.id),
             creationdate
手长情犹 2024-11-24 15:07:33

您不应该拥有表 Post_Has_Parent。  而是看看
将列添加到“parent_id”的 Post 表并使用
post_id 和parent_id 之间的关系以及自连接。  何时
有人回复帖子,只需将父母的帖子 ID 作为
新帖子的parent_id。  这将允许您存储
一张表中的关系。

根据您可以考虑使用的 DBMS:

Oracle:Connect_be

select seq_num, post_text, parent_id, SYS_CONNECT_BY_PATH(seq_num,
'/') AS PATH, level
 from post
start with seq_num = 1
CONNECT BY NOCYCLE PRIOR seq_num = parent_id;

SQL Server:公用表表达式

;with posts as
(

      select seq_num, post_text, parent_id, 0 AS generation,
              CAST(seq_num as varchar(50)) as path
              from recurs_test
              where parent_id is NULL
      UNION ALL
      select e.seq_num, e.post_text, e.parent_id, generation + 1,
              CAST(rtrim(p.path) + '/' +  CAST(e.seq_num as varchar(5)) as
varchar(50)) as path
              from recurs_test e
              inner join posts p
              on e.parent_id = p.seq_num

)

select seq_num, name, parent_id, dir, generation from
managers order by dir;

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

select seq_num, post_text, parent_id, SYS_CONNECT_BY_PATH(seq_num,
'/') AS PATH, level
 from post
start with seq_num = 1
CONNECT BY NOCYCLE PRIOR seq_num = parent_id;

SQL Server: Common Table Expressions

;with posts as
(

      select seq_num, post_text, parent_id, 0 AS generation,
              CAST(seq_num as varchar(50)) as path
              from recurs_test
              where parent_id is NULL
      UNION ALL
      select e.seq_num, e.post_text, e.parent_id, generation + 1,
              CAST(rtrim(p.path) + '/' +  CAST(e.seq_num as varchar(5)) as
varchar(50)) as path
              from recurs_test e
              inner join posts p
              on e.parent_id = p.seq_num

)

select seq_num, name, parent_id, dir, generation from
managers order by dir;

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!! )

蝶舞 2024-11-24 15:07:33

您不会将日期放在一列中,但恕我直言,无论如何,这有点令人困惑。您可以选择在显示数据的任何 UI 中显示子线程日期或父线程日期:

SELECT Parent.ID, Parent.CreationDate ParentCreationDate,
       Child.CreationDate ChildCreationDate
FROM Post Parent LEFT OUTER JOIN
     Post_Has_Post PHP ON Parent.ID = PHP.Parent_ID LEFT OUTER JOIN
     Post Child ON PHP.Child_ID = Child.ID
ORDER BY Parent.CreationDate, Child.CreationDate;

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:

SELECT Parent.ID, Parent.CreationDate ParentCreationDate,
       Child.CreationDate ChildCreationDate
FROM Post Parent LEFT OUTER JOIN
     Post_Has_Post PHP ON Parent.ID = PHP.Parent_ID LEFT OUTER JOIN
     Post Child ON PHP.Child_ID = Child.ID
ORDER BY Parent.CreationDate, Child.CreationDate;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文