[SQL] MySQL의 계층 구조 자체 조인, 모든 하위 범주를 검색

MySQL의 계층 구조 자체 조인, 모든 하위 범주를 검색

나는 다음과 같은 열이 포함 된 테이블 카테고리를 가지고 :

category_id
category_name
parent_id

나는 4, 5, 6 ... 예를 들어 내가 어떤 LVL 3 카테고리의 ID를 주면 내가 모든 LVL의 목록을 다시 얻을 것, 주어진 주요 범주의 모든 수준에있는 모든 하위 범주의 목록을 얻을 수의 하위 범주가 필요합니다 하나 LVL 3 종류있다.

어떤 계층의 요구는 단순한 목록을 보존하지합니다.

내가 처음에 대해 단지 더 깊은 이후 그래서하지 않는 방식으로 가고 있음을 벌 것이다 여러 조인 및 하위 쿼리와 함께하지만 생각 된 범주보다 그 일을 생각했다.

난 그냥 SQL I을 시작했습니다 때문에 여전히이 큰 도움이 학습 자료가 될 것입니다 때문에 재귀 쿼리를 작성하는 방법을 모르겠어요.

해결법

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

   1.하단 처음하시기 바랍니다 참고를 참조하십시오. 좋아 좋아, 당신은 다시입니다.

   하단 처음하시기 바랍니다 참고를 참조하십시오. 좋아 좋아, 당신은 다시입니다.

   계층 구조 검색과 같은 재귀에 대한 저장 프로 시저의 생성.

   참고, 당신은 수준으로 그것을 원하지 않았다하지만 쉽게 수행 할 수 있습니다.

   create table category
   (   category_id int not null auto_increment primary key,
       category_name varchar(40) not null,
       parent_id int null,  -- index on this column not a shabby idea
       unique key (category_name)
   );
   
   insert category(category_name,parent_id) values ('car',null),('food',null); -- 1,2
   insert category(category_name,parent_id) values ('ford',1),('chevy',1),('fruit',2); -- 3,4,5
   insert category(category_name,parent_id) values ('economy',3),('escort',6),('exhaust',7); -- 6,7,8
   insert category(category_name,parent_id) values ('chassis',7),('loud',8),('banana',5); -- 9,10,11
   -- ok granted I could have explicity inserted category_id to make it more obvious
   

   -- drop procedure showHierarchyBelow;
   delimiter $$
   create procedure showHierarchyBelow
   (
   catname varchar(40)
   )
   BEGIN
       -- deleteMe parameter means i am anywhere in hierarchy of role
       -- and i want me and all my offspring deleted (no orphaning of children or theirs)
       declare bDoneYet boolean default false;
       declare working_on int;
       declare theCount int;
       declare findFirst int;
   
       select ifnull(category_id,0) into findFirst from category where category_name=catname;
   
       CREATE TABLE xx_RecursishHelper_xx
       (   -- it's recurshish, not recursive
           category_id int not null,
           processed int not null
       );
       if isnull(findFirst) then
           set findFirst=0;
       end if;
       insert into xx_RecursishHelper_xx (category_id,processed) select findFirst,0;
       if (findFirst=0) then
           set bDoneYet=true;
       else
           set bDoneYet=false;
       end if;
   
       while (!bDoneYet) do
           -- I am not proud of this next line, but oh well
           select count(*) into theCount from xx_RecursishHelper_xx where processed=0;
   
           if (theCount=0) then 
               -- found em all
               set bDoneYet=true;
           else
               -- one not processed yet, insert its children for processing
               SELECT category_id INTO working_on FROM xx_RecursishHelper_xx where processed=0 limit 1;
               insert into xx_RecursishHelper_xx (category_id,processed)
               select category_id,0 from category
               where parent_id=working_on;
   
               -- mark the one we "processed for children" as processed
               update xx_RecursishHelper_xx set processed=1 where category_id=working_on;
           end if;
       end while;
   
       delete from xx_RecursishHelper_xx where category_id=findFirst;
   
       select x.category_id,c.category_name
       from xx_RecursishHelper_xx x
       join category c
       on c.category_id=x.category_id;
   
       drop table xx_RecursishHelper_xx;
   END
   $$
   

   call showHierarchyBelow('food');
   +-------------+---------------+
   | category_id | category_name |
   +-------------+---------------+
   |           5 | fruit         |
   |          11 | banana        |
   +-------------+---------------+
   
   call showHierarchyBelow('car');
   +-------------+---------------+
   | category_id | category_name |
   +-------------+---------------+
   |           3 | ford          |
   |           4 | chevy         |
   |           6 | economy       |
   |           7 | escort        |
   |           8 | exhaust       |
   |           9 | chassis       |
   |          10 | loud          |
   +-------------+---------------+
   
   call showHierarchyBelow('ford');
   +-------------+---------------+
   | category_id | category_name |
   +-------------+---------------+
   |           6 | economy       |
   |           7 | escort        |
   |           8 | exhaust       |
   |           9 | chassis       |
   |          10 | loud          |
   +-------------+---------------+
   
   call showHierarchyBelow('xxx');
   -- no rows
   

   참고 난 단지 당신의 필요를 위해 몇 달 전부터 내이 대답을 수정했습니다.

   상기는 예시적인 목적으로 만 제공된다. 실제 상황에서, 나는 저장된 프로 시저에서 테이블을 생성하지 않습니다 않을 것입니다. DDL을 오버 헤드가 중요하다. 대신, 세션의 개념을 기존의 비 임시 테이블을 사용합니다. 그리고 수행 세션의 행을 청소. 당신과 같은 더 확대됨 수 있도록하는 그래서 기다리고 허수아비보다 더으로 위를하지 않습니다. 그 혼란 있는지 물어.

from https://stackoverflow.com/questions/31965365/mysql-hierarchy-self-join-retrieve-all-subcategories by cc-by-sa and MIT license