깊이가 깊지않는 카테고리에 적당
=====================================================
— 카테고리 테이블을 작성한다.
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 ;
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;
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(“\t”, 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;
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
WHERE name = ‘FRS2’;
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt – @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft – @myWidth WHERE lft > @myRight;
— 조회
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;
— 쓰레기 청소
DROP TABLE IF EXISTS category ;
DROP TABLE IF EXISTS nested_category ;
DROP TABLE IF EXISTS product ;
====================================================
http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/hierarchical-data.html