MySql: ORDER BY родитель и ребенок

Question

У меня есть таблица, как:

+------+---------+-
| id   | parent  |
+------+---------+
| 2043 |    NULL |
| 2044 |    2043 |
| 2045 |    2043 |
| 2049 |    2043 |
| 2047 |    NULL |
| 2048 |    2047 |
| 2043 |    2047 |
+------+---------+

которая показывает простую двухуровневую корреляцию "родитель-ребенок". Как я могу заказать оператор SELECT, чтобы получить порядок, как в приведенном выше списке, что означает: 1-й родитель, дочерние элементы от 1-го родителя, 2-й родительский элемент, дочерние элементы от 2-го родителя и т. Д. (Если у меня есть, я могу добавить ПОРЯДОК БЫТЬ для детей... Надеюсь). Возможно ли добавить поле сортировки?

Answer 1

В том числе сортировка детей по id:

ORDER BY COALESCE(parent, id), parent IS NOT NULL, id

Пример SQL Fiddle

Объяснение:

• COALESCE(parent, id): Сначала сортируйте (эффективно группируя вместе) идентификатор родителя.
• parent IS NOT NULL: Поместите родительский ряд поверх группы
• id: Наконец сортировать всех детей (один и тот же родитель и parent не нуль)

Answer 2

Если ваша таблица использует 0 вместо null чтобы указать запись без родителя:

id   | parent
-------------
1233 | 0
1234 | 1233
1235 | 0
1236 | 1233
1237 | 1235

использование greatest вместо coalesce и проверить значение не равно 0:

ORDER BY GREATEST(parent, id), parent != 0, id

Answer 3

Этот вопрос по-прежнему отображается как один из первых результатов поиска. Поэтому я хотел бы поделиться своим решением и надеюсь, что оно поможет большему количеству людей. Это также будет работать, если у вас есть таблица со многими уровнями родительских и дочерних отношений. Хотя это довольно медленное решение. Верхний уровень имеет родительский.

      +---------+---------+
| id      | parent  |
+---------+---------+
| 1       | NULL    |
| 2       | 1       |
| 3       | 1       |
| 4       | 2       |
+---------+---------+

В моем подходе я буду использовать процедуру, которая будет рекурсивно вызывать себя и продолжать добавлять путь к родительскому элементу запрошенного id пока он не достигнет NULL родитель.

      DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PATH`(IN `input` INT, OUT `output` VARCHAR(128))
BEGIN

  DECLARE _id INT;
  DECLARE _parent INT;
  DECLARE _path VARCHAR(128);

  SET `max_sp_recursion_depth` = 50;

  SELECT `id`, `parent`
  INTO _id, _parent
  FROM `database`.`table`
  WHERE `table`.`id` = `input`;

  IF _parent IS NULL THEN
    SET _path = _id;
  ELSE
    CALL `PATH`(_parent, _path);
    SELECT CONCAT(_path, '-', _id) INTO _path;
  END IF;

  SELECT _path INTO `output`;

END $$
DELIMITER ;

Чтобы использовать результаты в ORDER BY пункт вам понадобится FUNCTION тоже, что обертывает результаты PROCEDURE.

      DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GETPATH`(`input` INT) RETURNS VARCHAR(128)
BEGIN

  CALL `PATH`(`input`, @path);
  RETURN @path;

END $$
DELIMITER ;

Теперь мы можем использовать рекурсивный путь для сортировки порядка таблицы. На столе с 10000 строками на моей рабочей станции это занимает чуть больше секунды.

      SELECT `id`, `parent`, GETPATH(`id`) `path` FROM `database`.`table` ORDER BY `GETPATH`(`id`);

Пример вывода:

      +---------+---------+---------------+
| id      | parent  | path          |
+---------+---------+---------------+
| 1       | NULL    | 1             |
| 10      | 1       | 1-10          |
| 300     | 10      | 1-10-300      |
| 301     | 300     | 1-10-300-301  |
| 302     | 300     | 1-10-300-302  |
+---------+---------+---------------+
5 rows in set (1,39 sec)

Answer 4

Приведенное выше решение не сработало, моя таблица использовала 0 вместо NULL. Я нашел другое решение: вы создаете столбец с конкатенированным родительским идентификатором и дочерним идентификатором в своем запросе и можете отсортировать результат по нему.

SELECT CONCAT(IF(parent = 0,'',CONCAT('/',parent)),'/',id) AS gen_order
FROM table 
ORDER BY gen_order