\uae4a\uc774\uac00 \uae4a\uc9c0\uc54a\ub294 \uce74\ud14c\uace0\ub9ac\uc5d0 \uc801\ub2f9<\/p>\n
=====================================================
\n—\u00a0\uce74\ud14c\uace0\ub9ac\u00a0\ud14c\uc774\ube14\uc744 \uc791\uc131\ud55c\ub2e4.<\/p>\n
DROP TABLE IF EXISTS category ;
\nCREATE TABLE category(
\ncategory_id INT AUTO_INCREMENT PRIMARY KEY,
\nname VARCHAR(20) NOT NULL,
\nparent INT DEFAULT NULL);<\/p>\n
INSERT INTO category
\nVALUES(1,’ELECTRONICS’,NULL),(2,’TELEVISIONS’,1),(3,’TUBE’,2),
\n(4,’LCD’,2),(5,’PLASMA’,2),(6,’PORTABLE ELECTRONICS’,1),
\n(7,’MP3 PLAYERS’,6),(8,’FLASH’,7),
\n(9,’CD PLAYERS’,6),(10,’2 WAY RADIOS’,6);<\/p>\n
SELECT * FROM category ORDER BY category_id;<\/p>\n
— self \uc870\uc778\uc744 \uc774\uc6a9\ud55c DEPTH ( LEVEL ) \uad6c\ud604 \ucffc\ub9ac
\n— \uc77c\ubc18\uc801\uc778 \ubc29\ubc95\uc774\ub2e4.
\nSELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
\nFROM category AS t1
\nLEFT JOIN category AS t2 ON t2.parent = t1.category_id
\nLEFT JOIN category AS t3 ON t3.parent = t2.category_id
\nLEFT JOIN category AS t4 ON t4.parent = t3.category_id
\nWHERE t1.name = ‘ELECTRONICS’;<\/p>\n
— \ub808\ubca8\uc758 \ub9c8\uc9c0\ub9c9 leaf node \ub9cc\uc744 \uc870\ud68c<\/p>\n
SELECT t1.name FROM
\ncategory AS t1 LEFT JOIN category as t2
\nON t1.category_id = t2.parent
\nWHERE t2.category_id IS NULL;<\/p>\n
— \ud55c\uac1c\uc758\u00a0\uce74\ud14c\uace0\ub9ac\uc5d0 \ub300\ud574 \uc0c1\uc704\u00a0\uce74\ud14c\uace0\ub9ac\ub4e4 \uc870\ud68c
\nSELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
\nFROM category AS t1
\nLEFT JOIN category AS t2 ON t2.parent = t1.category_id
\nLEFT JOIN category AS t3 ON t3.parent = t2.category_id
\nLEFT JOIN category AS t4 ON t4.parent = t3.category_id
\nWHERE t1.name = ‘ELECTRONICS’ AND t4.name = ‘FLASH’;<\/p>\n
— SELF \uc870\uc778\uc774 \uc544\ub2cc \ubc29\ubc95\uc744 \uc774\uc6a9\ud558\uae30 \uc704\ud55c \ud14c\uc774\ube14 \uc0dd\uc131
\nDROP TABLE IF EXISTS\u00a0 nested_category ;<\/p>\n
SELECT node.name FROM `nested_category` AS node, `nested_category` AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nAND parent.name = ‘PORTABLE ELECTRONICS’
\nORDER BY node.lft;<\/p>\n
INSERT INTO nested_category
\nVALUES(1,’ELECTRONICS’,1,20),(2,’TELEVISIONS’,2,9),(3,’TUBE’,3,4),
\n(4,’LCD’,5,6),(5,’PLASMA’,7,8),(6,’PORTABLE ELECTRONICS’,10,19),
\n(7,’MP3 PLAYERS’,11,14),(8,’FLASH’,12,13),
\n(9,’CD PLAYERS’,15,16),(10,’2 WAY RADIOS’,17,18);<\/p>\n
— \ub2f9\u00a0\uce74\ud14c\uace0\ub9ac\uac00 \uc9c0\ub2c8\uace0 \uc788\ub294 \ud558\uc704\u00a0\uce74\ud14c\uace0\ub9ac\u00a0\ucd9c\ub825<\/p>\n
SELECT node.name
\nFROM nested_category AS node,
\nnested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nAND parent.name = ‘PORTABLE ELECTRONICS’
\nORDER BY node.lft;<\/p>\n
— \ubaa8\ub4e0 \ub9c8\uc9c0\ub9c9 \ub808\ubca8 \uc870\ud68c
\nSELECT name
\nFROM nested_category
\nWHERE rgt = lft + 1;<\/p>\n
— \ud574\ub2f9\u00a0\uce74\ud14c\uace0\ub9ac\uac00 \uc18c\uc18d\ub418\ub294 \uc0c1\uc704\u00a0\uce74\ud14c\uace0\ub9ac\u00a0\ucd9c\ub825
\nSELECT parent.name
\nFROM nested_category AS node,
\nnested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nAND node.name = ‘FLASH’
\nORDER BY parent.lft;<\/p>\n
—\u00a0\uce74\ud14c\uace0\ub9ac\ubcc4 TREE \uc758 \ub808\ubca8 \ucd9c\ub825
\nSELECT node.name, (COUNT(parent.name) – 1) AS depth
\nFROM nested_category AS node,
\nnested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nGROUP BY node.name
\nORDER BY node.lft;<\/p>\n
— \ub4dc\ub514\uc5b4~!!\u00a0 \uc804\uccb4\u00a0\uce74\ud14c\uace0\ub9ac\uc758 TREE \uad6c\uc870 \ucd9c\ub825<\/p>\n
SELECT CONCAT( REPEAT(“\\t”, COUNT(parent.name) -1), node.name) AS name FROM nested_category AS node, nested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft;<\/p>\n
SELECT CONCAT( REPEAT(‘ ‘, COUNT(parent.name) – 1), node.name) AS name
\nFROM nested_category AS node,
\nnested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nGROUP BY node.name
\nORDER BY node.lft;<\/p>\n
— \ub4dc\ub514\uc5b4~!!\u00a0 \uc804\uccb4\u00a0\uce74\ud14c\uace0\ub9ac\uc758 TREE \uad6c\uc870\ubc0f \ub808\ubca8 \ucd9c\ub8252
\nSELECT node.name,
\nCONCAT( REPEAT(‘\u00a0 ‘, COUNT(parent.name) – 1), node.name) AS name2,
\n(COUNT(parent.name) – (sub_tree.depth + 1)) AS depth
\nFROM nested_category AS node,
\nnested_category AS parent,
\nnested_category AS sub_parent,
\n(
\nSELECT node.name, (COUNT(parent.name) – 1) AS depth
\nFROM nested_category AS node,
\nnested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nAND node.name = ‘ELECTRONICS’
\nGROUP BY node.name
\nORDER BY node.lft
\n)AS sub_tree
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nAND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
\nAND sub_parent.name = sub_tree.name
\nGROUP BY node.name
\nHAVING depth <= 3\u00a0 — ‘ELECTRONICS’ \uc758 \ud558\uc704\uc758 \uc870\ud68c \ub2e8\uacc4 \uc9c0\uc815
\nORDER BY node.lft;<\/p>\n
—\u00a0\uce74\ud14c\uace0\ub9ac\u00a0\ud558\uc704\uc758 \uc0c1\ud488 \ub4f1\ub85d\uc744 \uc704\ud55c \ud14c\uc774\ube14 \uc0dd\uc131
\nDROP TABLE IF EXISTS\u00a0 product ;
\nCREATE TABLE product(
\nproduct_id INT AUTO_INCREMENT PRIMARY KEY,
\nname VARCHAR(40),
\ncategory_id INT NOT NULL
\n);<\/p>\n
INSERT INTO product(name, category_id) VALUES(’20” TV’,3),(’36” TV’,3),
\n(‘Super-LCD 42″‘,4),(‘Ultra-Plasma 62″‘,5),(‘Value Plasma 38″‘,5),
\n(‘Power-MP3 5gb’,7),(‘Super-Player 1gb’,8),(‘Porta CD’,9),(‘CD To go!’,9),
\n(‘Family Talk 360’,10);<\/p>\n
—\u00a0\uce74\ud14c\uace0\ub9ac\ubcc4 \ub4f1\ub85d\ub418\uc5b4 \uc788\ub294 \uc0c1\ud488\uc758 \uac1c\uc218 \uce74\uc6b4\ud2b8
\nSELECT parent.name, COUNT(product.name)
\nFROM
\nnested_category AS node
\nleft join\u00a0 nested_category AS parent
\non node.lft BETWEEN parent.lft AND parent.rgt
\nleft join product
\non node.category_id\u00a0 =\u00a0 product.category_id
\nGROUP BY parent.name
\nORDER BY node.lft;<\/p>\n
— \ud2b9\uc815\u00a0\uce74\ud14c\uace0\ub9ac\uc640 \ub3d9\uc77c \ub808\ubca8\uc758\u00a0\uce74\ud14c\uace0\ub9ac\u00a0\ucd94\uac00 ( \uc9c0\uc815\ud55c\u00a0\uce74\ud14c\uace0\ub9ac\u00a0\ub2e4\uc74c\uc5d0 \uc0dd\uc131)
\nset @myRight = 0;
\nSELECT @myRight := rgt
\nFROM nested_category
\nWHERE name = ‘TELEVISIONS’;
\nUPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
\nUPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
\nINSERT INTO nested_category(name, lft, rgt) VALUES(‘GAME CONSOLES’, @myRight + 1, @myRight + 2);<\/p>\n
— \uc870\ud68c
\nSELECT CONCAT( REPEAT(‘\u00a0 \u00a0 ‘, COUNT(parent.name) – 1), node.name) AS name
\nFROM nested_category AS node,
\nnested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nGROUP BY node.name
\nORDER BY node.lft;<\/p>\n
— \ud2b9\uc815\u00a0\uce74\ud14c\uace0\ub9ac\uc758 \ud558\uc704 \ub808\ubca8\uc5d0\u00a0\uce74\ud14c\uace0\ub9ac\u00a0\ucd94\uac00 ( \uc874\uc7ac\ud558\uace0 \uc788\ub294 \ud558\uc704 \ub808\ubca8\uc758 \uc55e\uc5d0 \uc0dd\uc131\uc774 \ub41c\ub2e4 )
\nset @myLeft = 0;
\nSELECT @myLeft := lft FROM nested_category WHERE name = ‘2 WAY RADIOS’;
\nUPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
\nUPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
\nINSERT INTO nested_category(name, lft, rgt) VALUES(‘FRS2’, @myLeft + 1, @myLeft + 2);<\/p>\n
— \uc870\ud68c
\nSELECT CONCAT( REPEAT(‘\u00a0 \u00a0 ‘, COUNT(parent.name) – 1), node.name) AS name
\nFROM nested_category AS node,
\nnested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nGROUP BY node.name
\nORDER BY node.lft;<\/p>\n
—\u00a0\uce74\ud14c\uace0\ub9ac\u00a0\uc0ad\uc81c
\nset @myRight = 0;
\nset @myLeft = 0;
\nSELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt – lft + 1
\nFROM nested_category
\nWHERE name = ‘FRS2’;
\nDELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
\nUPDATE nested_category SET rgt = rgt – @myWidth WHERE rgt > @myRight;
\nUPDATE nested_category SET lft = lft – @myWidth WHERE lft > @myRight;<\/p>\n
— \uc870\ud68c
\nSELECT CONCAT( REPEAT(‘\u00a0 \u00a0 ‘, COUNT(parent.name) – 1), node.name) AS name
\nFROM nested_category AS node,
\nnested_category AS parent
\nWHERE node.lft BETWEEN parent.lft AND parent.rgt
\nGROUP BY node.name
\nORDER BY node.lft;<\/p>\n
— \uc4f0\ub808\uae30 \uccad\uc18c<\/p>\n
DROP TABLE IF EXISTS category ;
\nDROP TABLE IF EXISTS\u00a0 nested_category ;
\nDROP TABLE IF EXISTS\u00a0 product ;
\n====================================================<\/p>\n
http:\/\/ftp.nchu.edu.tw\/MySQL\/tech-resources\/articles\/hierarchical-data.html<\/a><\/p>\n