[MySQL] 테이블 하나로 만든 무한카테고리 쿼리문 만들기 > db

기본적으로 원문의 요청사항은 보통 Tree 구조로 보았을때 5 단계 미만일 경우 SELF JOIN을 이용한 View 를 생성해서 사용 합니다. 

그러나  생산관리나 공급망 관리같은 것으로 들어 가게 될 경우 , BOM 구조의 특성상 5단계는 가비압게 넘어 가게 되죠 

오라클이나 MSSQL 2005 버전 이하인 경우 BOM 모델의 경우 재귀쿼리를 수행하기 위해서는 쑈를 하는 경우가 많습니다.  ( 어쩔수 없죠 지원해 주지 않는 이상...) 

아래의 샘플은 mysql 이 서브쿼리를 지원하기 시작했을 때,  구글링을 통해 어느분이 개념 잡으신 거를 제가 나름 정리 한겁니다 

database.sarang.net 이었던가  기억이 안나는데..database.sarang.net  이 맛이 가 있어서 확인할 길은 없지만... 
아무튼, 원작자님의 허락없이 게시해서 죄송하지만 (사실 이부분때문에 메일로 드리려 했죠)  원작자님께서 보시고 부당하다 생각 하시면 바로 자삭 하겠습니다. 


그런 아래 나갑니다.. 

이해 안가신다 하시지 마세요... 쿼리 한번씩만 돌려 보시면 답나옵니다.. 

===================================================== 
-- 카테고리 테이블을 작성한다. 

DROP TABLE IF EXISTS category ; 
CREATE TABLE category( 
category_id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(20) NOT NULL, 
parent INT DEFAULT NULL); 


INSERT INTO category 
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2), 
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1), 
(7,'MP3 PLAYERS',6),(8,'FLASH',7), 
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6); 

SELECT * FROM category ORDER BY category_id; 


-- self 조인을 이용한 DEPTH ( LEVEL ) 구현 쿼리 
-- 일반적인 방법이다. 
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 
FROM category AS t1 
LEFT JOIN category AS t2 ON t2.parent = t1.category_id 
LEFT JOIN category AS t3 ON t3.parent = t2.category_id 
LEFT JOIN category AS t4 ON t4.parent = t3.category_id 
WHERE t1.name = 'ELECTRONICS'; 

-- 레벨의 마지막 leaf node 만을 조회 
  
SELECT t1.name FROM 
category AS t1 LEFT JOIN category as t2 
ON t1.category_id = t2.parent 
WHERE t2.category_id IS NULL; 


-- 한개의 카테고리에 대해 상위 카테고리들 조회 
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 
FROM category AS t1 
LEFT JOIN category AS t2 ON t2.parent = t1.category_id 
LEFT JOIN category AS t3 ON t3.parent = t2.category_id 
LEFT JOIN category AS t4 ON t4.parent = t3.category_id 
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH'; 

-------------------------------------------------------------------------------------------
-- SELF 조인이 아닌 방법을 이용하기 위한 테이블 생성  
DROP TABLE IF EXISTS  nested_category ; 
  
CREATE TABLE nested_category ( 
 category_id INT AUTO_INCREMENT PRIMARY KEY, 
 name VARCHAR(20) NOT NULL, 
 lft INT NOT NULL, 
 rgt INT NOT NULL 
); 

INSERT INTO nested_category 
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4), 
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19), 
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13), 
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18); 


-- 당 카테고리가 지니고 있는 하위 카테고리 출력  

SELECT node.name 
FROM nested_category AS node, 
nested_category AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
AND parent.name = 'PORTABLE ELECTRONICS' 
ORDER BY node.lft; 

-- 모든 마지막 레벨 조회  
SELECT name 
FROM nested_category 
WHERE rgt = lft + 1; 

-- 해당 카테고리가 소속되는 상위 카테고리 출력 
SELECT parent.name 
FROM nested_category AS node, 
nested_category AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
AND node.name = 'FLASH' 
ORDER BY parent.lft; 

-- 카테고리별 TREE 의 레벨 출력 
SELECT node.name, (COUNT(parent.name) - 1) AS depth 
FROM nested_category AS node, 
nested_category AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
GROUP BY node.name 
ORDER BY node.lft; 

-- 드디어~!!  전체 카테고리의 TREE 구조 출력 
SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name 
FROM nested_category AS node, 
nested_category AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
GROUP BY node.name 
ORDER BY node.lft; 


-- 드디어~!!  전체 카테고리의 TREE 구조및 레벨 출력2 
SELECT node.name, 
        CONCAT( REPEAT('  ', COUNT(parent.name) - 1), node.name) AS name2, 
        (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth 
FROM nested_category AS node, 
    nested_category AS parent, 
    nested_category AS sub_parent, 
    ( 
        SELECT node.name, (COUNT(parent.name) - 1) AS depth 
        FROM nested_category AS node, 
        nested_category AS parent 
        WHERE node.lft BETWEEN parent.lft AND parent.rgt 
        AND node.name = 'ELECTRONICS' 
        GROUP BY node.name 
        ORDER BY node.lft 
    )AS sub_tree 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt 
    AND sub_parent.name = sub_tree.name 
GROUP BY node.name 
HAVING depth <= 3  -- 'ELECTRONICS' 의 하위의 조회 단계 지정  
ORDER BY node.lft; 


-- 카테고리 하위의 상품 등록을 위한 테이블 생성 
DROP TABLE IF EXISTS  product ; 
CREATE TABLE product( 
product_id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(40), 
category_id INT NOT NULL 
); 


INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3), 
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5), 
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9), 
('Family Talk 360',10); 
  
-- 카테고리별 등록되어 있는 상품의 개수 카운트 
SELECT parent.name, COUNT(product.name) 
FROM 
nested_category AS node 
    left join  nested_category AS parent 
        on node.lft BETWEEN parent.lft AND parent.rgt 
    left join product 
        on node.category_id  =  product.category_id 
GROUP BY parent.name 
ORDER BY node.lft; 


-- 특정 카테고리와 동일 레벨의 카테고리 추가 ( 지정한 카테고리 다음에 생성) 
set @myRight = 0; 
SELECT @myRight := rgt 
FROM nested_category 
WHERE name = 'TELEVISIONS'; 
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight; 
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight; 
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2); 

-- 조회  
SELECT CONCAT( REPEAT('    ', COUNT(parent.name) - 1), node.name) AS name 
FROM nested_category AS node, 
nested_category AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
GROUP BY node.name 
ORDER BY node.lft; 

-- 특정 카테고리의 하위 레벨에 카테고리 추가 ( 존재하고 있는 하위 레벨의 앞에 생성이 된다 ) 
set @myLeft = 0; 
SELECT @myLeft := lft FROM nested_category WHERE name = '2 WAY RADIOS'; 
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft; 
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft; 
INSERT INTO nested_category(name, lft, rgt) VALUES('FRS2', @myLeft + 1, @myLeft + 2); 

-- 조회  
SELECT CONCAT( REPEAT('    ', COUNT(parent.name) - 1), node.name) AS name 
FROM nested_category AS node, 
nested_category AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
GROUP BY node.name 
ORDER BY node.lft; 
  

-- 카테고리 삭제 
set @myRight = 0; 
set @myLeft = 0; 
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 
FROM nested_category