복붙노트

[SQL] 위해 SQL 트리 계층 구조

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 또는 이름으로 계층을 정렬하려면 : '피자'// 노드 1 'piperoni'// 노드 1.1 '치즈'// 노드 1.2 '추가 치즈'// 노드 1.2.1 'vegetariana'// 노드 1.3 '햄버거'// 노드 2 '커피'// 노드 3

편집 : 이름 끝에 숫자는 정렬되지 않습니다, 더 나은 구조를 시각화하는 것입니다.

편집 2 : 언급 한 몇 번으로 ... 이름이 "치즈 1.2"의 끝에있는 숫자는하지 정렬 만 시각화 목적이었다. 의견이 너무 많은 사람들이 죄송합니다, 혼동했기 때문에 나는 그들을 움직였다.

해결법

  1. ==============================

    1.열 경로와 트리거를 추가함으로써이 상당히 용이하게 수행 할 수있다.

    열 경로와 트리거를 추가함으로써이 상당히 용이하게 수행 할 수있다.

    첫 번째 노드에 루트에서 경로를 포함하는 VARCHAR 열을 추가 :

    ALTER TABLE category ADD path VARCHAR(50) NULL;

    다음 삽입에 경로를 계산하는 트리거를 추가 :

    (단순히 부모의 경로로 새 ID를 concats)

    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

    바이올린을 참조하십시오.

    이 방법의 유지 보수 비용도 최소화됩니다. 경로 필드를 삽입 할 때 숨겨져 트리거를 통해 산출된다. 노드의 모든 자식도 제거되기 때문에 노드를 제거하면, 오버 헤드가 없습니다. 노드의 PARENT_ID를 업데이트 할 때 유일한 문제는, 음, 그러지 마! :)

  2. ==============================

    2.레벨 컬럼과 함께 중첩 된 트리 세트는 읽기, 트리 기반 구조를 정렬 정말 좋은 기술이다. 그것은 하위 트리를 선택하기 쉽고, 일정 수준의 결과를 제한하고, 한 쿼리에서 정렬 않습니다. 그런 다음 더 자주 데이터를 쿼리 성능을 읽는 것이 중요 어디를 작성하고 있다면 당신이 그것을 사용해야하므로 그러나 삽입하고 entires을 삭제하는 비용은 상대적으로 높다. (50-100, 제거 또는 삽입 요소도 (1000)는 문제가되지 않으며, 어떠한 문제가 없어야 이동하는 시간 동안).

    레벨 컬럼과 함께 중첩 된 트리 세트는 읽기, 트리 기반 구조를 정렬 정말 좋은 기술이다. 그것은 하위 트리를 선택하기 쉽고, 일정 수준의 결과를 제한하고, 한 쿼리에서 정렬 않습니다. 그런 다음 더 자주 데이터를 쿼리 성능을 읽는 것이 중요 어디를 작성하고 있다면 당신이 그것을 사용해야하므로 그러나 삽입하고 entires을 삭제하는 비용은 상대적으로 높다. (50-100, 제거 또는 삽입 요소도 (1000)는 문제가되지 않으며, 어떠한 문제가 없어야 이동하는 시간 동안).

    (왼쪽, 오른쪽, 레벨) 당신은 당신이 할 것입니다 그것의 후손과 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.당신이 그런 식으로 뭔가를 할 수 중첩의 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. ==============================

    4.나는 모두가 오버 건축가 - 보내고 솔루션을 생각합니다. 당신의 목표는 정말 0 ID의 가상 최고 레벨 3 수준으로, 귀하의 예를 나타내는 경우,이 충분합니다.

    나는 모두가 오버 건축가 - 보내고 솔루션을 생각합니다. 당신의 목표는 정말 0 ID의 가상 최고 레벨 3 수준으로, 귀하의 예를 나타내는 경우,이 충분합니다.

    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
  5. ==============================

    5.한 가지 방법은 모든 노드의 전체 경로를 저장하기위한 별도의 문자열 필드를 가지고있다. 당신은 모든 삽입 / 업데이트 / 삭제 작업에이 필드를 유지해야합니다.

    한 가지 방법은 모든 노드의 전체 경로를 저장하기위한 별도의 문자열 필드를 가지고있다. 당신은 모든 삽입 / 업데이트 / 삭제 작업에이 필드를 유지해야합니다.

    아래 같은 필드 값을 가질 수 있습니다

    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 데모

    당신이 올바른 정렬 순서에서 전체 트리를 인쇄하는 프로그래밍 언어의 재귀가 필요하지 않습니다이 방법.

    노트 :

    당신은 9 개까지 최대 ID가있는 경우이 예제는 전용으로 작동합니다 | 1 || 11 | 이전 올 것이다 | 1 || 2 |

    이 문제를 해결하려면, 당신은 예상 최대 값으로 예를 아래처럼 응용 프로그램에 대한 예상되는 ID 필드의 최대 값을 기준으로 문자열을 구축하기위한 패딩을해야 할 것은 999 (3 자리)입니다

    |001||002|

    내 경험에 따라이 솔루션은 7-8 레벨 업에 깊이 핸들 나무 좋은이어야합니다.

    다른 방법 : 여기를 클릭하세요

  6. ==============================

    6.SQL

    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. ==============================

    7.

    SELECT * FROM category ORDER BY name, parent_id ASC
  8. ==============================

    8.당신의 SQL 쿼리의 끝에서 이름, ID BY ORDER를 사용해보십시오.

    당신의 SQL 쿼리의 끝에서 이름, ID BY ORDER를 사용해보십시오.

    이것은 어떤 관계를 해결하기 위해 이름과 사용 ID로 정렬합니다.

    9. from https://stackoverflow.com/questions/14890204/order-sql-tree-hierarchy by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 내가 한 번 사용 후 왜 내 CTE에 액세스 할 수 없습니다?  (0) 2020.06.27
[SQL] 행 사이 SQL 차이  (0) 2020.06.27
[SQL] 유사한 문자열을 포함하는 SQL 기록을 찾기  (0) 2020.06.26
[SQL] PostgreSQL을 다른 테이블의 컬럼이있는 테이블의 열을 업데이트  (0) 2020.06.26
[SQL] 에서 테이블을 선택 *에 SQLite는 삽입  (0) 2020.06.26
,

티스토리툴바