SQL: упорядочить результаты по (необязательному) отношению

Question

У меня две таблицы 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   |
+------+-------+--------------+

Есть ли способ решить эту проблему с помощью сортировки?

Answer 1

Предполагая, что родитель всегда предшествует ребенку, это должно сработать.

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

Answer 2

У вас не должно быть таблицы Post_Has_Parent. Вместо этого обратите внимание на добавление столбца в таблицу Post таблицы "parent_id" и использование отношения между post_id и parent_id и самостоятельного соединения. Когда кто-то публикует ответ на сообщение, просто укажите родительский идентификатор сообщения в качестве parent_id нового сообщения. Это позволит вам хранить отношения в одной таблице.

В зависимости от СУБД вы можете использовать:

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 не допускает никаких рекурсивных операторов выбора (исправьте меня, если я ошибаюсь, чтобы я мог повторить некоторый код, который я написал!!)

Answer 3

У вас не будет дат в одном столбце, но, в любом случае, это немного сбивает с толку. Вы можете выбрать показ даты дочернего потока или даты родительского потока в любом пользовательском интерфейсе, который представляет данные:

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;