Упорядочить иерархию дерева sql - программирование
Подтвердить что ты не робот

Упорядочить иерархию дерева sql

Каков наилучший способ сортировки таблицы следующим образом:

CREATE TABLE category(
    id INT(10),
    parent_id INT(10),
    name VARCHAR(50)
);

INSERT INTO category (id, parent_id, name) VALUES
(1, 0, 'pizza'),        --node 1
(2, 0, 'burger'),       --node 2
(3, 0, 'coffee'),       --node 3
(4, 1, 'piperoni'),     --node 1.1
(5, 1, 'cheese'),       --node 1.2
(6, 1, 'vegetariana'),  --node 1.3
(7, 5, 'extra cheese'); --node 1.2.1

Чтобы отсортировать его по иерархии id или имя:
'pizza'//node 1
'piperoni'//node 1.1
'сыр'//node 1.2
"дополнительный сыр" // node 1.2.1
'vegetariana'//node 1.3
'burger'//node 2
'coffee'//node 3

РЕДАКТИРОВАТЬ: номер в конце name - лучше визуализировать strucutre, это не для сортировки.

EDIT 2:, как уже упоминалось несколько раз... номер в конце name "cheese 1.2" был предназначен только для целей визуализации, НЕ для сортировки, Я переместил их в качестве комментариев, слишком много людей запутались, извините.

4b9b3361

ОТВЕТЫ

Ответ 1

Добавив столбец пути и триггер, это можно сделать довольно легко.

Сначала добавьте столбец varchar, который будет содержать путь от корня до node:

ALTER TABLE category ADD path VARCHAR(50) NULL;

Затем добавьте триггер, который вычисляет путь вставки:

(просто конкатцирует новый id с контуром родителя)

CREATE TRIGGER set_path BEFORE INSERT ON category
  FOR EACH ROW SET NEW.path = 
  CONCAT(IFNULL((select path from category where id = NEW.parent_id), '0'), '.', New.id);

Затем просто выберите порядок по пути:

SELECT name, path FROM category ORDER BY path;

Результат:

pizza         0.1
piperoni      0.1.4
cheese        0.1.5
extra cheese  0.1.5.7
vegetariana   0.1.6
burger        0.2
coffee        0.3

См. fiddle.

Таким образом, стоимость обслуживания также минимальна. Поле пути скрыто при вставке и вычисляется через триггер. Удаление node не имеет накладных расходов, так как все дочерние элементы node также удаляются. Единственная проблема заключается в обновлении parent_id из node; Ну, не делай этого!:)

Ответ 2

Вложенные наборы деревьев в сочетании с столбцом level - очень хороший метод для чтения и сортировки древовидных структур. Легко выбрать поддерево, ограничить результат до определенного уровня и сортировать в одном запросе. Но затраты на вставку и удаление желаний относительно высоки, поэтому вы должны использовать их, если чаще будете запрашивать свои данные, а затем пишете их и где важна производительность чтения. (для 50-100 время для удаления, вставки или перемещения элементов не должно быть проблемой, даже с 1000 это не должно быть проблематично).

В каждой записи вы сохраняете ее level и значение для left и right, в приведенном ниже примере это: (left, right, level), если вы хотите выбрать только 1.2 с его потомками, которые вы сделали бы:

 SELECT * FROM table WHERE left >=7 AND right <=16

если вы хотите выбрать только детей, тогда

 SELECT * FROM table WHERE left >=7 AND right <=16 AND level=2

если вы хотите сортировать, вы могли бы сделать

 SELECT * FROM table WHERE left >=7 AND right <=16 ORDER BY left

Сортировка по другим полям при сохранении группировки иерархии может быть проблематичной, в зависимости от того, как вы хотите сортировать.

                               1 (0,17,0)
                                   |
                                   |
                   +---------------+---------------------------------------+
                   |                                                       |
              1.1 (1,6,1)                                            1.2 (7,16,1)
                   |                                                       |
      +------------+-------+                  +-------------------+--------+----------------+
      |                    |                  |                   |                         |
  1.1.1 (2,3,2)      1.1.2 (4,5,2)      1.2.1 (8,9,2)       1.2.2 (10,13,2)         1.2.2 (14,15,2)
                                                                  |
                                                                  |
                                                                  |
                                                            1.2.2.1 (11,12,3)

Таблица закрытия (для завершения, но я бы не рекомендовал для вашего случая использования). Он сохраняет все пути в дереве, и поэтому требуемое пространство для хранения иерархии будет расти очень быстро, если у вас много уровней.

Перечисление пути там вы сохраняете путь каждого элемента с записью /0/, /0/1/ путь запроса легко там, но для сортировки это не так гибко.

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

Ответ 3

если есть только 3 уровня вложенности, вы можете сделать что-то вроде этого

SELECT c1.name FROM category as c1 LEFT JOIN category as c2
   ON c1.parent_id = c2.id OR (c1.parent_id = 0 AND c1.id = c2.id) 
   ORDER BY c2.parent_id, c2.id, c1.id;

Если у вас больше уровней вложенности, это будет более сложным

для большего уровня вложенности вы можете написать функцию

delimiter ~
DROP FUNCTION getPriority~

CREATE FUNCTION getPriority (inID INT) RETURNS VARCHAR(255) DETERMINISTIC
begin
  DECLARE gParentID INT DEFAULT 0;
  DECLARE gPriority VARCHAR(255) DEFAULT '';
  SET gPriority = inID;
  SELECT parent_id INTO gParentID FROM category WHERE ID = inID;
  WHILE gParentID > 0 DO
    SET gPriority = CONCAT(gParentID, '.', gPriority);
    SELECT parent_id INTO gParentID FROM category WHERE ID = gParentID;
  END WHILE;
  RETURN gPriority;
end~

delimiter ;

поэтому я теперь на

SELECT * FROM category ORDER BY getPriority(ID);

У меня есть

+------+-----------+--------------------+
| ID   | parent_id | name               |
+------+-----------+--------------------+
|    1 |         0 | pizza 1            |
|    4 |         1 | piperoni 1.1       |
|    5 |         1 | cheese 1.2         |
|    7 |         5 | extra cheese 1.2.1 |
|    6 |         1 | vegetariana 1.3    |
|    2 |         0 | burger 2           |
|    3 |         0 | coffee 3           |
+------+-----------+--------------------+

Ответ 4

Один из способов - иметь отдельное строковое поле для хранения полного пути любого node. Вы должны поддерживать это поле в каждой операции вставки/обновления/удаления.

У вас может быть значение поля, как показано ниже

CREATE TABLE category(
    id INT(10),
    parent_id INT(10),
    name VARCHAR(50),
    path VARCHAR(255)
);

INSERT INTO category (id, parent_id, name, path) VALUES
(1, 0, 'pizza 1','|1|'),
(2, 0, 'burger 2','|2|'),
(3, 0, 'coffee 3','|3|'),
(4, 1, 'piperoni 1.1','|1||4|'),
(5, 1, 'cheese 1.2','|1||5|'),
(6, 1, 'vegetariana 1.3','|1||6|'),
(7, 5, 'extra cheese 1.2.1','|1||5||1|');

Вам нужно заказать по полю пути, чтобы иметь дерево в правильном порядке сортировки.

SELECT * FROM `category` ORDER BY `path`;

См. SqlFiddle Demo

Таким образом, вам не нужна рекурсия на языке программирования для печати всего дерева в правильном порядке сортировки.

Note:

Этот пример будет работать, только если у вас есть max ID до 9, так как | 1 || 11 | будет раньше, чем | 1 || 2 |

Чтобы устранить эту проблему, вам нужно выполнить добавление для построения строки на основе максимального значения поля идентификатора для вашего приложения, например, ниже пример с максимальным ожидаемым значением - 999 (3 цифры)

|001||002|

В соответствии с моим опытом это решение должно быть хорошим для обработки дерева с глубиной до уровня 7-8.

Для другого метода: Нажмите здесь

Ответ 5

Я думаю, что все по-архитектурному решению. Если ваша цель действительно представлена ​​вашим примером, как в 3-х уровнях с виртуальным верхним уровнем 0 id, этого должно быть достаточно.

SELECT *
     , id AS SORT_KEY
  FROM category a
 WHERE parent_id = 0
UNION ALL
SELECT a.*
     , CONCAT(b.id, '.', a.id) AS SORT_KEY
  FROM category a
     , category b
 WHERE b.parent_id = 0
   and b.id = a.parent_id
UNION ALL
SELECT a.*
     , CONCAT(c.id,'.', b.id,'.', a.id) AS SORT_KEY
  FROM category a
     , category b
     , category c
 WHERE c.parent_id = 0
   and b.id = a.parent_id
   AND c.id = b.parent_id
ORDER BY sort_key

Ответ 6

Sql

WITH CTE_Category
    AS
    (
      SELECT id, parent_id, name
      , RIGHT(name,CHARINDEX(' ',REVERSE(RTRIM(name)))-1) as ordername
      FROM Category 
    )

    SELECT id, parent_id, name FROM CTE_Category ORDER BY ordername

MySql

SELECT id, parent_id, name
FROM Category ORDER BY SUBSTRING_INDEX(name,' ',-1)

Ответ 7

SELECT * FROM category ORDER BY name, parent_id ASC

Ответ 8

Попробуйте ORDER BY name , id в конце вашего SQL-запроса.

Это будет сортировать по имени и использовать идентификатор для определения любых связей.